I'm trying to solve an issue for our analysts.
Every time we read in a file, there will inevitably be a couple of rows that don't make it through and they are currently getting deposited into a separate csv.
The issue is that the row that causes the DataReader_Error is not in the output, just a record id. So now the analysts have to go find the csv with the error output, collect all of the record ids that didn't make it through, locate the original spreadsheets, and then those specific rows in these massive spreadsheets.
My question is: is there a way to send the actual row to the csv so they don't have to hunt through all of these files and rows just to discover that it was appropriately discarded.
Please and thank you!
Every time we read in a file, there will inevitably be a couple of rows that don't make it through and they are currently getting deposited into a separate csv.
The issue is that the row that causes the DataReader_Error is not in the output, just a record id. So now the analysts have to go find the csv with the error output, collect all of the record ids that didn't make it through, locate the original spreadsheets, and then those specific rows in these massive spreadsheets.
My question is: is there a way to send the actual row to the csv so they don't have to hunt through all of these files and rows just to discover that it was appropriately discarded.
Please and thank you!
-
If I understand correctly, you do not have this output from FlatFileReader/UniversalDataReader? Or you're talking about very different component here?
FFR_error.PNG -
Interesting, is that only for FlatFile and Universal because most of my graphs use the SpreadsheetDataReader and it gives me: <Record fieldDelimiter="|" name="SpreadsheetDataReader_Error" recordDelimiter="\n" type="delimited">
<Field name="recordID" trim="true" type="integer"/>
<Field name="file" type="string"/>
<Field name="sheet" type="string"/>
<Field name="fieldIndex" trim="true" type="integer"/>
<Field name="fieldName" type="string"/>
<Field name="cellCoords" type="string"/>
<Field name="cellValue" type="string"/>
<Field name="cellType" type="string"/>
<Field name="cellFormat" type="string"/>
<Field name="message" type="string"/>
</Record>
The cell value exists but it's not always easy to tell what actually happened with that row. -
Hello Jcatoe,
there are a couple of different options that you can choose in this situation. However, they can be summed up in these 2 general approaches basically:- Converting the XLS(X) file into a CSV file and then reading the CSV file using the FlatFileReader component (as demonstrated by the_goat in the screenshot).
- Reading the XLS(X) file as if all the fields were string-type fields and then using the CloverETL functions and features to validate and convert those fields into their respective types if possible.
Please note that these options would make sense only if the errors you are dealing with are purely conversional (for example, when trying to read a string-type field as integer). At this point, I believe it would be most beneficial for an efficient and effective resolution of this case if you either attached an example graph that tries to read an XLS(X) file or at least if you listed those various types of errors that you get when reading the XLS(X) file using the SpreadsheetDataReader component.
Kind regards,
Please sign in to leave a comment.
Comments 4