How to fix file .csv error data columns are replaced with commas

Question

My company is using MS365 Business Standard and i'm having issues with .csv files. When opening .csv files, the data formatting in the file encounters errors, with data sticking together and separated by commas. All the data is in one column.

Link image: Error data form

I have to use the "Get Data from Text" method to correct the file format. However, in some cases, decimal numbers converted to sequences like 50.8928392 will have additional dots inserted between the numbers, resulting in 50.893.839.2

Link image: After fix with method

Is there a way to completely resolve this issue? This situation only occurs on a few laptops.

Answer 1

Yes.

The entire context is not just comma and dot, you also have dates and times in the CSV which also must be converted into numbers.

I guess that the "English (UK)" locale should convert your CSV into correct data.

BTW, you need to setup all this only once. If you get new data overwrite the old CSV file, open your Excel file and click Data \ Refresh All and you're done.

Andreas.

Answer 2

Great, thank you.

This is the only correct way to handle it, right?

Answer 3

Import the file using Power Query, all detailed steps in here:

Power Query - How to import a CSV file that does not match your locale - Microsoft Community

Andreas.