Excel changes date formats

Question

I run a process to produce a rather large CSV file of data. Sometimes I find it helpful to open the CSV in excel, make changes manually, and then re-save. However, if there are dates in the CSV, excel automatically reformats them. Is there a way to prevent excel from doing this? It would be helpful if I could turn off all text formatting altogether.

Answer 1

If you prepend an apostrophe ' to the beginning of any date string during the export process, Excel will read the value literally (i.e. as plain text) rather than trying to convert it to a date.

This particular solution is handled during the export process. I'm not sure how you would change Excel to treat the file differently at runtime.

Answer 2

Excel does some nasty tricks when outputting XML. One of its tricks is to drop left most column delimiters if 16 or so consecutive rows have no values for these columns. This means that if you're splitting the lines up based on commmas then these rows will have a different number of columns to the rest.

It will also drop any initial 0's so things like numeric Ids can become messed up.

Another risk you run is chopping the file off short since Excel can only support a maximum number of rows. (Prior to Excel 2007 this was around 65536)

If you need to do anything to a CSV file other than read it use a text editor.

Answer 3

When you import the CSV file into Excel, be sure to pre-format the date column as text. There's a frequently overlooked option in the parsing that allows you to control the format column by column. This also works well for preventing the leading zeros in New England ZIP codes from getting dropped in your contact lists.