Survey123 Pull Data with "0" as first character

854
4
Jump to solution
05-27-2019 07:46 PM
MarcoPoetsch
Occasional Contributor II

Hi all,

I have some troubles with pulling data from a CSV file.

When the lookup item starts with a "0" I can't find an matching entry.

If I delete the "0", then it would work....

I assume that's because of the nature of CSV files. Do you know if there is any workaround?

Cheers,

Marco

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
MarcoPoetsch
Occasional Contributor II

Thanks all.

Everything is sorted now. The solution is simple: Just don't open the CSV with Excel before you publish the survey.

As soon as you open the file in MS Excel all the leading '0' will get deleted... Odd..

Anyway, thanks again.

View solution in original post

0 Kudos
4 Replies
JamesTedrick
Esri Esteemed Contributor

Hi Marco,

A leading "0" should not cause an issue so long as the question where it is inputted is a text question.  If you are having an issue, could you provide a sample of the form and data?  Is it a ZIP/Postal Code or FIPS lookup?

0 Kudos
MarcoPoetsch
Occasional Contributor II

Thanks James,

It about water meter IDs;

That's the schema I use in Survey123:

That's the calculation for the other fields:

That's the pulldata source (CSV file):

As you can see item number two should start with a 0, however its not displayed in the CSV. Consequently, it's also not working for the survey app as it can't find a matching record.

Do you have any ideas if there is a workaround or a solution for that?

0 Kudos
JamesTedrick
Esri Esteemed Contributor

Hi Marco,

Thanks for the additional details.  Is the CSV coming from a database of some form?  If you open it in a text editor (before opening in any other program), are leading 0's present?  If they are, I would not touch the CSV with MS Excel unless you specify that the ID should be text when importing - it will attempt to auto convert the IDs to numbers.  If the 0's are not present, you may want to work with whatever is generating the CSV (alternatively, treat the ID as a number by using the number() function in pulldata on the form).

MarcoPoetsch
Occasional Contributor II

Thanks all.

Everything is sorted now. The solution is simple: Just don't open the CSV with Excel before you publish the survey.

As soon as you open the file in MS Excel all the leading '0' will get deleted... Odd..

Anyway, thanks again.

0 Kudos