Question

Geometry not created from x_coordinate and y_coordinate in XLSX when change file name in Reader

  • 8 February 2019
  • 4 replies
  • 1 view
tim_wood
Rank
Badge +7

I have a Workspace containing an Excel Reader. The Reader parameters specify two of the columns as x_coordinate and y_coordinate. After updating the Reader to point at a new version of the XLSX with 2 additional columns (after the x_coordinate and y_coordinate ones), the geometry is not created. If I inspect the output from the Reader, it says there's no geometry. The x_coordinate and y_coordinate columns have the same names, so why is the geometry not getting created?

Also, if I wildcard the file name in the Reader e.g. "data*.xslx", the geometry does not get created from the Reader, even though x_coordinate and y_coordinate are specified.

I want to be able to wildcard the file name in the Reader for regular updates e.g. "data*.xslx" so I don't have to update the Workspace for "data Feb 2019.xlsx", "data Mar 2019.xlsx", etc. Is this just the Reader, or do I need to do it with a VertexCreator rather than in the Reader for this scenario?

4 replies

david_r
Rank
Userlevel 4

The Excel reader is notoriously fussy about schema if you change the input filename. Even if the schema looks identical between the input files, the reader may still decide detect a tiny difference and be unable to properly populate some or all of the attributes.

The easiest workaround is to delete the reader and re-create it.

If you need a more robust solution you can configure the reader to use a dynamic schema, but of course that has it's downsides as well.

takashi
Rank
Userlevel 2
Badge +17

If you need to preserve the schema (including data types setting) of Excel reader feature type even if the source file has been changed, try setting Use Current Settings to the Additional File Settings parameter when adding the Excel reader to the workspace at first.

Note: The reader with this setting won't read additional columns which didn't exist in the first Excel file used to configure the reader schema.

tim_wood
Rank
Badge +7

Thanks @david_r and @takashi for your suggestions. I think I will stick with the VertexCreator using "replace with point" for now as it seems more reliable. The Workspace only takes minutes to run anyway.

To clarify about the additional columns: These were missing from the first XLS I received but the schema should be the same for future updates.

To clarify about multiple files: There should only be one input file to read each time the Workspace is run, but the file name will be different hence wildcarding part of it.

tim_wood
Rank
Badge +7

If you need to preserve the schema (including data types setting) of Excel reader feature type even if the source file has been changed, try setting Use Current Settings to the Additional File Settings parameter when adding the Excel reader to the workspace at first.

Note: The reader with this setting won't read additional columns which didn't exist in the first Excel file used to configure the reader schema.

I still get no geometry if I change that Additional File Settings value and connect the Reader to an Inspector. Maybe I'd have to remove the Reader and start again for it to work...

Reply


Community Stats

28,706
Posts
109,463
Replies
36,551
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.