I’m on Clover ETL Designer Community v3.0.1. (It’s not my box or software, I just have to work with it. So, I’m not looking for suggestions that I upgrade.)
I’m using the XLSDataReader, CTL2 for the transformation and writing to a CSV. Currently, the Start Row and End Row properties are being used, but 1) I don’t want to have to update them for every new XLS file I want to load, and 2) these properties are deprecated according to the documentation.
The question is: how do I know when the XLSDataReader will consider itself at the end of the file, or is that something I’m supposed to recognize and “return STOP;” for?
Rephrased, what is to replace the Start Row and End Row properties? Usually, when documentation states that an attribute / method / etc is deprecated, the attribute / method / etc that you could or should use instead is stated. Unfortunately, the Clover documentation doesn’t follow that convention – at least not in this case.
I really appreciate any assistance you choose to provide.
I had just been reading about returning -1 (SKIP) and -2 (STOP) from the transform() function, so I was under the impression that Number of Skipped Records was possibly how many times I could skip before Clover stopped reading the input file or something. And there’s no indication in the documentation that this property is the number of records that will be skipped *from the beginning* so I wasn’t sure where it started skipping.
Making sure I get this…
Am I correct in thinking the following?
I need to set the Metadata Row to 1 (row 1 has the column names)
I need to set the Number of Skipped Records to 1 (to skip the Metadata Row / column names row)
Max Number of Records should be left blank (because I always want to process all rows with data, and I don’t want to play with this every time I run the graph)
My CTL2 code should return -2 (STOP) when $0.firstInboundDataColumnName is blank (or null or whatever)
Thank you very much for the time you spent reading and responding.
No, metadata row is skipped automatically. Leave this field blank if you do not want to skip also some data records.
Yes.
I do not know if I got you right. If your goal is to stop graph run with error if your file does not contain data records, then the answer is yes. But it is not mandatory - leaving it blank will cause successful graph run with 0 read records. If your goal is to stop the graph after the last read record then no, this is done automatically.
My process is a repeating process. I want to open Clover, click Run Graph and close Clover. If following the instructions on the link you provided will have to be done every time, I’m not interested. (If they’ve already been done, I haven’t been using Clover long enough to have figured that out.) If I can turn this into a command-line process, so I don’t even need to see the Clover UI, that would be great. (I’ll be looking into that next.)
2 and 3) Thanks.
This leads back to the original question I had in my head: How does Clover know it has reached the end of data on the spreadsheet? I know that, depending how you “delete” them, Excel sometimes considers many blank rows at the bottom of the sheet to still be part of the active area on the sheet, so I expect Clover would still pass them to me for processing. I guess the answer to this question doesn’t matter – I’ll include a check for data in key columns, and I’ll return -1 (SKIP) from transform() if those columns are blank. (I’m not wanting to raise an error.)
Thanks for your time, Imriskal. I appreciate the content and quality of your responses and the time you’ve spent composing them.
Next, metadata extraction. There is no way how to automatize it completely in Community Edition. It might be possible in commercial Designer but just under given set of circumstances, not generally. It depends mainly on the specifics of your task.
And regarding your question about end of data in Excel files, you are right in your assumption. XLSDataReader reads any lines which were modified somehow (i.e. also empty lines). So having some filter for empty lines is the right way.
