Power Pivot CSV Number Formats

[walshj5]

I have a combined CSV file with three years of data (debits and credits). There are about 1.7m rows. When I import to Power Pivot, the the numbers with decimals change from for example .94 to 1.00. What am I doing wrong?

 

[scottsen]

That questions always makes Rob Collie shake his head. YOU did not do anything wrong... the software did.

Here is my guess (and it's just a guess... as this doesn't sound 100% familiar -- usually I see this with a bunch of number rows, then letters blow you up)... A whole bunch of columns at the beginning of your data were whole numbers. Later on... you started getting some decimal numbers. But by then Power Pivot had incorrectly "guessed" the column was of whole numbers.

*If* that is the case, I know of a few workarounds... none are perfect
* Somehow re-arrange your data to get some decimals first.
* Use Power Query to bring in your data, it tends to be more awesome for this.
* There is such a thing as a "format file" for a CSV. I have never actually created one, but that would probably solve this.

 

[walshj5]

That questions always makes Rob Collie shake his head. YOU did not do anything wrong... the software did.

Here is my guess (and it's just a guess... as this doesn't sound 100% familiar -- usually I see this with a bunch of number rows, then letters blow you up)... A whole bunch of columns at the beginning of your data were whole numbers. Later on... you started getting some decimal numbers. But by then Power Pivot had incorrectly "guessed" the column was of whole numbers.

*If* that is the case, I know of a few workarounds... none are perfect
* Somehow re-arrange your data to get some decimals first.
* Use Power Query to bring in your data, it tends to be more awesome for this.
* There is such a thing as a "format file" for a CSV. I have never actually created one, but that would probably solve this.

Many thanks. The sort work around worked.

 

[PentaGalCXO]

Glad to hear scottsen's sort suggestion worked. For the benefit of others I'll chime in with my suggestion - Do the "format file" aka schema.ini method. I blogged about it here, and Prologika did perhaps a better job here. I deal with CSVs every day and strongly believe it is worth the bit of extra time to create the schema.ini and avoid the problems such as yours.

In full disclosure though, I am using Power Query more and more too. The ability to cleanse & transform data before it hits the data model is huge. It would be my first choice were it not for the incredibly painful shortcoming of Power Query connections going read-only & forcing complete recreation of tables & pivots.

 

[scottsen]

Somebody really needs to create a website to autogen the schema.ini for us...