Excel changes date formats

I run a process to produce a rather large CSV file of da开发者_StackOverflowta. 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.

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.

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.

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.

If you used the excel file version which is 2010 or later (not sure lower version), you can set up to use current operation-system date format or not in Excel/CSV file.

1. Right Click cell with date value (e.g. '9/12/2013') in CSV file and pop up the menu
2. Click 'Format Cells' and open a pop up screen
3. Go to 'Number' tab and you can see 'Date' was selected in 'Category' (left side) and 'Type' on the right side
4. Observed that there are two types of Date format (one is with () and another is not with ()). Read the comment there and you can find that you can use the date format which is not with date. It means that your changes to the CSV file will not be applied with your current operation-system date format. So, I think date format won't be changed in CSV file in this case.