Solved problem opening a .csv file in excel - dates all messed up

[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

 

[CJ_London]

@xavier.batlle - you're a star - that works.

With xlWS.QueryTables.Add(Connection:="TEXT;" & csvPath, Destination:=xlWS.Range("A1")) QueryTables is the key

I had tried similar to no benefit using fileinfo (also generated by chatGPT)- but the difference was it suggested using workbooks.opentext

@Edgar_ - I tried import from text, but did not solve the date issue - but again, I did not use ws.querytables.Add. I've not tested your code, but not is a position to change regional settings

So I've learned something new - querytables

@DickyP - for each bank, they say it is their standard format, so I have to live with it. One bank sends a .xls which generates a 'not in standard format' error when opened directly by Excel, but not when using workbooks.open. Turns out it is actually a html file - looking at the file in notepad, shows lot's of Spanish phrasing which is probably the reason why it errors on open. They also provide a .csv download, but that perversely does not include the balance column which I need for data validation.

@MsAccessNL - happy to share once I have incorporated it into the process. I have a couple of tables that store parameters such as where and how to find the account number, the various columns, any calculations etc which are used to generate a standard csv file for import. The objective is if clients get a new bank (or they change formats) it can be accommodated without needing to write more code. However I hadn't catered for such a significant change!. Right now I have more urgent work to address so it will be a week or so.

All - many thanks for your contributions, I will mark this thread as solved

 

[arnelgp]

@xavier.batlle - you're a star - that works.

you would have said that to ChatGPT

 

[CJ_London]

Well he obviously phrased the question better than I did