Forum Discussion
Microsoft Excel not formatting CSV file correctly
Select the cells and click on Data, then Text to Column as shown below
Then choose Delimiter. Click on Next and select Comma on the next page, it will show you the preview of the solution.
Click on finish and you should have the data properly arranged in the columns.
Cheers
wumolad thank you so much for your reply! the issue is that we need to regularly update the ORIGINAL file, so that solution sadly does not apply in this case:/
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.
- 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...
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=1612615673550Then 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.
