PowerPivot decimal seperator on refresh

[jroz2001]

For a couple of years I am a happy PowerPivot user. However, I've got an issue I haven't experienced before:
I have a CSV file, delimited by ';', with some decimal numbers in it. In my country, we use ',' as decimal seperator.

When I import this CSV file with PowerPivot, I convert some columns from text to decimal number, and all is well. However, when I refresh this data in PowerPivot, my seperator is gone, and 1234,56 turns into 123456. Is there anyone out here who knows why this is happening?

What I already have tried/ checked:
- All settings on my pc are also set to use ',' as decimal seperator.
- Change advanced settings > Coding and country settings when importing this CSV in PowerPivot
- Create a schema.ini file
- Changing ',' to '.' in the CSV --> Works, however not the solution I want.

Workaround for now, when refreshing, first change those fields to text > refresh > change back to decimal number, but I would like to not use a crappy workaround.

Thanks in advance!

 

[sandy666]

try PowerQuery
import CSV
delimiter: ;
set column as text
Query Options - Regional Settings: your country (it should convert , to . )
set column as decimal
load to Data Model

 

[jroz2001]

That's an option indeed, however some others should also have the possibility to update it, but they don't have PowerQuery, but do have PowerPivot. So I would like to only use PowerPivot.

 

[sandy666]

If you've PowerPivot only it means this is add-in so you can get PowerQuery add-in
if you've PowerPivot built-in it means you've PowerQuery (Get&Transform) built-in
if you want resolve this problem with PowerPivot only try to call Microsoft

 

[jroz2001]

I've got both as add-in, but not everyone within the company has PowerQuery. Install it on every pc is of course an option, but prefer to solve it within PowerPivot. If this is not possible, so be it, but it's kinda stupid that PowerPivot can handle comma as decimal seperator when I first load an CSV, but can't handle it when refreshing, right? So that's why I posted this question, maybe I'm doing something wrong, or maybe there is an solution within PowerPivot for this.

 

[sandy666]

One of option is changing your local settings (system) because PowerPivot use it for recognize "style" of imported file (not tested - make Office compatible with System)

 

[jroz2001]

Those where already set to recognize ',' as decimal seperator.

 

[sandy666]

one more

system: ,
excel: , (Options-Advanced-Editing Options-Use Sytem Separators [or set your own])
PowerPivot - Table Import Wizard - Advanced : set your country language (it doesn't work for me but maybe for you )

if not - I give up

 

[jroz2001]

if not - I give up

I will do the same, unfortunately. Thanks anyway for trying to help me out!

 

[sandy666]

You are welcome. I strongly recommed PowerQuery