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

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.

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.

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

Ok, thank you for the clarification
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies