locked
DataFormat.Error after csv import RRS feed

  • Question

  • Hi

    First of all the data explorer is an awesome tool.

    The problem so far:

    After importing data from a csv file the various columns splits up and get automaticlly identfied by its type. When I then for instance try to change the order of a column tagged as “Number” I’ll get the error message

    DataFormat.Error: Could not convert to Number. Details: (564 Items)

     

    of a total of 564 entries.

    Two points are conspicuous in particular 

    First not every column tagged as “Number” leads to this error message so it’s not due to a corrupt csv file. (I also double checked the csv file with a hex editor for any suspicious without results.)

    Secondly it is possible to sum up this critical column with another number column. The new column is again automaticlly identified as “Number” and shows mathematically the correct result but leads again to the same error message.

    So what might have gone wrong?

    Thursday, May 23, 2013 9:52 PM

Answers

  • Thanks for the feedback Draagen, we'll take this into consideration for the planning of vNext. 

    Peter Q. http://blogs.msdn.com/peter_qian

    Monday, May 27, 2013 8:13 AM

All replies

  • Hi Draagen,

    I'm having some trouble understanding the repro steps

    First, you import a CSV file, then you split by delimiter, at this point you are looking at the resultant table.

    One of the columns is Number typed, you tried to reorder this column by dragging it? and then the error shows up?

    Can you show me the formula Data Explorer generated? You can get the full formula by going to Options, check "Advance Formula Editing", then go back to your query, click the sheet icon in the right side of the formula bar.

    Regards,

    PQ


    Peter Q. http://blogs.msdn.com/peter_qian

    Thursday, May 23, 2013 11:15 PM
  • Hi Peter

    Sorry for my ambiguous description.

    Meanwhile I found out what went wrong. But let me descripe…

     

    First, here are the formulars for each step to import the file up to the crucial point:

    Step 1: Source

    = Csv.Document(File.Contents("Path\File.csv"))

    Step 2: ChangedType

    = Table.TransformColumnTypes(Source,{{"Column1", type text}})

    Step 3: SplitColumnDelimiter

    = Table.SplitColumn(ChangedType,"Column1",Splitter.SplitTextByDelimiter("|"),12)

    Step 4: FirstRowAsHeader

    = Table.PromoteHeaders(SplitColumnDelimiter)

    Step 5: ChangedType1

    = Table.TransformColumnTypes(FirstRowAsHeader,{{"ID", type number}, {"Name", type text},…

    Step 6: SortedRows

    = Table.Sort(ChangedType1,{{"ID", Order.Descending}})

     

    To sort this number typed column I simply used the drop down button of this column.

    As soon as I confirm this command the error message is displayed.

     

    Here comes what happend

    As you can see in Step 5 the first column is transformed as type number. But the program which generates this csv source file writes in the last row something like ‘(565 Items)’ thus a text.

    The Data Explorer now converts the whole column as number typed but throws an error in the cell of the last row. This I can’t see in the Query Editor because ‘the data has been trunceated due to size limits.’  As well I can’t see this error in resulting table because this specific cell is simply empty.

    I only just become aware of this single error cell once I sort the table by other column in the Query Editor so that this row gets listed at first.

    By deleting this row everthing works as it should.


    What I missed here is a feedback of the Data Explorer that an error occurred and where it is!

     

    Best Regards

    Holger


    • Edited by Draagen Sunday, May 26, 2013 2:13 PM
    Sunday, May 26, 2013 1:41 PM
  • Thanks for the feedback Draagen, we'll take this into consideration for the planning of vNext. 

    Peter Q. http://blogs.msdn.com/peter_qian

    Monday, May 27, 2013 8:13 AM