problem opening a .csv file in excel - dates all messed up (1 Viewer)

[GPGeorge]

George. Opening a csv with excel changes the data, or can change the data at any rate. I think a date can change to a date plus time, which in itself can cause an issue. Viewing the data with notepad won't change it. For what it's worth, I would much rather process a csv file than an excel file.

Yup, that's exactly what I had to deal with. Users who could not resist "verifying" the csv by checking it in Excel.

 

[CJ_London]

It’s not a problem if I open the csv directly

 

[Pat Hartman]

MS perpetuates this problem by making Excel the default program to open .csv files rather than the text editor.

 

[xavier.batlle]

I don't know if this can help. When I import a CSV File to an Excel spreadsheet, I use some code similar to this one generated by ChatGPT where you can specify the type and date format of each column. (Not tested)

Sub ImportCSVToExcelWithFormatting()
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim csvPath As String
    Dim excelPath As String
 
    ' === Set file paths ===
    csvPath = "C:\Path\To\Your\File.csv"
    excelPath = "C:\Path\To\Your\Output.xlsx"
 
    ' === Start Excel ===
    Set xlApp = New Excel.Application
    xlApp.Visible = True ' Set to False if you don’t want to show Excel

    ' === Add a new workbook ===
    Set xlWB = xlApp.Workbooks.Add
    Set xlWS = xlWB.Sheets(1)
 
    ' === Import CSV ===
    With xlWS.QueryTables.Add(Connection:="TEXT;" & csvPath, Destination:=xlWS.Range("A1"))
        .TextFileConsecutiveDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileTabDelimiter = False
        .TextFilePlatform = 65001 ' UTF-8
        .TextFileColumnDataTypes = Array(1, 1, 4, 2, 5) ' Example formats:
        ' 1 = General, 2 = Text, 3 = MDY Date, 4 = DMY Date, 5 = Skip
        .Refresh BackgroundQuery:=False
    End With

    ' === Example: Format columns explicitly ===
    With xlWS
        .Columns("A").NumberFormat = "General"
        .Columns("B").NumberFormat = "0.00" ' Numeric with 2 decimals
        .Columns("C").NumberFormat = "dd/mm/yyyy"
        .Columns("D").NumberFormat = "@" ' Text
    End With

    ' === Save the workbook ===
    xlWB.SaveAs Filename:=excelPath, FileFormat:=xlOpenXMLWorkbook
    xlWB.Close SaveChanges:=False
    xlApp.Quit

    ' === Clean up ===
    Set xlWS = Nothing
    Set xlWB = Nothing
    Set xlApp = Nothing

    MsgBox "CSV imported to Excel with formatting."
End Sub

XlColumnDataType enumeration (Excel)​

Specifies how a column is to be parsed.

xlDMYFormat	4	DMY date format.
xlDYMFormat	7	DYM date format.
xlEMDFormat	10	EMD date format.
xlGeneralFormat	1	General.
xlMDYFormat	3	MDY date format.
xlMYDFormat	6	MYD date format.
xlSkipColumn	9	Column is not parsed.
xlTextFormat	2	Text.
xlYDMFormat	8	YDM date format.
xlYMDFormat	5	YMD date format.

 

[arnelgp]

IMO, you should first check the "Date To" string and parse the date?

strDateTo = (Cdate("theParsedDateTo") & "")=> will give you the string date in English(EN)

then compare the resulting string to "theparsedDateTo". if they are the same, re-format the date column as Englisjh(EN),
otherwise as English(UK), by Split() and Join().

 

[Edgar_]

 'Start Excel
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True

    ' Open the file
    Set ws = xlApp.Workbooks.Add.worksheets(1)
    With ws.querytables.Add("Text;" & srcFile, ws.range("A1"))
        .TextFileStartRow = 9
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(1, 3, 1, 1, 1, 1, 1) '<-- second column format is 3
        .Refresh False
    End With

No matter what you do, if you need to import a CSV file into Excel, you should always use the import from text option from the data tab. Its equivalent in VBA is a QueryTable object. Not doing this results in problematic CSV files, either because of the encoding, the delimiter or, like in this case, the date format.

In an english version of Access, if you have a date column with DMY format, set the column to xlColumnDataType 3 in the .TextFileColumnDataTypes property of the QueryTable object.

 

[DickyP]

@DickyP - completely agree, but the level of manipulation is high

The bank in question has a new system with many changes, in particular to do with exporting transactions
1. Account number (to identify which nominal code to be updated) - used to be in the file name, now it is in the first few rows of text
2. Date To - used to identify the file - used to be in the file name, now it is in the first few rows of text
3. Values - used to be 2 columns, now one column - requires two calculated columns to get back to 2
4. Order - used to be latest at bottom, now latest on top (requires creating a sort column and then sort descending
5. Rows - most rows take 6 columns, but now some take 7 - and the 7th column is inserted in the 4th position - so the value might be in the 6th column or the 7th, depending

And if you want to look at historical data from the old system - records are returned completely randomly with incorrect closing balances (one of the fields I use to validate the data)

I have suggested the client might want to change banks

Whilst I sympathise the real question is are these files in a standard format? If so then manipulation in text is possibly long winded but only needs defining once. And it's the way it should be done! I agree with your final remark!!

I personally would probably do it using regular expressions, but that's just me.

 

[MsAccessNL]

I am working on a similiar project, importing bankstatements from different banks.

I have choosen to use dao to import the csv files and use copyfromrecordset into the body of an already formatted Excel table, the format, like the dates, are respected thisway. (No idea how if this will work with the American date problem).

To handle all the different bank lay-outs and get them in one table is really a challenge, i am curious how you solved that problem.

Are you willingly to share your solution?

 

[Edgar_]

After some testing, this is the combination of previous format and import format that worked for an english installation of Office. The installation of Office wasn't the culprit, it was the Regional settings of Windows. If the system's date has the MDY format, this code will work:

Sub testEng()
    Dim xlapp As Object
    Dim ws As Object
    'Start Excel
    Set xlapp = CreateObject("Excel.Application")
    xlapp.Visible = True

    ' Open the file
    Set ws = xlapp.Workbooks.Add.worksheets(1)
    ws.Columns(2).numberformat = "dd/mm/yyyy;@"
    With ws.querytables.Add("Text;" & CurrentProject.Path & "\Transaction Report.csv", ws.range("A1"))
        .TextFileStartRow = 9
        .TextFileCommaDelimiter = True
        .Refresh False
    End With
End Sub

Format the column first, leave the import format as general, which is the default format, so I don't need to specify it.

And this for other languages And if the system's date has DMY or YMD format, then this code should be used:

Sub testOtherLang()
    Dim xlapp As Object
    Dim ws As Object
    'Start Excel
    Set xlapp = CreateObject("Excel.Application")
    xlapp.Visible = True

    ' Open the file
    Set ws = xlapp.Workbooks.Add.worksheets(1)
    ws.Columns(2).numberformat = "dd/mm/yyyy;@"
    With ws.querytables.Add("Text;" & CurrentProject.Path & "\Transaction Report.csv", ws.range("A1"))
        .TextFileStartRow = 9
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(1, 3, 1, 1, 1, 1, 1) '<-- second column format is 3
        .Refresh False
    End With
End Sub

Format the column first and then import with format by specifying it