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”“”
Close lFile
Workbooks.Open sFile
End Sub
- Plain:
12-14
is in the CSV file and Excel converts it to a date. - Apostrophe:
'12-14
is in the CSV file and Excel shows the apostrophe. - Preceding Space:
[space]12-14
is 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. - Formula:
="12-14"
is in the CSV. This is actually the best representation if you don’t mind having a formula in there.
Dates are always a problem and I’m glad you went through all those different options. Adding a space before the date can be advantageous if dealing with data from prior years. Or dealing with a December file in January.
We have a product that requires input of a month in the format “Feb 2007?. We use Excel as a convenience to get data to and from our system. Unfortunately, if a user downloads a CSV with “Feb 2007? in it and opens it up, Excel converts it to 2/1/2007 but shows it, like you said, as “1-Feb”. When the user saves the file, this item becomes “1-Feb” in the CSV. If the user opens it up again, Excel “helps” by filling in the missing year with the current year. So our original Feb 2007 becomes 2/1/2010.
Avoid doing what we did at all cost.
Actually you wouldn’t even need to save the file as txt. I use text import every day with files with no extension. You just need to select “All files” so that the filename comes up.
Great post!
This is indeed a big issue with Excel, no version (even 2007 & 2010) can deal with date in text files correctly.
The problem gets bigger with dates formatted french way dd/mm/yyyy or swiss way dd.mm.yyyy.
In that particular case, Excel understands 2/1/2007 as feb-1, but it is in fact Jan-2, so imagine a list with date from July-1 to July-30, you’ll end up with :
Jan-7, Feb-7, Mar-7, Apr-7, Jun-7, Jul-7, Aug-7, Sep-7, Oct-7, Nov-7, Dec-7, Jul-8, Jul-9, Etc… which is obviously not the way it is supposed to be :-D
Not the most elegant way to go about it, but this method first opens the file “normally” to get a column count, closes the file, renames it to *.txt, and then opens it again using the OpenText method specifying every column as text.
Dim FilesToOpen As Variant
Dim FileCounter As Long
Dim wb As Workbook
Dim FilePath As String
Dim NumColumns As Long
Dim ColumnInfo() As Variant
Dim ColumnCounter As Long
Dim fso As Object
Dim FilePathTxt As String
FilesToOpen = Application.GetOpenFilename(“CSV files (*.csv), *.csv”, , “Select files to open”, , True)
If Not IsArray(FilesToOpen) Then
MsgBox “No files selected, aborting”, vbCritical, “No soup for you!”
Exit Sub
End If
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
Set fso = CreateObject(“Scripting.FileSystemObject”)
For FileCounter = LBound(FilesToOpen) To UBound(FilesToOpen)
FilePath = FilesToOpen(FileCounter)
Set wb = Workbooks.Open(FilePath)
NumColumns = Cells(1, Columns.Count).End(xlToLeft).Column
wb.Close False
ReDim ColumnInfo(0 To NumColumns – 1) As Variant
For ColumnCounter = 1 To NumColumns
ColumnInfo(ColumnCounter – 1) = Array(ColumnCounter, 2)
Next
FilePathTxt = Left(FilePath, Len(FilePath) – 3) & “txt”
If Dir(FilePathTxt) <> “” Then Kill FilePathTxt
fso.MoveFile FilePath, FilePathTxt
Workbooks.OpenText Filename:=FilePathTxt, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=ColumnInfo, TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs FilePath, xlCSV
Kill FilePathTxt
Next
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
Set fso = Nothing
MsgBox “Done”
End Sub
There’s another problem which comes into play as you move away from the US. If you have the date 14/12 (UK format) or 12/14 (US format) then that’s bound to be the 14th December, but if you have 10/7 or 7/10? Do you want July or October. In the text import wizard it reads dates using, I think, the local format, but if you load the data using VBA then it always uses US format – even if you’ve used Record Macro.
One case where Excel is too helpful.
This has annoyed me often enough that I changed the file association for .CSV files so that they open in OpenOffice Calc, which unlike Excel always fires up their equivalent of the file parser when opening .CSV files. Once open and parsed, I copy and paste into Excel. Maybe overkill, but I refuse to reinvent this particular wheel. Excel should provide an option for opening .CSV files ‘natively’ or as text files.
As for options when creating .CSV files, my favorite work-around is using ‘soft hyphen’ (decimal char code 173) when I want to ensure there’s neither date nor leading sign conversions. An alternative is using trailing nonbreaking spaces (decimal char code 160). Either of these work with other programs besides Excel, though that may be of minimal interest to those who only use Excel to work with .CSV files.
How about this for strange, I had a problem when opening an Excel file on a machine configured to UK Dates.
When Access loads in the file when the excel file is closed, the file format comes in as a US date (which in this case was correct. However when Excel is open in the background it pulls in the column and converts to UK, as it can now see the value as a number. How annoying is this !
I ended up getting round it by opening the file formatting the dates as dd mmm yyyy and saving the file under a new name to prevent any confusion. This was the best solution as later on I found some people had date columns as US formats and some as UK. I have to say we should all go to yyyy mm dd or at least dd mmm yyyy.
Jan
I should add I don’t do this manually. I just wrote the VBA to manage it.
I faced the same problem – I have a logging device the produces a CSV file with dates in mm/dd/yyyy format, but my system is running on dd/mm/yyyy format. Opening the CSV file in Excel leads to some dates being imported as a mix of dates (but the wrong dates unless the month and day are the same) and text. As I paste data from the CSV files into the analysis sheet all the time, I needed a simple way of correcting the dates manually so I inserted a new first column and use a formula to calculate the correct dates from the pasted dates in column b. Thus for line 5030 the formula reads:
=IF(ISNUMBER(B5030),DATEVALUE(MONTH(B5030)&”/”&DAY(B5030)&”/”&YEAR(B5030)),IF(B5030?”,DATEVALUE(MID(B5030,FIND(“/”,B5030)+1,FIND(“/”,B5030,4)-FIND(“/”,B5030)-1)&”/”&LEFT(B5030,FIND(“/”,B5030)-1)&”/”&RIGHT(B5030,4)),””))
The trap for blanks prevents a Value# error when I don’t have any data pasted into that line yet.