Survey123 Connect populates multiple excel tabs upon export

[Anonymous User]

Hello! I am having issues exporting my Survey123 data to Excel. I am using Connect to build my survey for reference. Upon export, my Excel file has multiple tabs instead of just one. My survey does include repeats. For further reference, repeats are included in the survey for when a survey taker sees the same type of wildlife sign again and needs to add an extra data point. Is there a way to condense the exported Excel sheet into a single tab? Or a different way to structure the survey in Connect so I don't have this issue? Thanks in advance!!

(Side not: I am also having issues with the data output in Survey123 showing text and note fields as a field type... how do I include a text field in a survey without it showing up in the field type of the data? I have tried putting 'null' in the 'bind::esri:fieldtype' but the field still shows up upon viewing the output data..)

[ClayDonaldsonSWCA]

Survey123 repeats are tables that are related to the parent data (anything outside the repeat). If you need everything in a single table, you'll have to design your form without repeats. Or join the data in pro/desktop before exporting to excel.

On the text/note fields... If you published the survey and then updated the bind::esri:fieldtype to null, the fields are not populated - but it does not delete the fields for you. You will need to go into the feature service and delete the fields - or republish the forms.

[Anonymous User]

Thanks Clay! I will join the repeat data through Access I think. 

On your second point, so if I just republish the survey, these fields should be gone from the data and not the survey? Because I have republished with the 'null' value and the fields are still present. If there is no way to remove these from the output data, I can most definitely just select and delete fields from the feature service... just looking for a simplified way to achieve this.  

[LauraGreenODFW]

Were you able to successfully join your repeat data through Access?  I have a similar scenario for one of my surveys.  I tried to make it user friendly on the surveyor side, but it made the data hard for me to digest on data receiver side.  Would love to hear any tips on joining the repeat data prior to exporting as an excel form. Thanks!

[VickiMcKay]

Hi Clay,

I am having the same issues and I didn't find your answer clear. I also have repeat groups within my form, often for images related to a section. I don't necessarily need all the data in a single tab, but I do need to be able to identify which station the data in each tab belongs to. Right now there is an ObjectID on each tab of the spreadsheet, but these appear to be unique numbers that don't clearly link the data on one tab to matching data on another tab. How do I link all the tabs to a Site Code that is entered in the very first section of the form? Do I need to do anything on the form itself, like repeat the Station Code in every section? Is there a way to add the Station Code to each section based on the Station Code entered in the first section (i.e. not have the user re-enter the information)? I have three forms like this. Thanks for your help.

[ClayDonaldsonSWCA]

Survey123 builds relationships based on `globalid` to `parentglobalid` fields. ObjectID is not part of the relationship class.

You can join in arcgis pro based on those fields, or use something like xlookup()in excel. Here is an example:

1. Sheet_1 ("Stations") contains a list of stations with a globalid.
2. Sheet_2 ("Survey Results") contains survey results with a parentglobalid, which refers to the globalid in the "Stations" sheet.

Sheet 1 ("Stations")

Station Name globalid

Station A	123abc
Station B	456def
Station C	789ghi

Sheet 2 ("Survey Results")

Survey Date parentglobalid Result

2024-01-01	123abc	Pass
2024-01-02	456def	Fail
2024-01-03	789ghi	Pass
2024-01-04	123abc	Pass

You want to lookup the corresponding Station Name in Sheet 1 for each survey in Sheet 2 using the relationship between parentglobalid (in "Survey Results") and globalid (in "Stations").

In Sheet 2, to lookup the Station Name for each survey based on the parentglobalid, you can use the following formula in a new column (e.g., in cell D2 of "Survey Results"):

=XLOOKUP(B2, 'Stations'!B:B, 'Stations'!A:A, "Not Found")

Explanation:

• B2 The parentglobalid value in "Survey Results" (Sheet 2).
• 'Stations'!B:B: The column in "Stations" (Sheet 1) that contains the globalid.
• 'Stations'!A:A: The column in "Stations" (Sheet 1) where we want to return the Station Name.
• "Not Found": Optional message if a matching globalid is not found.

Survey Date parentglobalid Result Station Name

2024-01-01	123abc	Pass	Station A
2024-01-02	456def	Fail	Station B
2024-01-03	789ghi	Pass	Station C
2024-01-04	123abc	Pass	Station A