[Article] Power Query - How to import a CSV file that does not match your locale settings

Question

📌 Note: This article was originally created by Andreas Killer, a valued member on Answers Support Community. It provided meaningful insights and proved helpful to many. We're recreating it here on their behalf to preserve its value and ensure continued access for others.

A CSV file does not contain any information about the format of the data in it. That means when you open a CSV file in Excel, it only works if the data format is the same as in your control panel.

And if you try to open a CSV file with any other data format, you're in trouble ... until you read this article.

Please never change the system settings of your system to avoid this problem.

Here is a CSV example which has a German data format, let’s try to import the data on an English system and see what happens. https://www.dropbox.com/s/lzyxv342o9ntvze/GermanData.csv?dl=1

All of the following screenshots are from Excel 2019, some parts might be different in your version, but works the same way in all versions of Excel since Excel 2010.

If you use Excel 2010 or Excel 2013 and do not already have Power Query installed, you can download the last version here: Download Microsoft Power Query for Excel from Official Microsoft Download Center

In Excel 2016 and later Power Query is build in and renamed to Get & Transform in the UI of Excel, but it’s the same thing.

So let us import that file

.

The Power Query preview window opens and at the first glance it did not look so bad. Power Query tries to guess the data format and it is right that a semicolon is the delimiter.

If you only have numbers with a comma as the decimal separator in your CSV file, then PQ will recognize a comma as the delimiter, which is obviously wrong.

In this case, change the delimiter to a semicolon in this step.

.

Let’s click Transform Data and see what we get.

.

The first 2 columns looks great, but the decimal number should be 1.234 because the German number format has a comma as decimal separator. The currency is a text, same as the date.

Some people delete the Change Type step in Power Query to get the text and then perform a lot of steps to get the numerical data … I don’t know why. Here is the simple way out of this disaster:

Click File in the menu and open the Query options

.

Change the regional settings of the current workbook to the locale of the CSV file, German in this example.

.

This setting does not affect your workbook or other workbooks or Excel, it is only how the query interprets text.

Click Ok and close the Power Query editor by a click on the X at the top right of the window.

.

And discard the changes!

.

Power Query does not discard our change in the regional settings.

Now let us import the same CSV file again and take a look what we get

.

Much better except for the date part, that is now a number. Power Query has a problem to recognize the date, because in Germany the thousand separator and the date delimiters are both a dot.

If we try to change it to a date as usual we get garbage. Also using a locale leads to garbage. What’s wrong?

Nothing, the problem is the automatically created Change Type step! Power Query simply guessed wrong.

If we look at the MCode we can see that the Date is changed to an Int64.Type (an integer). = Table.TransformColumnTypes(#"Promoted Headers",{{"Text", type text}, {"Text with delimiter", type text}, {"Decimal number", type number}, {"Currency using thousand separator", Currency.Type}, {"Date", Int64.Type}})

Advanced users can change the MCode directly and we’re done. All others can follow this steps:

Right click the date column and change the type to Text

.

Power Query asks you if you like to insert or replace the current step, we want to replace it, because it is wrong.

.

Now we can see the text from the CSV file

.

Once again, right click the Date column and change the type to Date

.

Once again, replace the current step and we get

.

Perfect, that’s my birthday! Close and load the data to the workbook.

Finally we can take a look at the MCode for the next time:

= Table.TransformColumnTypes(#"Promoted Headers",{{"Text", type text}, {"Text with delimiter", type text}, {"Decimal number", type number}, {"Currency using thousand separator", Currency.Type}, {"Date", type date}})

Basically all we have done is to replace "Int64.Type" with "type date".

That’s it. Have fun, stay healthy, Andreas.