S123 export to Excel to FME: Reordering sheet names

[LaurrenDuncan]

I am exporting the data from my Survey123 form to Excel for use in FME and I encounter an issue when one of my field team members export the same Excel workbook. 

So when I export the file the sheets are supplied in this order (which makes sense considering the way the form is designed):

- Vegetation_Quadrats_v3_0

- coord_1

- quad_2

- species_measure_3

- weed_measure_4

- nat_weed_measure_5

- ann_species_r_6

- upp_area_spec_7

- wa_species_8

But when one of my team members do the same from the S123 website they get it reordered and renamed like this:

- quad_0

- species_measure_1

- weed_measure_2

- nat_weed_measure_3

- ann_species_r_4

- upp_area_spec_5

- wa_species_6

- Vegetation_Quadrats_v3_7

- coord_8

In order to use FME on the Excel workbook I need the sheet names to be consistent and in the right order. Is there a reason what I export is different to my other team members? Like some setting they need to enable?

[JamesTedrick]

Hi Laurren,

Could you compare the order of the original feature service and stakeholder views?  They should be same and that is the order that the export would be based on.  That being said, are you seeing the column names have their position in the sheet appended to the name of the column (as your list indicates)?  That should not be occuring.

[LaurrenDuncan]

Hey James,

So I went into ArcGIS Online and I opened the original feature service and the stakeholder feature service (I assume that is what you meant?) and then I looked at the list of layers and tables, they looked like they were in the right order. Vegetation_Quadrats_v3 and coord are layers, while the rest are listed as tables: quad, species_measure, weed_measure, nat_weed_measure, ann_species_r, upp_area_spec and wa_species.

However, when I exported each of the them the sheet naming and order were inconsistent between each feature service exactly like my post above. The column names for each sheet are in the correct position based on which table they were being generated from, so all the columns for quad are the same regardless of if it is quad_2 or quad_0. I attached each one below, ignore the v4 instead of v3 I have just made changes to the form since I submitted this post.

Generally all I have to do is just rename the sheets exported from the stakeholder version to the correct naming that is on the original feature service and everything works fine on FME, but I just wanted to see if it was something I did by accident when I created the form.

Cheers,

Laurren

[JamesTedrick]

Hi Laurren,

Thanks for the details - it sounds like you may want to file a support issue documenting this inconsistency with the ArcGIS Online export tool.

[LaurrenDuncan]

Cool thanks!

[Anonymous User]

Hi Laurren,

Have you tried using the wildcard feature in FME?

In the Excel Reader you can set the Merge Feature Type and use a filter with the Filter Type to Wildcard.

This way the FME Workbench would always be looking for the sheet that starts with quad_, the number should not matter unless stated.

Regards,

Julius

[LaurrenDuncan]

Awesome thanks!

[RyanUthoff]

As an alternative to exporting to Excel, you might be able to use the FME AGOL Feature Service Reader to read your feature service from AGOL.

This would eliminate having to manually export to Excel and relying on field names to be in the correct order.

Esri ArcGIS Online (AGOL) Feature Service Reader/Writer 

[LaurrenDuncan]

Cool thanks!