Survey123 CSV Pulldata Works Inconsistently

[RobertAnderson3]

I have been working on a Survey for our Utilities department, I have set up several CSVs that are hosted in ArcGIS Online to give them information based on the Asset ID they put in, it's separate CSVs for the different asset types they use just to keep the lists a little more organized.

For several of the CSVs this works seamlessly (as far as I can tell) with the pulldata expression filling in the address values no problem. However in one of the other CSVs this only works with some IDs, I can't identify why, the CSVs follow the same format. The fact it loads sometimes tells me the set up is still accurate, it just doesn't find the Asset IDs in the latter half of the CSV for some reason?

Has anyone experienced anything similar recently?

[DougBrowning · Accepted solution]

Usually a sneaky bad char.  Esp newlines.  It will work up to that point in the file then no.  To find new lines I turn on word wrap and look for anything that is more than a single line.  Special chars can be hard to find.  I would look for one of the values that does not pull and go up from there. 

Good luck.

[RobertAnderson3]

Thanks Doug! I thought we had stripped out all the characters causing issues but it seems you are correct! It appears the one causing us issues is a quotation mark (") which I wasn't expecting. Removed that from the first file and then it all worked. (We may run into a problem with users typing it for measurements in inches but hey, we found it anyways).

The interesting thing I notice, if I have a CSV I'm also using for a choice list, the offending asset ID is just gone from the choice list, but any of the following IDs still show up and populate other pulldata expressions fine.

When it's solely used for a pulldata it just all breaks after the quotation mark.

EDIT: Also of note, I figured out why I didn't catch this as a problem prior, it seems when the CSV is packaged with the Survey in it's media folder, quotes don't cause an issue, yet when it's a hosted CSV in AGOL it seems to cause the problem. Would there be a reason a CSV is handled differently from the media folder vs hosted? 

EDIT 2: I have noticed I was mistaken, the previous form did in fact have a different structure. It contained " " around any string that contained a " in it itself (and "" for the character to stay), our manual export vs our automated export from SQL Server changed this so we're working on a solution.

[survey123_solutions]

Would you be so kind to share the csv file so that we may see what worked for you?  Thank you so much! Appreciate it.