Question

Excel FeatureReader reading empty row

  • 10 September 2018
  • 7 replies
  • 31 views
R
Badge

I have a simple workbench consisting of a FeatureReader, a VertexCreator and a FeatureWriter writing to a postGIS table. I am reading an Excel spreadsheet.

The problem is that the FeatureReader is reading an extra row beyond the data and passing null coordinates into the VertexCreator. I have 103 records, but for some reason it is reading 104. The columns in the last row are all marked as missing. I have checked to see if there is any spurious data, like white space in the last row that might be getting registered as data, but I can't find anything.

7 replies

oscard
Rank
Userlevel 2
Badge +19

I have the same problem.

 

 

A quick solution is adding a Tester after the Reader to filter the null rows.
H
Badge +2

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.

R
Badge

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)

oscard
Rank
Userlevel 2
Badge +19

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!

 

 

H
Badge +2

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:

 

0684Q00000ArMy2QAF.png

 

 

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!
M
Badge +2

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

 

 

tim_wood
Rank
Badge +7

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?

Reply


Community Stats

28,723
Posts
109,511
Replies
36,559
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.