locked
Loading csv file with extension microsoft excel comma separated values file RRS feed

  • Question

  • I am trying to load csv files with an extension microsoft "excel comma separated values file.

    all the content in the files appear as error except the text that is header and footnote. the report is system generated

    When I click on the error I get the following message

    DataFormat.Error: There were more columns in the result than expected.
    Details:
        Count=11

    • Edited by H 2 J Monday, December 16, 2019 2:42 PM
    Monday, December 16, 2019 2:41 PM

Answers

  • H 2 J,

    You didn't give us much to go on here.  See Aleksei Zhigulin's reply.

    If I had to proffer a guess as to what's going on, I would say when you load csv files from a folder, PQ auto-generates a custom function.  By default, that function is based off of the first file's first 200 records.  In this case it must have had 11 columns in it.

    You are not trying to import a file that has more than 11 columns and the custom function doesn't know what to do with it.

    This may or may not be the issues, but given the amount of information you provided it's my best guess.

    If you can post a few import files and your BI File or XL Workbook, it would be easier to help.

    Hope this helps.


    John Thomas

    Thursday, January 2, 2020 9:01 PM
  • H 2 J,

    Just had another thought.  If someone put a "," in one of the description fields in the system, when the file is exported it could cause the column count to be off.  Depends on how the CSV file is formatted.  The text columns should be qualified with "" so this sort of thing doesn't happen.  

    Anyway... see what you find.


    John Thomas

    Wednesday, January 15, 2020 5:15 PM

All replies

  • Hi,

    Please, provide your code and data sample.

    Monday, December 16, 2019 4:01 PM
  • H 2 J,

    You didn't give us much to go on here.  See Aleksei Zhigulin's reply.

    If I had to proffer a guess as to what's going on, I would say when you load csv files from a folder, PQ auto-generates a custom function.  By default, that function is based off of the first file's first 200 records.  In this case it must have had 11 columns in it.

    You are not trying to import a file that has more than 11 columns and the custom function doesn't know what to do with it.

    This may or may not be the issues, but given the amount of information you provided it's my best guess.

    If you can post a few import files and your BI File or XL Workbook, it would be easier to help.

    Hope this helps.


    John Thomas

    Thursday, January 2, 2020 9:01 PM
  • H 2 J,

    Just had another thought.  If someone put a "," in one of the description fields in the system, when the file is exported it could cause the column count to be off.  Depends on how the CSV file is formatted.  The text columns should be qualified with "" so this sort of thing doesn't happen.  

    Anyway... see what you find.


    John Thomas

    Wednesday, January 15, 2020 5:15 PM