This is an age old problem that I’ve mostly ignored. When you have something in a CSV that could be a date, like 2-1 could be February 1st, Excel “helpfully” converts it into a date. This problem isn’t unique to Comma Separated Values (CSV) files. Excel will convert 2-1 entered into a cell to 2/1/2010 and format it as 1-Feb. The difference with CSV is that you don’t have any control over the data when it’s opened. If you really want to enter 2-1 in a cell, you can prefix it with an apostrophe or format the cell as Text to avoid the automatic conversion. Neither of those is available to you with a CSV.
A common solution to this problem is to use the Text Import Wizard to open or import the file. If you change the file extension from CSV to TXT, you can open it in Excel and go through the Text Import Wizard to tell it that it’s comma delimited and which columns contain which data types. You can also Import the data (Data – Import External Data – Import Data in Excel 2003), which also uses the Text Import Wizard.
Those are decent options, but one of the nice features of Excel is that it opens CSV files natively. If you’re not the one creating the CSV file, you don’t have a lot of options other than those presented above. If you are creating the CSV, you have some options in how you store the data that will effect how Excel interprets it. And here they are:
And the code to generate that
Dim sFile As String
Dim lFile As Long
lFile = FreeFile
sFile = Environ(“TEMP”) & “TestDates.csv”
Open sFile For Output As lFile
Print #lFile, “Plain,Single Apostrophe,Preceding Space,Double Quotes,Space Double Quotes,Double Quotes Space,Formula”
Print #lFile, “12-14,’12-14, 12-14,”“12-14”“, ““12-14”“,”” 12-14″“,=”“12-14”“”
12-14is in the CSV file and Excel converts it to a date.
'12-14is in the CSV file and Excel shows the apostrophe.
- Preceding Space:
[space]12-14is in the CSV and Excel treats it as text and the space is in the cell.
- Double Quotes:
"12-14"is in the CSV file. This is the normal way to delineate text in a CSV file, but Excel doesn’t care and converts it to a date anyway. Bad Excel!
- Space Double Quotes:
[space]"12-14"is in the CSV. Now I’m just trying silly stuff.
- Double Quotes Space:
" 12-14"is in the CSV. Same as Preceding Space.
="12-14"is in the CSV. This is actually the best representation if you don’t mind having a formula in there.