10 thoughts on “Excel Converts to Date When Opening CSV”

1. 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.

2. 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.

3. 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!

4. 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

5. 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.

   Sub OpenCSVAllText()
      
       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

6. 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.

7. 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.

8. 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

9. I should add I don’t do this manually. I just wrote the VBA to manage it.

10. 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.

Posting code? Use <pre> tags for VBA and <code> tags for inline.