Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • Data
  • Working with geochemical data - converting excel files to gdb feature classes. Zinc (Zn...) columns are automatically recognised as a z-coordinate and the data doesn't plot!

Hi,

 

 

I'm attempting to convert hundreds of excel files to geodatabase feature classes in a dynamic workflow.

 

 

What I've done:

 

In the generate workspace dialogue,

 

Reader - Format: Microsoft Excel, Dataset: my excel files (testing with 5 for now but will have hundreds in reality), Coord. System: UTM83-20. In the parameters, it correctly selects the Easting column as x-coord and Northing as y-coord but incorrectly selects the column "Zn_4A_ICPMS_ppm" as z-coord. I then change the type to "Number" for the first file (only file that is listed in the "Sheets to Read")

 

 

Writer - Format: Esri Geodatabase (File Geodb Open API), Dataset: my gdb

...with Dynamic Schema selected in workflow options, press ok.

 

 

In the writer parameters, I changed the Geometry to gdb_point and also the Schema Definition Name to fme_feature_type

When I run it, the log says that the translation was successful and all 5 feature classes show up in my gdb but only the first plots spatially.

 

 

In every excel workbook there's 1 sheet and in each sheet the column for zinc is automatically being recognized as the z-coordinate (except for the first file, which I manually changed to number). The zinc columns are all different: Zn_4A_ICPMS_ppm, Zn_XRF_ppm, Zn_ICPMS_ppm, Zn_AQR_ICPMS_ppm, Zn_4A_ICPOES_ppm

Does anyone have any suggestions to work around this? Is there a way to somehow set what columns will be recognized as the x/y coordinates if they have the same name (and maybe set that there is no z-coordinate)?

 

 

Thanks!

Hi @badams,

That changed setting from the first sheet probably isn't applying because the Zn_.. column name isn't the same for all of your input files. Perhaps try creating the points within the workspace instead of relying on the reader for that. You could try adding a VertexCreator between your reader and writer to create a point from the Easting and Northing values and leave the Z value blank. You'll probably get the best results by setting the mode to 'Replace with Point'.

schema is different.

You can read dynamicaly.

Change the zinc column to the z value after extracting it with a coordinate extractor. Remove z from points by using 2dForcer.

Hi @badams,

That changed setting from the first sheet probably isn't applying because the Zn_.. column name isn't the same for all of your input files. Perhaps try creating the points within the workspace instead of relying on the reader for that. You could try adding a VertexCreator between your reader and writer to create a point from the Easting and Northing values and leave the Z value blank. You'll probably get the best results by setting the mode to 'Replace with Point'.

Thanks so much! @LauraAtSafe

schema is different.

You can read dynamicaly.

Change the zinc column to the z value after extracting it with a coordinate extractor. Remove z from points by using 2dForcer.

Thanks for the input @gio I used LauraAtSafe's idea and it worked perfectly!

 

 

Reply


Community Stats

30,102
Posts
114,260
Replies
37,733
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.