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

[CJ_London]

I'm using a sub in ms Access to open an instance of Excel, which in turn opens a csv file. It then manages the data into a standard format (delete header rows, calculate some columns and change the sort order) to then save as a csv file for subsequent upload into an Access table.

The files are all csv or .html files of transactions downloaded from various banks. Each bank has a different format for file naming, order and names of columns, header information etc. Some have latest transaction at the top, others at the bottom and I have a a table containing various parameters for which rows to delete, whether to change the sort order, etc.

It all works well, except for one thing

If I open the file manually from excel, the data appears as it should. However if I use vba code (late binding)

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

    ' Open the file
    Set wb = xlApp.Workbooks.Open(srcFile)
    Set ws = wb.Sheets(1)

Date columns are a mixture of text and numbers

and the numbers are based on the US format date. So the correct date is 1st July - but interpreted as 7th Jan - whilst the 14th July is correct - but text (which I can live with). I believe this is due to the assumption a date is in US format (1st July) unless it is unambiguous (14th July)

Any suggestions for resolving this? I've tried chatGPT, which didn't come up with anything useful. Basically I need to force the date column to be UK or SQL standard of yyyy-mm-dd - and obviously getting the correct date

 

[Trevor G]

Hi what about recording a macro in Excel to either import and use the text to columns to sort the date format or open the CSV and change the format. That way you have code that can be adapted and run from Access.

If you could share a sample, obviously no real data and I'd be happy to take a look and develop some Excel VBA code for you. If acceptable.

 

[CJ_London]

The .csv file looks like this - dates are correct. Open in Excel, and dates are correct

I've deleted sensitive data and attached the file - use the code in post#1 and just change srcFile to the path and name of the file. You'll see the 1st July dates change 7th Jan.

you can use OLE to open the workbook, set the format for the date column and close and save the workbook.

I've tried that - but these are .csv and .html files, I've also tried changing Excel settings to treat dates as text but to no avail

what about recording a macro in Excel to either import and use the text to columns to sort the date format or open the CSV and change the format

That is what I did originally - until I realised the issue is with opening the file - the data changes at that point. I might be able to have an excel macro file and use that - problem will be passing the name of the file to the macro. Just seems a complex way of handling what should be a simple import.

 

[Trevor G]

Ok im away from my laptop just know. A quick thought? If the CSV file is stored in a folder and only that file then the file name shouldn't be an issue if the code looks for "*.cvs"

I'll download and explore some options and I'll keep an eye on your thread to see if anyone else has taken a look and made any suggestions.

 

[gemma-the-husky]

The .csv file looks like this - dates are correct. Open in Excel, and dates are correct

View attachment 120559
I've deleted sensitive data and attached the file - use the code in post#1 and just change srcFile to the path and name of the file. You'll see the 1st July dates change 7th Jan.


I've tried that - but these are .csv and .html files, I've also tried changing Excel settings to treat dates as text but to no avail


That is what I did originally - until I realised the issue is with opening the file - the data changes at that point. I might be able to have an excel macro file and use that - problem will be passing the name of the file to the macro. Just seems a complex way of handling what should be a simple import.

Could it be because you have English dates, so some dates are rejected. Are the invalid dates actually being displayed as strings, and the valid dates getting the correct numerical values?

Is there an istext() function you can use to test whether the 14/07/2025 you see after the import is text or date?

 

[Minty]

@Pat Hartman - I'm afraid I've come across a similar issue with UK dates in CSV's and Excel - it's definitely an Excel "trick." No special characters etc. it's just Excel being a pain in the ass.

@CJ_London - I did find a work around, but cannot find the database or process to remind myself what I did with them at the moment.

 

[CJ_London]

Could it be because you have English dates, so some dates are rejected. Are the invalid dates actually being displayed as strings, and the valid dates getting the correct numerical values?

Yes - As mentioned, the problem is any date from 1st to 12th is treated as US style and we are in the UK

I would guess that some of the "dates" have either special characters embedded or the values are enclosed in quotes.

@Minty - will be very interested to see what you did

I've just tried creating an xlsm to run the import and opened and executed from Access. Same issue.

My old routine opens the csv file in Access without an issue. This all came about because one of the banks has changed the layout to one that needs a lot of manipulation to be able to successfully import - and that manipulation is a lot easier to do in Excel than Access and I'm trying to keep the import routine to a simple path, not completely different paths depending on the bank - but it may be a route I will need to go

 

[DickyP]

Rule 1 - never process csv files in Excel.
Rule 2 - repeat Rule 1.

Always manipulate csv files as text!

 

[GPGeorge]

The .csv file looks like this - dates are correct. Open in Excel, and dates are correct

View attachment 120559
I've deleted sensitive data and attached the file - use the code in post#1 and just change srcFile to the path and name of the file. You'll see the 1st July dates change 7th Jan.


I've tried that - but these are .csv and .html files, I've also tried changing Excel settings to treat dates as text but to no avail


That is what I did originally - until I realised the issue is with opening the file - the data changes at that point. I might be able to have an excel macro file and use that - problem will be passing the name of the file to the macro. Just seems a complex way of handling what should be a simple import.

This may not help, but I thought I'd mention it in case there's something you can take from it.

We had a client that needed to import csv files into their accounting system. However, the source files were in a proprietary format exported by a different system.

We used Access to open the proprietary files, parse the data, and export the results back out to csv format.

The problem came in if a user opened the csv files to inspect or verify. As you note, opening them changed data so that they could no longer be imported into the accounting system.

Our solution was to export two csv files, one for humans and the other for the accounting system. That allowed for auditing, but also protected the virgin copy of the csv.

Perhaps you can avoid the opening of the files which seems to be the problem.

 

[CJ_London]

@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

 

[CJ_London]

you can use OLE to open the workbook, set the format for the date column and close and save the workbook.

Is this what you mean? Since we are talking about a CSV file, I wasn't sure what you were suggesting since the issue is Excel opening the file

Perhaps you can avoid the opening of the files which seems to be the problem.

the problem is opening the file using Excel opened in Access. It works fine if I use SQL on the csv file but manipulating it without going too far off-piste to get the required outcome is the problem - which I thought Excel would solve - which it does, but introduces the date issue

 

[CJ_London]

I would also read the .csv file to process it.

this is what I used to do, quite happily for several years. See post 12 for the manipulation required.

I can solve 1 and 2 easily enough with sql, 3 is not too bad but a departure from the standard process for other banks, 4 is a problem as I need to introduce a sort column to reverse the order - otherwise I need to reverse the order of the last 3 years data so it is all consistent. That can be done, but may have an impact on reporting. Still thinking about 5 - probably a nested union query.

Point is I'm importing data from several banks. All have their variations, but they are minor in comparison primarily a different column order (solved by using alias field names) and a defined sort order (which this bank has now reversed).

 

[gemma-the-husky]

This may not help, but I thought I'd mention it in case there's something you can take from it.

We had a client that needed to import csv files into their accounting system. However, the source files were in a proprietary format exported by a different system.

We used Access to open the proprietary files, parse the data, and export the results back out to csv format.

The problem came in if a user opened the csv files to inspect or verify. As you note, opening them changed data so that they could no longer be imported into the accounting system.

Our solution was to export two csv files, one for humans and the other for the accounting system. That allowed for auditing, but also protected the virgin copy of the csv.

Perhaps you can avoid the opening of the files which seems to be the problem.

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.

 

[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

 

[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?