Get data from CSV file reads numeric columns as text

[B_Real]

I'm doing:

Get Data -> Text/CSV 

Data Type Detection: Based on entire dataset

The csv file was created in R using the command:

write.csv(dataframe, file = "./outputs/financials.csv", row.names = F)

There are a bunch of columns in the csv file that contain only numbers, but Power BI is still reading them in as text. It looks like PBI is converting large values into scientific notation, e.g.:

600000 becomes 6e+05

And therefore treating the whole column as text. In fact it only seems to treat numbers that are  at the hundred thousand level, e.g. 100000 becomes 1e+05, 200000 becomes 2e+05, 300000 becomes 3e+05. All other values in the column are numbers.

A similar thing happens when I open the csv file in Excel, the 600000 becomes 65e+05 but only that cell is formatted as 'scientific' whereas the rest of the column is 'general'. 

What the deuce is going on?

Edit: I know I can just go into the query editor and change the column type, but this is not a practical solution when there are over 90 columns that need to be corrected, every time the csv file is opened.

[Greg_Deckler]

Hmm, interesting. I was not able to reproduce with a file like:

ID,Val1,Val2,Val3,Val4
1,1000000,1000000,1000000,1000000
2,6000000,1000000,1000000,1000000
3,600000000,1000000,1000000,1000000
4,1000000,1000000,1000000,1000000

You can select multiple columns and then Change Type. You can also use Advanced Editor, line highlighted below.

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\largeNumbers.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Val1", Int64.Type}, {"Val2", Int64.Type}, {"Val3", Int64.Type}, {"Val4", Int64.Type}})
in
    #"Changed Type"

If the column names are consistent or in the same position (do it before promoting headers) then you could just copy and insert the line of code as appropriate. What version are you on? I seem to remember this sort of thing happening to me in the past but I can't reproduce on the current version.

[B_Real]

Thanks Greg. I have a feeling the problem is in the csv file that's created by R but I can't see anything obvious. It's fairly big, c. 90k rows and 90 columns.

I have, for now, created a custom function in the query editor that transforms all offending text columns to decimal.  Not ideal, but at least I can now repeatedly update the csv file without incurring the wrong data types.

I'm on the following release:

December 2018

Product Version:
2.65.5313.841 (18.12) (x64)

OS Version:
Microsoft Windows NT 10.0.17134.0 (x64 en-US)