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

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

 

[Pat Hartman]

The problem is with the sloppy way the Excel sheet is formatted. To have a consistent format, you need to either open the workbook and format the date column manually or if this is other than a one-time problem, you can use OLE to open the workbook, set the format for the date column and close and save the workbook. Then when you open the workbook you will have a consistent date format and then we can deal with that.

 

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

 

[Pat Hartman]

CJ. .csv is a text file. .xlsx is a proprietary, non-text format. When you open the .csv file in Excel, why does Excel think that some of the values are strings and some are actual dates? I would guess that some of the "dates" have either special characters embedded or the values are enclosed in quotes.

The other possibility is that your PC is not set for UK date format as the default. If I open the test file, using a text editor, I don't see anything that indicates that some of the dates are text and some are dates. However, if US is the default date format as it is on my PC, then 14 is not a valid month and therefore Excel is interpreting those values as strings. So, that sort of makes sense. I cannot change those dates by simply setting the column to short date format, most likely because my PC uses US format dates setting.

If you use the OLD text import wizard ONCE, you can specify that the dates are in d/m/y order to create an import spec and then Access will see the dates correctly when you add the spec name to the import command.

 

[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

 

[Pat Hartman]

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

I provided a solution in my initial reply. It is one of those "no code" solutions.

 

[Pat Hartman]

Since the file is a "report" rather than a proper data file AND it has non-US format dates, I would also read the .csv file to process it. You can define the date fields as text OR use the import spec to define the date fields as dates with format d/m/y.

 

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

 

[Pat Hartman]

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

That was the solution if there really were two different formats. Once I convinced myself that, that wasn't the problem (sorry I didn't let you in on my change of heart), I moved on to defining a spec that defined the date correctly for the .csv file.

If you use OLE to manipulate the Excel workbook and then save the result as .csv, you can add the spec to get past the date format problem. So, maybe the simple solution is to use both OLE to fix the other problems, then opening as .csv and using a spec to fix the date problem.

 

[Pat Hartman]

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

I solved this problem by having the client buy an OCR program that let me get rid of the report crap and just save the data cleanly as a .csv file. At least our banks always used the account number as part of the file name and also the closing date. Their formats were not identical but both pieces of data were available in the file name.

I also had to create a log table to ensure that no statement ever got imported out of sequence. The statements came via email so the user saved the statements to the import folder and when all the bank statements for the month were received, she fired up the OCR program which processed all the files in the import folder and wrote them to the export folder as .csv's. Then she fired up the Access app which processed the .csv's and then at the end of the process, the Access app archived both the import files and the .csv's so both folders were empty for next month.

 

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

 

[Pat Hartman]

For what it's worth, I would much rather process a csv file than an excel file.

Me too. Try creating the import spec to fix the date issue if you need to. Although I think you shouldn't have a problem if you open the .csv file directly.

The big problem with opening .csv's with outlook is what it does to long numeric fields. Excel tries to make them numbers and in the process converts them to scientific notation.