topic Re: Survey123 data - commas for blank options? in ArcGIS Survey123 Questions

Survey123 data - commas for blank options?

SarahTruebe — Tue, 05 Oct 2021 00:02:09 GMT

In my Survey123 survey, I have some questions that are multiple/many choice, where users can select Option A, B, C, D, and/or E, for instance. I am frustrated because when I download the Excel file, it will give me answers to that question that look like this:

A, C, E

A, B, C, E

Obviously, when I want to put this into columns in excel, I have to manually add blank cells between each answer. It would be much easier if Survey123 Excel output did this:

A,,C,,E

A,,B,C,,E

If it would automatically do this for me, in Excel I could have columns for each answer to the question. Is there a setting in Survey123 Connect that I am missing, perhaps, to get the output data to do this?

Re: Survey123 data - commas for blank options?

DeonLengton — Tue, 05 Oct 2021 06:47:34 GMT

Hi Sarah

Survey123 creates the comma separated list according to the sequence that the user selects the options. I think for some people that would be the expected behaviour, for others it might be less so.

Regardless, there are two options to get the list sorted. The first option is to use a JavaScript function in your survey to sort the list alphabetically. As far as I know this will require a logged in user so doing that with a public survey wont be an option.

Nonetheless, you can look at something like this to sort the values in JavaScript then just save the output to a new field in your survey:

str.split(",").sort().join(",")

The other option is to use the functions in Excel to achieve this. There is an Excel blog which details this exact scenario:

https://exceljet.net/formula/sort-comma-separated-values

Basically you can use the following formula to convert a cells comma separated values to be alphabetical:

=TEXTJOIN(",";1;SORT(FILTERXML("<x><y>"&SUBSTITUTE(B5;",";"</y><y>")&"</y></x>";"//y")))

- which assumes your data is in cell B5

Re: Survey123 data - commas for blank options?

SarahTruebe — Tue, 05 Oct 2021 16:31:01 GMT

Hello, I think maybe my issue wasn't understood. My problem is I'm NOT getting commas for the empty options. I would like to get commas inserted in the downloaded file for the empty options (which could be all the options, or just one, or none). Then I can use text-to-columns and not have to manually add empty cells and turn each answer into a Y/N.

Here's the specific question:

1. People: do you see evidence of:

toilet paper/human waste
trash
road
trail
unnatural patches of bare soil
campsite

I want to be able to easily take the Excel output from S123 and make it look like this:

Location	toilet paper	trash	road	trail	bare soil	campsite
1	Y		Y		Y
2	Y	Y	Y			Y

But the way it comes out of the export doesn't allow me to easily use text-to-columns to convert to this format because there are not commas for the empty values. Only the checked values generate text and a comma...e.g.,

toilet paper,road,bare soil

toilet paper,trash,road,campsite

which would give me:

Location
1	toilet paper	road	bare soil
2	toilet paper	trash	road	campsite

which I then have to manually add cells so it's all sorted by each issue.

Thanks for your help!

Re: Survey123 data - commas for blank options?

DeonLengton — Wed, 06 Oct 2021 07:10:31 GMT

Hi Sarah

Thanks for explaining your scenario so well. I do however think you will be better off doing most of the heavy work in Excel.

You can achieve your requirement with the SEARCH function of Excel:

=IFERROR(IF(SEARCH(F$4;$E$5);"Y";"N");"")

I hope this helps.

I have attached my Excel example for your convenience.

Re: Survey123 data - commas for blank options?

SarahTruebe — Tue, 12 Oct 2021 16:11:06 GMT

Thank you! This should work great for all questions that we have like this. Thanks for your help!