Importing CSV Data, Detection of Structure Inconsistent

Question

I use the data import feature of Excel a lot at work and occasionally at home. When I do I use the PQE (Power Query Editor) by pressing the Transform Data button.

I've run into a situation where I downloaded two files from a bank in CSV format. They are formatted exactly the same. The difference is the data found in the three columns.

One file shows all three columns and correctly defaults to comma delimiter while the second file incorrectly defaults to colon delimiter and shows two columns. If I change the delimiter to comma the number of columns displayed is reduced to one. The first column has date and time and the colon in the time is what Excel is determining is the delimiter rather than the two commas that separate the three columns.

From my perspective, this is a bug. The files are formatted exactly the same. The difference is the data contained between the commas. Neither file have quotes around the data for any of the columns.

lorenzo · Answer

HiFrom my perspective, this is a bugMaybe, maybe not…If you're after a solution could you share (after changing a few numbers if too sensitive) the 2 Csv?

sergeibaklan · Answer

Agree with Lorenzo , without sample file it's hard to help. In general functionality works as expected, at least on Excel desktop for Windows.

iam6ft7in · Answer

Please find attached a file that is formatted exactly as both of the other files where one works and the other does not. Besides the differences in terms of the transaction data, the only difference between the two files is on the second line of each file.

File One

DateRange: 11/01/2021 - 11/08/2024
Account Type: Card Account
Date Timestamp - EST,Description,Amount

File Two

DateRange: 11/01/2021 - 11/08/2024
Account Type: Deposit Account
Date Timestamp - EST,Description,Amount