Forum Discussion

Copper Contributor

Feb 22, 2024

Microsoft Excel not formatting CSV file correctly

We are trying to open a CSV file with Microsoft Excel (2013 and 2016) on Windows Desktop. Unfortunately, excel is not formatting the file correctly: each row is in a new row as it should be, but all ...

excel

AndreasKiller

Copper Contributor

Feb 22, 2024

wumolad

In most cases this did not work due to the region settings, so e.g. the DMY order night be different, numbers can have a comma as decimal separator... etc.

This is a FAQ, you have to import the file:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-msoversion_other/power-query-how-to-import-a-csv-file-that-does-not/e19fe942-80ad-4ac1-a164-061e7cac9486?tm=1612610313469

Andreas.

OfficeWillYouWorkPls

Copper Contributor

Feb 22, 2024

Hey Andreas, thank you so much for your reply! The issue is that we need to frequently update the original file, and not change the delimeter of the file itself, as it is then used as part of a java program - also, we've changed the regional setting of the computer so that the system delimeter is comma ( , ) rather than semicolon now, but for some reason the issue persists...

AndreasKiller
Copper Contributor
Feb 23, 2024
OfficeWillYouWorkPls

Changing the region settings is the worst what you can do! Reset the settings immediately:
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-msoversion_other/system-settings-how-to-import-a-csv-file-that-does/3376be5b-ec44-435a-9058-8d8ae573db2c?tm=1612615673550

Then read the article I mentioned in my last post, which you obviously didn't do. In there I explain in details how to import any kind of CSV file without issues.

Andreas.