Forum Discussion

aniketbhoi's avatar
aniketbhoi
New Contributor II
8 years ago
Solved

Strip white spaces out of Excel

I currently have a pipeline that pulls an excel file from a Box location,Parses the excel file and then writes /inserts that data into a table.
We encountered a case wherein the Pipeline failed with error-‘ORA-01400: cannot insert null into (string)’.
This was because someone had inadvertently inserted blank rows(perhaps with white spaces) into the excel sheet.so when the file was read it tried was read with null rows(apart from the not null rows) and then an attempt was made to insert the null rows into the table.

How do I get the Excelparser to ignore rows with blank spaces or rows with null data.

  • del's avatar
    del
    7 years ago

    I tackle this following a CSV parser with this router configuration using an arrow function and the jsonPath wildcard:

    Note, your jsonPath may vary slightly - like ($,"$.*") instead of ($.original,"$.*")

    I got this idea from a post on this forum (I think from @tstack, but not sure), however, I’ve searched for 15 minutes and cannot find the original post to reference in this post.

5 Replies

  • del's avatar
    del
    Contributor III
    7 years ago

    I tackle this following a CSV parser with this router configuration using an arrow function and the jsonPath wildcard:

    Note, your jsonPath may vary slightly - like ($,"$.*") instead of ($.original,"$.*")

    I got this idea from a post on this forum (I think from @tstack, but not sure), however, I’ve searched for 15 minutes and cannot find the original post to reference in this post.

  • robert_parks's avatar
    robert_parks
    New Contributor III
    7 years ago

    It could be that some of the cells in the rows following the lat row of populated data have values in it. This can happen if the data source is coming from human interaction – it sees the rows are populated even if the cells you are concerned with are populated.
    I would throw a filter to check if the Card Name != null after the parser.

    • nisars's avatar
      nisars
      New Contributor III
      7 years ago

      I also had the same issue, if the user empties the contents of the cells rather than deleting rows, snaplogic will consider the rows to have data. My pipeline is dynamic and it is not possible to specify the column name or guarantee it should always be empty.