topic Re: Commas in quoted CSV columns still separating columns in ArcGIS Survey123 Questions

Commas in quoted CSV columns still separating columns

ZacharyStauber1 — Sat, 18 Nov 2017 00:00:34 GMT

I've made an external CSV file with a pulldata command in a calculation. Here is an example of the syntax in the calculation column:

pulldata('deficiency_list','citation','sdwis_deficiency_code',${G001_S001_DEF})

And here are the top three lines of my deficiency_list.csv file, including the header:

sdwis_deficiency_code,system_type,element,condition,concern_description,citation

S001,Surface,Treatment,"4 in 1 valve pump supports a critical process (fluoride, coagulant feed, chlorine)",Critical processes require a reliable pump to deliver continuous and uninterrupted additive feeds. 4 in 1 valve pumps are inadequate for critical processes.,40 CFR 141.723(b)

G001,Ground,Management/Operations,Additives or Materials directly added to water during production or treatment do not conform to ANSI/NSF Standard 60,Materials which are not approved for use in drinking water may fail prematurely or introduce contaminates into the drinking water supply.,20.7.10.400.K NMAC

As you can see, Excel exported the CSV file properly, meaning that if a cell had commas in it, it was double quoted so that commas won't break up a single column into multiple ones. However, Survey123 Connect doesn't seem to respect the double quotes.

In this case, if the value of a previous column, G001_S001_DEF is S001, it should pull in the value from the CSV file's 'citation' column (the last one) which would be 40 CFR 141.723(b) in this case. However, what it pulls in is chlorine). It counts in five commas from the left and returns that value, even though two of those commas are in the double quotes and supposed to be within a single column. If G001_S001_DEF is G001, it returns the correct column value, or 20.7.10.400.K NMAC, because there are no commas in any other column up to that point.

I think this is just a bug with Survey123's CSV parser, but before I file a ticket, I'd like to see if there's a workaround. I've tried using single quotes instead of double, no luck, tried backslashing the column internal commas, no luck. I really need to do this, and I can maybe use the choices tab in Excel to store the tabular data, but it will be harder to maintain over time.

Re: Commas in quoted CSV columns still separating columns

DanPatterson_Retired — Sat, 18 Nov 2017 00:10:20 GMT

in the interim use ... | ... the old vertical bar. most people don't know where to find it on the keyboard and it least if you can translate it back to commas, your workflow won't be slowed down

Re: Commas in quoted CSV columns still separating columns

JamesTedrick — Mon, 20 Nov 2017 20:27:34 GMT

Hi Zachary,

As you mentioned in the end, this is a known issue with Survey123's CSV parser.

Re: Commas in quoted CSV columns still separating columns

ZacharyStauber1 — Mon, 20 Nov 2017 20:58:55 GMT

@Dan I tried the vertical bar when re-exporting the CSV file, and it didn't seem able to read it at all. Although XLSForms doesn't allow you to specify a delimiter, I was hoping maybe it would take its cue from the initial column heading line.

@James Thanks for the official word. Is there an issue page I can subscribe to for this? I'd hate to file a second issue if it's already in the queue to fix.

Re: Commas in quoted CSV columns still separating columns

JamesTedrick — Mon, 20 Nov 2017 21:08:43 GMT

We don't have na official tech support BUG number for this issue yet; please submit this via Esri Tech Support if you want to be able to track the issue.

Re: Commas in quoted CSV columns still separating columns

ZacharyStauber1 — Mon, 20 Nov 2017 21:20:36 GMT

Will do, as soon as I stop getting the message "The Esri Support site is currently being impacted by technical issues that may restrict access to Bug Page on Support Site. We are working to address this issue and apologize for any inconvenience." on that page. Until then, y'all have a Happy Thanksgiving!