Forum Discussion

Luca Rivoira's avatar
Luca Rivoira
Copper Contributor
Dec 06, 2017

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 p...
csv
excel
import
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 13, 2017

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.

  • Jean-Charles_M's avatar
    Jean-Charles_M
    Copper Contributor
    Sep 08, 2023

    SergeiBaklan 

    Hi Sergei, your solution works well, except for data including a currency sign. How can I fix these data during the import ?

    thanks

    JC

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 08, 2023

      Jean-Charles_M 

      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.

  • andersoonasd's avatar
    andersoonasd
    Copper Contributor
    Jan 22, 2019

    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.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 22, 2019

      You may set PQ default locale only for current workbook

      • andersoonasd's avatar
        andersoonasd
        Copper Contributor
        Jan 22, 2019
        Ok, thank you for the clarification
  • Gregmac's avatar
    Gregmac
    Copper Contributor
    Jan 13, 2019

    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.

Resources