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.
1 ACCEPTED SOLUTION
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.
To filter out empty rows you can use a filter snap with “$ != {}”.
