Home

Decimal separator after CSV import

%3CLINGO-SUB%20id%3D%22lingo-sub-134617%22%20slang%3D%22en-US%22%3EDecimal%20separator%20after%20CSV%20import%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-134617%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EI%20need%20some%20help%2C%20please.%20I'm%20trying%20to%20import%20a%20csv%20file.%20The%20problem%20is%20that%20numbers%20are%20written%20with%20comma%20decimal%20separator%2C%20but%20my%20PC%20is%20working%20with%20dots%20as%20decimal%20separator.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20previous%20Excel%20versions%2C%20there%20was%20the%20possibility%20to%20convert%20them%20during%20the%20import%20wizard.%20Unfortunately%2C%20I'm%20not%20able%20to%20find%20the%20same%20option%20in%20the%20new%20import%20wizard%20(Power%20Query).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20for%20all%20your%20answers%3C%2FP%3E%0A%3CP%3ELuca%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-134617%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ecsv%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eimport%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-323655%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20separator%20after%20CSV%20import%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-323655%22%20slang%3D%22en-US%22%3EOk%2C%20thank%20you%20for%20the%20clarification%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-323653%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20separator%20after%20CSV%20import%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-323653%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20may%20set%20PQ%20default%20locale%20only%20for%20current%20workbook%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-323426%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20separator%20after%20CSV%20import%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-323426%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20this%20works.%20But%20is%20it%20possible%20to%26nbsp%3Bchange%20so%20that%20the%20query%20options%20are%20always%26nbsp%3Bset%20to%20a%20chosen%20language%3F%20No%20matter%20what%20language%20I%20choose%20as%20the%20Excels%20default%26nbsp%3Blanguage%20in%20the%20Excel%20option%2C%20the%20query%20option%20still%20has%20English(USA)%20as%20a%20default.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314216%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20separator%20after%20CSV%20import%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314216%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%20would%20appreciate%20some%20help.%20New%20laptop%20with%20windows%2010%20%26amp%3B%20office%20365.%20Unable%20to%20download%20csv.%20files%20to%20excel%20%26amp%3B%20separate%20info%20to%20columns%20with%20comma%20of%20the%20csv%20format.%20Region%20settings%20English%20South%20Africa.%20Region%2C%20Format%2C%20Additional%20Settings%20-%20list%20separator%20is%20a%20comma%20%26amp%3B%20even%20resetting%2C%20double%20OK%20button%20%26amp%3B%20reboot%20doesn't%20change%20the%20format.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-136769%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20separator%20after%20CSV%20import%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-136769%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Luca%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETwo%20options%20if%20you%20use%20Power%20Query%3C%2FP%3E%0A%3CP%3E1)%20In%20ribbon%20Data-%26gt%3BGet%20Data-%26gt%3BQuery%20Options-%26gt%3BRegional%20settings%20change%20the%20locale%20on%20French(France).%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20316px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F25543i00B5862B9D8FE27F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Regional.JPG%22%20title%3D%22Regional.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20importing%20CSV%20file%20use%20Tab%20as%20delimiter%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20350px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F25544iB001E15270BDAE07%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Delimeter.JPG%22%20title%3D%22Delimeter.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)%20Without%20above%20import%20as%20it%20is%2C%20remove%20automatic%20Change%20Type%20step%20if%20appears%2C%20select%20columns%20shall%20be%20converted%20to%20numbers%2C%20and%20from%20right%20click%20menu%20use%20Change%20Type-%26gt%3BUsing%20locale.%20Select%20French%20as%20the%20locale%20of%20origin%20and%20Decimal%20number%20as%20the%20type%20to%20convert.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-136511%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20separator%20after%20CSV%20import%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-136511%22%20slang%3D%22en-US%22%3EYou%20need%20to%20have%20a%20french%20version%20to%20do%20that%20and%20use%20%3B%20as%20a%20field%20separator%2C%20if%20you%20use%20%2C%20as%20a%20separator%20and%20also%20a%20%2C%20as%20decimal%20point%20yo%20cannot%20solve%20this%20withour%20going%20back%20to%20the%20creator%20of%20the%20csv.%3C%2FLINGO-BODY%3E
Highlighted
Luca Rivoira
Occasional Visitor

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

6 Replies
Highlighted
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.
Highlighted

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).

Regional.JPG

and importing CSV file use Tab as delimiter

Delimeter.JPG

 

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.

Highlighted

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.

Highlighted

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.

Highlighted

You may set PQ default locale only for current workbook

Highlighted
Ok, thank you for the clarification
Related Conversations
Import a CSV File and Pivot tables
gabriellerocha in Excel on
3 Replies
reverse text to columns for .csv import
milly87 in Excel on
3 Replies
In the developer tab Import XML is missing
TiesJ in Excel on
0 Replies