none
csv file errors on load - best practice RRS feed

  • Question

  • Hello, I’m importing a .csv file and the very last row is s summary row where only one column (the amount) has a value, all the other columns are empty so PQ sees this and displays an error hyperlink.  When I click on the error hyperlink it displays the error table and shows the row number of the row holding the error.  Great.  Now a few questions.

    1. In excel I can use the ctrl-down arrow to jump to the last row in a table but in a the PQ Query Editor how to jump to the last row?
    2. The Query Editor does not show you that there will be an error when you save & load the table so I use the Keep Rows > Keep Errors function to see if there are any errors prior to loading however, this error table does not show the row number. :(
    3.  What are the steps others use when importing a large csv file and there are errors? I want to have a sequence of steps that I can use over and over again. 

    Thanks.

    hd

    Sunday, October 2, 2016 12:15 PM

Answers

  • You can add an index column that will show the row-numbers before applying the "Keep-Errors-Step".

    Imke Feldmann TheBIccountant.com

    Sunday, October 2, 2016 7:20 PM
    Moderator
  • For #1, PQ uses virtualized previews of the first N rows of the data you're viewing. To jump to the end of the current chunk of preview rows, select a preview cell and press Ctrl+End. This will cause more preview rows to be loaded (if more exist). To get to the end of the dataset, you'll have to keep scrolling like this until the final page of results is loaded. (As you can probably tell, PQ isn't really designed for viewing all of your data in tabular form, but rather for doing operations based on small previews of the data.)

    For #2, see Imke's reply.

    For #3, you'll usually want to determine what's causing the errors and fix them upstream from where they're occurring. One common source of errors is data in a non-text column (such as a column of numbers) which can't be converted to the proper type. Sometimes this can occur because of headers, footers, or other non-row data occurring in the middle or at the end of a file. To resolve this, filter out the problematic row(s) before converting the columns to their non-text data types.

    Hope that helps.

    Ehren


    Wednesday, October 5, 2016 6:20 PM
    Owner

All replies

  • You can add an index column that will show the row-numbers before applying the "Keep-Errors-Step".

    Imke Feldmann TheBIccountant.com

    Sunday, October 2, 2016 7:20 PM
    Moderator
  • For #1, PQ uses virtualized previews of the first N rows of the data you're viewing. To jump to the end of the current chunk of preview rows, select a preview cell and press Ctrl+End. This will cause more preview rows to be loaded (if more exist). To get to the end of the dataset, you'll have to keep scrolling like this until the final page of results is loaded. (As you can probably tell, PQ isn't really designed for viewing all of your data in tabular form, but rather for doing operations based on small previews of the data.)

    For #2, see Imke's reply.

    For #3, you'll usually want to determine what's causing the errors and fix them upstream from where they're occurring. One common source of errors is data in a non-text column (such as a column of numbers) which can't be converted to the proper type. Sometimes this can occur because of headers, footers, or other non-row data occurring in the middle or at the end of a file. To resolve this, filter out the problematic row(s) before converting the columns to their non-text data types.

    Hope that helps.

    Ehren


    Wednesday, October 5, 2016 6:20 PM
    Owner