Countries with comma as decimal setting - How do you handle this for importing CSV files?

jtkerlin
Occasional Contributor

Countries with comma as decimal setting - How do you handle this for importing CSV files?

We just encountered a bit of a hiccup with some of our countries saving and loading csv files to our model.  It turns out that countries using comma as the decimal have this issue in Microsoft Excel.  When they save as a csv, it uses a semicolon as the separator.  Even if you change the separator option in Windows/Excel to a period for these countries, it will still save the file with semicolons separating the data rather than commas.  Because Anaplan does not recognize the semicolon as a separator it fails to load.

 

The solution that we have verified with Anaplan Support is for those users to change their number setting, then save the file as a csv.  After saving the file, changing their number setting back for their regular reporting needs.

 

Are there any other workarounds you have identified and used?  The only other one would be to change the import file to txt.

 

 

To change the number setting in Excel - Go to File, Options, Advanced, uncheck Use system separators, change decimal to period and thousands to comma.

 

@PrafullaK @JaxD 

1 ACCEPTED SOLUTION

Accepted Solutions
LipChean_Soh
Certified Master Anaplanner

Re: Countries with comma as decimal setting - How do you handle this for importing CSV files?

Hi @jtkerlin 

 

After you click 'Data' > 'Import', you'll be asked to select a source. Select the csv file with the comma as decimal and semicolon as text separator, then pls select the following as shown, i.e.

1. Column Separators = Semicolon

2. Decimal Separator = Comma

LipChean_Soh_0-1623111577591.png

 

Thanks,

LipChean

 

View solution in original post

7 REPLIES 7
LipChean_Soh
Certified Master Anaplanner

Re: Countries with comma as decimal setting - How do you handle this for importing CSV files?

Hi @jtkerlin 

 

After you click 'Data' > 'Import', you'll be asked to select a source. Select the csv file with the comma as decimal and semicolon as text separator, then pls select the following as shown, i.e.

1. Column Separators = Semicolon

2. Decimal Separator = Comma

LipChean_Soh_0-1623111577591.png

 

Thanks,

LipChean

 

View solution in original post

jtkerlin
Occasional Contributor

Re: Countries with comma as decimal setting - How do you handle this for importing CSV files?

Thanks for the tip, I did not realize you could change that setting for csv.

 

Would I create two different processes then, one for countries who use comma as decimal and one who use period as decimal?  We have a mix of countries importing files.

 

 

LipChean_Soh
Certified Master Anaplanner

Re: Countries with comma as decimal setting - How do you handle this for importing CSV files?

Hi @jtkerlin 

 

If there's absolutely no way to standardize the csv file into 1 format, then i'm afraid you're right, you'll need 2 separate actions, i.e. one for countries who use comma as decimal and one who use period as decimal.

 

Thanks,

LipChean

jtkerlin
Occasional Contributor

Re: Countries with comma as decimal setting - How do you handle this for importing CSV files?

Thank you very much for your help.
jtkerlin
Occasional Contributor

Re: Countries with comma as decimal setting - How do you handle this for importing CSV files?

I noticed that you can have comma and semicolon clicked at the same time in the load file settings. Is there a best practices for this - to do it in one grouping or to create two separate actions?
LipChean_Soh
Certified Master Anaplanner

Re: Countries with comma as decimal setting - How do you handle this for importing CSV files?

Hi @jtkerlin 

 

There really isn't a best practice for this, because different laptops settings setup their column separators differently, so i guessed consistency is what you should aim for, if possible.

 

Technically, the same import definition can deal with both comma and semicolon as column separator, i.e. 1 import action for 2 different csv formats.

 

1. Comma as column separator

LipChean_Soh_0-1625101093894.png

LipChean_Soh_1-1625101108255.png

 

2. Semicolon as separator 

LipChean_Soh_2-1625101186970.png

LipChean_Soh_6-1625101450976.png

 

 

However, it becomes a problem if some of your users adopt the European way of using comma as decimal separators, as shown below. Notice how the comma column separator definition is applied to split 9,85 into 2 columns.

LipChean_Soh_4-1625101324218.png

LipChean_Soh_5-1625101391246.png

 

So i would encourage you to find out what combinations of csv you have amongst your users, and then try to see if everyone can follow the same format. If not, you might need to do some tweaking to the import mapping definition, OR come up with more than 1 import definition for the same import.

 

Thanks,

LipChean

 

jtkerlin
Occasional Contributor

Re: Countries with comma as decimal setting - How do you handle this for importing CSV files?

Thank you, I knew there was something we were not thinking through by doing the shortcut of just putting comma and semicolon as the separator.