AnsweredAssumed Answered

Commas in quoted CSV columns still separating columns

Question asked by cenobite on Nov 17, 2017
Latest reply on Nov 20, 2017 by cenobite

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.

Outcomes