Thread: Stubborn CSV file refuses to accept text format

I have a CSV file that contains a zip code. Some zip codes beginning with zero appear as four digits instead of five. So I attempted to use the TEXT(R2,"00000") formula to force the format. Doing this produced the following result:

1. Text appears correctly.
2. File is verified as saved (including a change in the last modified date).
3. When file is re-opened, changes are gone.

I also attempted to format a column as text FIRST then paste the values of the formula into the new column and the format STILL did not hold. The only thing that forces it is a single quote, but I cannot leave a single quote in my zip codes.

Any ideas?

i've run into that same issue ... when the file is saved, after the formatting ... it's all fine. i am able to then import that into my database ... should i open the CSV again, all of the formatting is gone. i think it has something to do with converting from CSV to EXCEL when you open it. if i open the file with Notepad ++, the formatting actually still exists.

See: OL97: Postal Codes Import Incorrectly from Excel Workbook

Basically it says that a zip code is stored as a number in Excel, but has the ZipCode format applied.

Hey guys - thanks for the quick reply!

I've seen the number issue before, but what makes this particularly odd is that the formatting does not stick. I tried formatting to zip plus four, but since the majority of my zips are five digits, they came out as 00009-4031 instead of 94031-0000.

Formatting to zip (without the dash four) has the same issue. Format disappears as soon as I save the file.

This came up because I am uploading the file and the errors are caught on validation of the data. So the formatting is lost before the file is ever opened again. After I save the file, I try to upload it even before it is opened again and the same 'error' appears.

I've been using Excel for more than 15 years, and I've never seen anything this stubborn!

strange ... about the upload ... because i've been able to upload them with the format ... it's only when i reopen the file that i see it changed back. but i'm uploading to MySQL, wonder if that makes a difference.

Hmm - probably does. I suspect that the validation on this upload requires the file to be opened. This is for use as a mailing list, so the data has to be checked to insure valid zip codes.

I think you'll find it's losing the formatting because you're saving as CSV. A CSV file won't retain any formatting.

Formatting as a zip code doesn't actually change the data in the cell, it just formats it to include a zero at the front of a 4 digit code. I think, but don't quote me on it, if you had the zip code as '01234 (ie starting with an apostrophe) it would keep the data as text.

Actually, you are right RR. I did try with a leading apostrophe and it does retain formatting in that limited case. But, since it will also presumably print said apostrophe, I cannot leave it there.

Also - saving a properly formatted file from XLS to CSV causes a loss of formatting. Bummer

Originally Posted by Lauramc

Also - saving a properly formatted file from XLS to CSV causes a loss of formatting. Bummer

That's the problem. Changing the formatting is just changing how it is displayed. The actual cell value is unaffected and a CSV won't save the formatting (what is displayed), it saves the value.

I think I did something like create a new column next to the zipcode and then used this formula (assuming zipcode is in A1)

Code:

=if(len(A1)=4,"0"&A1,A1)

Then I think saving as the csv will save the value from this formula.

Hope that helps.

i beg to differ RR ...

in order to import data into MySQL using Heid (which is the front end they use here), the file needs to be CSV and can't be EXCEL. when i format the field and save as CSV and import, I am getting the 5 characters, so it is saving it as a value.