Pulldata .csv Leading Zeroes Ignored

[GaryBowles1]

We are using the Pulldata function with barcode labels for inspection of assets. Our Asset IDs are 5 digit numbers with some that begin with a 0 ie. 02243. Excel ignores these zeroes when reading csv files. I have tried several different methods to create and save the .csv file, but the leading zeroes are not read in Survey123. we require the Asset ID data to be the 5 digit number including any leading zeroes so that we can link it back to our SCADA system. Has anyone else ran into this problem? Suggestions?

I have attached the survey, .csv file and a jpg of a barcode for testing.

Thanks,

--gary

[JamesTedrick · Accepted solution]

Hi Gary,

You can explicitly make a cell in Excel a string by using the =TEXT() function; this takes a format that allows leading zeros.  Then, you can copy and paste Special (Values) to set those as text.

[GaryBowles1]

Right, but when the csv is read by excel, aren’t the zeroes then ignored? I can open my csv in notepad and the zeroes are there, it is only in excel that the zeroes disappear.

I will try this and see if it will work.

Thanks for the quick reply,

--gary

Gary H. Bowles, GISP

GIS Database Administrator | Seneca Resources

Office : 412-548-2544 | Cell Phone: 412-334-5273

[JamesTedrick]

The TEXT function changes the nature of the data stored from being a number to being text, so characters are preserved; it's the formulaic equivalent of starting a text entry with a leading apostrophe (if you type '00123 into an Excel cell, the leading 0's will be retained; you'll also see the default formatting is left-justified (instead of right-justified for numbers) and a warning icon should appear asking if you want this to be a number.

[MichaelBrown4]

I just recently started seeing this problem in the Field App, but believe the Web Form to be OK.  I can't say if it's new or not, but my users just started reporting that the leading zeros are being removed from  select_one and select_multiple questions.  

I tried a few things like setting the TEXT field type and using the =TEXT() function, but when I test in connect, I can see the values are coming in as integers still, with the leading zero dropped.  See screenshot below.  Is there another way to do this?