We have Excel files from a system (Kronos, if you are curious) that are "formatted for human eyes." I already figured out how to ignore the top 15 lines of the Excel file (where the headers are) to get to the real data. The end of the data has a row that has totals in it. THe first row of this section has the following in column B - "Pay Code Summary for All Employees". The line above that is blank. So that represents the end of data in the file. How can I get Clover to recognize that this is where the data ends? The amount of data rows is variable, so I can't say "only pull out 100 rows of data"…which is what I'm doing for testing purposes until I can figure out how to recognize the "end of data" in the file.
Thanks,
Brian
Thanks,
Brian
-
Hi Brian,
I am affraid that in this particular situation, reader has to read everything, the whole Excel file. However, you can filter the redundant records out. I would use Reformat checking whether a field in a record is null or not. If the field is null, you know it is not necessary to keep the rest of the records. So you can route them to the second output port.
employees.csv
I hope you will find this solution useful.
Regards, -
So I see what you did and mimicked it. HOwever, I realized that the fields after the blank row contain data in a different format than what is defined (one column is numeric but the "footer" in the spreadsheet has a string in it). Here is the error I get, which is obvious that is the issue: WARN [WatchDog_0] - Can't flush/rewind DataRecordTape.
INFO [WatchDog_0] - Execution of phase [0] finished with error - elapsed time(sec): 0
ERROR [WatchDog_0] - !!! Phase finished with error - stopping graph run !!!
INFO [WatchDog_0] - -----------------------** Summary of Phases execution **---------------------
INFO [WatchDog_0] - Phase# Finished Status RunTime(sec) MemoryAllocation(KB)
INFO [WatchDog_0] - 0 ERROR 0 12460
INFO [WatchDog_0] - ------------------------------** End of Summary **---------------------------
INFO [WatchDog_0] - WatchDog thread finished - total execution time: 0 (sec)
INFO [main] - Freeing graph resources.
ERROR [main] -
------------------------------------------------------------------------------------ Error details --------------------------------------------------------------------------------------
Component [Kronos Export:KRONOS_EXPORT] finished with status ERROR. (Out0: 85 recs)
Incompatible data types, xls type 'Label' cannot be used to populate a clover 'numeric' data field. in record 101, field 8 ("Money"), metadata "Input_Kronos_Metadata"; value: 'Money'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ERROR [main] - Execution of graph failed !
So, how can I get it to just stop completely. Clover wants to read the additional rows after the null and I just want it to stop reading. Here is the transform as I have it setup, mimicking what you had. I have output 1 go to the trash.//#CTL2
boolean continue_reading = true;
// Transforms input record into output record.
function integer transform() {
if (isnull($in.0.EEID)) {
continue_reading = false;
}
if (continue_reading) {
$out.0.EEID = $in.0.EEID;
$out.0.Name = $in.0.Name;
$out.0.Pay_Code = $in.0.Pay_Code;
string[] accountconvert = split($in.0.Account,"\\/");
$out.0.Assignment = accountconvert[5];
string[] hoursconvert = split($in.0.Hours,":");
decimal hours = str2decimal(hoursconvert[0]);
decimal minutes = str2decimal(hoursconvert[1]);
minutes = minutes / 60;
$out.0.Hours = hours + minutes;
return 0;
} else {
$out.1.EEID = $in.0.EEID;
return 1;
}
} -
I can not think of any way you could stop the reading. However, you could set Data policy of the reader to lenient and ignore the wrong records this way.
http://doc.cloveretl.com/documentation/ ... olicy.html
Is this an acceptable solution for you?
Please sign in to leave a comment.
Comments 3