Skip to main content

So apparently, FME does look for fields that might be a good candidate for creating a point geometry in both Excel and CSV readers (see https://community.safe.com/s/question/0D54Q000080hfENSAY/excel-format-of-attribute).

This means that if in an Excel or CSV file, there is a row with column names such as X and Y, FME will automatically try to create a point with the contents of these cells.

 

In the top-rated answer of the above link, it is mentioned that for the CSV reader, this will only occur if the option "Scan for types" is activated. This option does seem to be missing for the Excel reader, though, or am I overlooking something?

 

I have the problem that I am using a FeatureReader to dynamically read from an Excel file (the specific file is only known at translation time, not before) and FME unwantedly tries to create point geometries for the features.

The sheet of the Excel file might contain X and Y columns, but these are usually empty and surely not any spatial data. Nevertheless, the FeatureReader floods the log with warnings about how he cannot create a point geometry from these allegedly invalid features:

imageThe option "Allow Reader Feature Type editing" is deactivated in my FME workbench:image 

Any ideas for a fix?

FME tries very hard to be intelligent about coordinates, perhaps sometimes too hard.

In the Excel reader parameters, make sure to change the column datatypes of type "x/y_coordinate" to something else, e.g. string or number.

image


FME tries very hard to be intelligent about coordinates, perhaps sometimes too hard.

In the Excel reader parameters, make sure to change the column datatypes of type "x/y_coordinate" to something else, e.g. string or number.

image

Hi @david_r​ ,

thanks for your reply once again.

For me, setting the Excel parameters like this is not possible since the Excel file is dynamically determined during the translation (stored in the attribute '_ca___windows_DET_path' of the feature entering the FeatureReader):

imageConsequently, FME does not know which parameters it can expect from the Excel file to read:

image 

However, I think it was some kind of weird bug that resulted in the unwanted creation of point geometries. I also had another FeatureReader in the workspace which basically did the same as the problematic one, and this other FeatureReader did not print the log messages of failed point geometry creations.

I did the following and now, the log messages/unwanted creation of point geometries disappeared:

 

  1. Duplicate the problematic FeatureReader transformer (via Ctrl + D).
  2. In the "Dataset" property of the ReatureReader, replace the attribute '_ca___windows_DET_path' with the selection of an actually existing file.
  3. Click on "Parameters".
  4. In the "Parameters" window, I selected the proper "Sheet to Read" and also changed the "Attribute Definition" from "Automatic" to "Manual". I assessed the exposed attributes and their names, and since no X/Y or similar attributes were recognized by FME, I simply switched the "Attribute Definition" back to "Automatic".
  5. I closed the "Parameters" window and in the "FeatureReader Parameters" window, I selected the "Dataset" to be '_ca___windows_DET_path' , again

Afterwards, I ran the workspace once again and poof, the log messages were gone. Hence, I conclude it was some kind of weird bug.

 

So thanks again for your reply which lead me to a solution. ✌️


@lbd-kufers​ I think we can probably add an option on the Excel reader parameters to switch of point geometry creation in a future version of FME.


@lbd-kufers​ I think we can probably add an option on the Excel reader parameters to switch of point geometry creation in a future version of FME.

@Mark Stoakes​ This would be a nice addition. Do you require this to be added as an FME idea?


@Mark Stoakes​ This would be a nice addition. Do you require this to be added as an FME idea?

@lbd-kufers​ No. I created a change request.


@Mark Stoakes​ This would be a nice addition. Do you require this to be added as an FME idea?

Nice, thanks @Mark Stoakes​ 


Hi @lbd-kufers​,

I'm happy to report that the change request that Mark had filed has been addressed for FME 2023.1 and is currently available for testing in our Betas. You can find our beta downloads here: engage.safe.com/support/downloads/beta

 

This change adds an option to Scan for Geometric Types similar to the CSV reader. It's checked by default and I believe in your case you would want to uncheck it so that you don't get point geometry. More information on it can be found at the bottom of this 2023.1 Excel Reader Parameters doc under Advanced.


Reply