Forum Discussion
Decimal separator after CSV import
Hi,
I need some help, please. I'm trying to import a csv file. The problem is that numbers are written with comma decimal separator, but my PC is working with dots as decimal separator.
In the previous Excel versions, there was the possibility to convert them during the import wizard. Unfortunately, I'm not able to find the same option in the new import wizard (Power Query).
Thanks for all your answers
Luca
9 Replies
Hi Luca,
Two options if you use Power Query
1) In ribbon Data->Get Data->Query Options->Regional settings change the locale on French(France).
and importing CSV file use Tab as delimiter
2) Without above import as it is, remove automatic Change Type step if appears, select columns shall be converted to numbers, and from right click menu use Change Type->Using locale. Select French as the locale of origin and Decimal number as the type to convert.
Hi Sergei, your solution works well, except for data including a currency sign. How can I fix these data during the import ?
thanks
JC
In Power Query editor select such column(s), Transform->Replace Values. In Value to Find $ in Replace With nothing, Ok. Select column again, click on icon on icon on the left of header (most probably ABC), apply Currency data type from drop-down menu.
Thank you this works. But is it possible to change so that the query options are always set to a chosen language? No matter what language I choose as the Excels default language in the Excel option, the query option still has English(USA) as a default.
You may set PQ default locale only for current workbook
Greetings would appreciate some help. New laptop with windows 10 & office 365. Unable to download csv. files to excel & separate info to columns with comma of the csv format. Region settings English South Africa. Region, Format, Additional Settings - list separator is a comma & even resetting, double OK button & reboot doesn't change the format.
- You need to have a french version to do that and use ; as a field separator, if you use , as a separator and also a , as decimal point yo cannot solve this withour going back to the creator of the csv.
Toto DeveloperThe decimal point is moved for some number. From my point of view this is caused by hiring people who think that they are superintelligent. Indeed in previous versions the decimal point was not moved; it used to work and is not any more. Who did this is responsible to restore the working version in its original state.
The famous example is that you go with a cushing to a roof an release all feathers in the wind. Then you start to collect all feathers and put them back in the pillow
