I have the same problem.
A quick solution is adding a Tester after the Reader to filter the null rows.
Hi @rohan and @oscard,
I haven't been able to repro the issue, what build of FME Desktop are you both using and would you be able to share the Excel spreadsheet in question?
Another suggestion which will stop the extra record being read in at all is the set up the Cell Range in the Reader parameters of the FeatureReader (e.g. A1:B103). There is a preview tab so you can ensure you have set this up correctly and no additional row is being read in.
Hi @rohan and @oscard,
I haven't been able to repro the issue, what build of FME Desktop are you both using and would you be able to share the Excel spreadsheet in question?
Another suggestion which will stop the extra record being read in at all is the set up the Cell Range in the Reader parameters of the FeatureReader (e.g. A1:B103). There is a preview tab so you can ensure you have set this up correctly and no additional row is being read in.
tafe-and-dual-sector-locations-2018.xlsx
xl-upload.fmw
I have attached the work bench and the xl file.
My build version is
2018.0.0.1 (20180328 - Build 18295 - WIN64)
Hi @rohan and @oscard,
I haven't been able to repro the issue, what build of FME Desktop are you both using and would you be able to share the Excel spreadsheet in question?
Another suggestion which will stop the extra record being read in at all is the set up the Cell Range in the Reader parameters of the FeatureReader (e.g. A1:B103). There is a preview tab so you can ensure you have set this up correctly and no additional row is being read in.
Hi!
In my case I can't share the Excel files, sorry.
I'm getting the null rows with FME 2017.1.1.1 (17652) of 32 bits. I haven't tested it with FME 2018, yet.
It's not a big deal for me. And I also think is more of an Excel issue than an FME issue.
Thanks!
tafe-and-dual-sector-locations-2018.xlsx
xl-upload.fmw
I have attached the work bench and the xl file.
My build version is
2018.0.0.1 (20180328 - Build 18295 - WIN64)
Hi @rohan,
I've had a look at your xlsx file and it looks there is a named range set up:
When I run the FeatureReader I get a warning in the translation log:
Excel Reader: Found named range '_xlnm__FilterDatabase' on sheet 'Sheet1'.
so my initial thoughts are that the transformer is picking up this as the range to be read in, and since it picks up the headers separately, this is why you are seeing an extra line. If you see this problem again I would use the suggestion I mentioned above, this time in the log after the warning you will see this:
Excel Reader: Sheet 'Sheet1' range: Start Row '2', Start Column 'A', End Row '102', End Column 'M'
Which shows the named range has been overwritten.
p.s. @oscard this may also be something for you to be aware of, but glad to hear it is not a big issue for you!
I have the same problem.
A quick solution is adding a Tester after the Reader to filter the null rows.
If you have touched any row or column (including formatting etc. ) then FME might think there is data in those cells and will read them. You can try selecting several rows at the end of your data and instead of just using "delete" key, use right-click Delete. right-click delete seems to remove rows rather than empty them
I too have this problem with various spreadsheets I process for users. I'm sure not all of these have named ranges, but some may have formatting etc. beyond the end of the actual data.
@hollyatsafe hard coding the range into the Reader may be fine in some cases, but if you are building a Workspace that will be run repeatedly on new versions of the spreadsheet that may have different numbers of rows, it's not an ideal solution.
I usually resort to the Tester route to deal with this issue, which can be very time consuming to setup if there are a lot of data columns.
The other option is to use the AttributeValidator with the "Has a value" Validation Rule. That's easier to setup because you can just select all fields to apply that rule to. EDIT: scrub that - the AttributeValidator fails the row if just ONE column is empty, not ALL columns. So Tester it is then...
However, an option to ignore empty rows in the Reader (regardless of any named ranges etc.) would be great so I've posted an Idea for this. Get voting!
https://knowledge.safe.com/idea/116258/excel-reader-option-to-ignore-empty-rows.html?
