Skip to main content
Solved

Prevent the FeatureReader from trying to create a point geometry for Excel features

L

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?

Best answer by lbd-kufers

david_r wrote:

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. ✌️

View original
Did this help you find an answer to your question?

7 replies

david_r
Evangelist
  • david_rEvangelist
  • March 24, 2023

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

L
  • lbd-kufers
  • Author
  • Best Answer
  • March 24, 2023
david_r wrote:

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. ✌️

M
Forum|alt.badge.img+2

@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.

L
  • lbd-kufers
  • Author
  • March 27, 2023
markatsafe wrote:

@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?

M
Forum|alt.badge.img+2
lbd-kufers wrote:

@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.

L
  • lbd-kufers
  • Author
  • March 28, 2023
lbd-kufers wrote:

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

Nice, thanks @Mark Stoakes​ 

jovitaatsafe
Safer
Forum|alt.badge.img+11

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


Most helpful members this week

Community Stats

30,872
Posts
117,040
Replies
38,692
Members
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.