Remove Tabs when saving to another file type

[ea73]

Hi,

I am trying to take an excel spreadsheet and save it as a .txt file. The cells need to be padded to a certain length, and while I have figured that out, when I save the file it places tabs at the end of each cell.

There are 100s of columns making this difficult to do manually seeing how I'll be sending daily files over the course of a month. Is there a way to not have this happen?

Thanks.

[6StringJazzer]

Save it as file type .prn.

[ea73]

I'm not quite sure how to do so

Google led me to the print dialog box where I was to click the "print to file" option and then go from there. I tried doing so, and then both c/p'ing into notepad as well as just saving it as a txt file and the tabs remain.

[6StringJazzer]

See attached, note highlighted file type.

[ea73]

That does seem to remove tabs but it opens up a whole other set of issues when I open the file in notepad. The padding I have used (both with a rpad function and also with a REPT function) seem to mess up when this happens.

I fear this exact issue is not completely solvable, but I am open to any and all suggestions.

[6StringJazzer]

Can't provide further advice without seeing your file. If the data is not sensitive can you post it? Or at least post a single row?

[ea73]

I have attached four files

Ex1 is using padding and saving as a tab delimited txt file
Ex2 is using padding and saving as a prn space delimited file, while the tabs are gone the padding is messed up, you'll see not enough spaces in between fields
Ex3 is actually a correct one I manually did with just spacing and no tabs
expadding.xls is the Excel file where I padded the cells

Thanks

[6StringJazzer]

I'm surprised at those results, but I tried it myself and see that Excel does not print all the blanks to the prn file.

My only other thought is to print it as csv and then edit it to remove the commas and any quotes, but that's a pain.

I'll come back if I can find better news.

[ea73]

could you explain that part of the process, printing it as a csv?

would it be possible to do so, then to find and replace the commas with nothing?

[6StringJazzer]

You save it as a csv the same as any other file type, just look at the list for CSV (Comma delimited) (csv).

Then you would have to edit the result in a text editor and replace all commas with the null string. However, this gets complicated if any of your data contains commas.

[ea73]

good lord that worked, luckily I don't have any commas

awesome, thanks for the help

[6StringJazzer]

Glad I could help, but I hate giving advice that requires a post-Excel hack like that.

BTW if a cell has a comma in it, the csv output will have quotes around the content of that cell. Removing the delimiting commas and the quotes without removing the internal commas would probably require an editor that handles regular expressions (i.e., not a Microsoft product).