Saving as text tab delimited (Am I missing something?)

[pjb515]

Hi all,

Excel 2007 on windows 7 professional.
We've got an end user who opens a .dat file which is delimited with semi-colons, replaces some text in a couple of columns then saves as a text tab delimited file. She swears that before today, the saved file would retain the semi-colons. Now she says it's not. she generated a new .dat file for me and recreated the process, with the resultant semicolon-free file. I asked her to generate another one and send it to me. I opened it first in notepad, and saw the semi-colons. Opened it in excel (I'm using 2010 on win 7 professional), which saw it as a delimited file and asked me to specify the delimiter. It shows up fine, all the columns are righteous. I made no edits and saved as a text tab delimited, and that saved file had no semi-colons in it when I opened it. This seems normal to me; there's no semi-colons in the excel file, so why would there be in the text tab delimited file. I asked her coworker to generate another .dat from his pc and let me know what he sees, but he hasn't reported back yet.
Unless i'm wrong, the end user is doing something different, or I'm just missing something. Could anyone offer guidance? Thanks.

[pjb515]

I just thought of an obvious tshooting step to try- create a brand new file and see what happens to the export. Same thing, no semi-colons, and I wasn't expecting them since they didn't exist but to be fair the original file from the end user has the semi-colons in it...

[papa-blaza]

Let me know if i have the problem correct from what i read. The original file has semicolons delimitating the columns. Changes are made to some of this text. The file is then saved as a tab delimitating file. And you need to know why this file has no semi-colons? And would you like this file to have the semicolons or not?

[pjb515]

Yes, you got it right. I think I already know why it has no semi-colons, because they're gone once the dat file is opened by excel, and I think this is expected behavior but that is my question, am I right about that? And yes, the end-user would like the text tab delimited file that excel creates to contain the semi-colons. If I'm right about the first part, then the only way to get semi-colons back in the file is to do some type of Edit, Replace command but the end-user thinks they should already be there...

[papa-blaza]

I would go to save as and see the different options for saving the file. If you save the file as tab delimited then excel will save the file with a tab between each column. I do not think excel has the option to save as a semi-colon delimited file. It does however have the option to save as comma delimited. If you definitely require the semi-colon then you will have to add the semi-colons into each cell then save it as a space delimited file or comma delimited file. Let me know if you need an easy way to add the semi-colons in.

[pjb515]

Nah, we can show the users how to use edit/replace, it'll work fine for their file. However, there is a new development. One of the end users got it to work as a .csv. I tried it too, and it worked. Here's the pertinent part of the email she sent:

"I got it to work as a CSV file. The end result gets me the semicolon. Go Excel Options/ Advanced/Editing Options Decimals and uncheck system separators and put in a comma in the decimal field. Then you need to save the file as a CSV file…it will automatically put in a semicolon between fields. I still don’t have a solution to the txt files but at least this works and most systems have no problems reading CSV files. And no it doesn’t change the decimal in the file to a comma or a semicolon".

So there ya go. Thanks for your input, by the way.

I'm still wondering if my original expectations are correct, the semi-colon should not be in the file once the .dat is opened in Excel? If anyone can confirm or expand on this I'd appreciate it.