How do I retrieve data from an orphaned survey?

[AndyZeigert]

What happened:

1. We've been developing several surveys on Survey123 and testing with beta users.

2. One of our users downloaded a "beta" survey to their device.

3. We then deleted that "beta" survey from arcgis online.

4. The user then (mistakenly) collected a bunch of data using the downloaded "beta" survey, which is now orphaned. "It says invalid URL when I try to sync."

Is there any way to retrieve that data from the device?

[JamesTedrick]

Hi Andy,

I might suggest trying to extract the data via GitHub - tedrick/ReadS123DB: Convert the survey data stored in the Survey123 Field App into a set of... .

[AndyZeigert]

That looks promising. What is the workflow for this like? The data is still on the device (iOS). How do I get it off of the device?

[JamesTedrick]

Hi Andy,

Instructions for copying the data off the device are at Troubleshoot—Survey123 for ArcGIS | ArcGIS 

[AndyZeigert]

Thanks, I was able to retrieve the .sqlite file. I attempted to run your python script to convert to .csv, but have run into another issue. It prints the first couple rows:

('Wetland_Arid', '"[{\\"attachments\\":[[{\\"editMode\\":0,\\"fieldName\\":\\"Photo_1\\",\\"fileName\\":\\"Photo_1-20180321-204554.jpg\\"},{\\"editMode\\":0,\\"fieldName\\":\\"Photo_2\\",\\"fileName\\":\\"Photo_2-20180321-204609.jpg\\"},{\\"editMode\\":0,\\"fieldName\\":\\"Photo_3\\",\\"fileName\\":\\"Photo_3-20180321-204620.jpg\\"},{\\"editMode\\":0,\\"fieldName\\":\\"Photo_4\\",\\"fileName\\":\\"Photo_4-20180321-204814.jpg\\"}]],\\"id\\":\\"%myform\\",\\"adds\\":[{\\"attributes\\":{\\"Veg_Tree_Repeat_count\\":\\"4\\",\\"Veg_Sapling_Repeat_count\\":\\"5\\",\\"Veg_Herb_Repeat_count\\":\\"10\\",\\"Veg_Woody_Repeat_count\\":\\"2\\",\\"Soil_Profile_Description_count\\":\\"8\\",\\"Sample_Date\\":1521615600000,\\"sample_type\\":\\"Sample_Point\\",\\"Sample_Point\\":\\"SP-5\\",\\"Investigators\\":\\"Other\\",\\"Other_Investigator\\":\\"R. Tobias\\",\\"Landform\\":\\"Depression\\",\\"Local_Relief\\":\\"Concave\\",\\"Slope_Class\\":\\"0-5\\",\\"Bare_Ground\\":\\"20\\",\\"Hydric_Soil_Ind\\":\\"Thick_Dark_Surface\\",\\"Restrictive_Layer\\":\\"No\\",\\"Hydric_Soil_Present\\":\\"Yes\\",\\"Remaks_soils\\":\\"Saturation at 6 inches\\",\\"Hydro_Indicator\\":\\"Surface_Water,High_Water_Table,Saturation,Water-Stained_Leaves\\",\\"Hydro_SW_Present\\":\\"Yes\\",\\"Hydro_WT_Present\\":\\"Yes\\",\\"Hydro_WT_Depth\\":6,\\"Hydro_Wet_Present\\":\\"Yes\\",\\"Hydro_Remarks\\":\\"Wetland B\\",\\">Veg_Tree_Repeat\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\",\\">Veg_Sapling_Repeat\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\",\\">Veg_Herb_Repeat\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\",\\">Veg_Woody_Repeat\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\",\\">Soil_Profile_Description\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"},\\"geometry\\":{\\"spatialReference\\":{\\"wkid\\":4326},\\"x\\":-117.60430254451438,\\"y\\":47.64294090447325,\\"z\\":730.9949951171875}}]},{\\"attachments\\":[null,null,null,null],\\"id\\":\\"$Veg_Tree_Repeat\\",\\"adds\\":[{\\"attributes\\":{\\"Tree_Stratum_Plot\\":\\"Shrub_15\\",\\"Tree_Stratum_1\\":null,\\"A_Per_Cover_Tree\\":null,\\"Dom_Species_Tree\\":\\"Yes\\",\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Tree_Stratum_Plot\\":null,\\"Tree_Stratum_1\\":null,\\"A_Per_Cover_Tree\\":null,\\"Dom_Species_Tree\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Tree_Stratum_Plot\\":null,\\"Tree_Stratum_1\\":null,\\"A_Per_Cover_Tree\\":null,\\"Dom_Species_Tree\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Tree_Stratum_Plot\\":null,\\"Tree_Stratum_1\\":null,\\"A_Per_Cover_Tree\\":null,\\"Dom_Species_Tree\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}}]},{\\"attachments\\":[null,null,null,null,null],\\"id\\":\\"$Veg_Sapling_Repeat\\",\\"adds\\":[{\\"attributes\\":{\\"Sap_Stratum_Plot\\":null,\\"Sap_Stratum\\":null,\\"A_Per_Cover_Sap\\":\\"70\\",\\"Dom_Species_Sap\\":\\"Yes\\",\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Sap_Stratum_Plot\\":\\"Shrub_15\\",\\"Sap_Stratum\\":\\"Black_cottonwood\\",\\"A_Per_Cover_Sap\\":null,\\"Dom_Species_Sap\\":\\"Yes\\",\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Sap_Stratum_Plot\\":null,\\"Sap_Stratum\\":null,\\"A_Per_Cover_Sap\\":null,\\"Dom_Species_Sap\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Sap_Stratum_Plot\\":null,\\"Sap_Stratum\\":null,\\"A_Per_Cover_Sap\\":null,\\"Dom_Species_Sap\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Sap_Stratum_Plot\\":null,\\"Sap_Stratum\\":null,\\"A_Per_Cover_Sap\\":null,\\"Dom_Species_Sap\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}}]},{\\"attachments\\":[null,null,null,null,null,null,null,null,null,null],\\"id\\":\\"$Veg_Herb_Repeat\\",\\"adds\\":[{\\"attributes\\":{\\"Herb_Stratum_Plot\\":\\"Herb_5\\",\\"Herb_Stratum\\":\\"Reed_Canarygrass_\\",\\"A_Per_Cover_Herb\\":\\"50\\",\\"Dom_Species_Herb\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Herb_Stratum_Plot\\":\\"Herb_5\\",\\"Herb_Stratum\\":null,\\"A_Per_Cover_Herb\\":null,\\"Dom_Species_Herb\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Herb_Stratum_Plot\\":null,\\"Herb_Stratum\\":null,\\"A_Per_Cover_Herb\\":null,\\"Dom_Species_Herb\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Herb_Stratum_Plot\\":null,\\"Herb_Stratum\\":null,\\"A_Per_Cover_Herb\\":null,\\"Dom_Species_Herb\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Herb_Stratum_Plot\\":null,\\"Herb_Stratum\\":null,\\"A_Per_Cover_Herb\\":null,\\"Dom_Species_Herb\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Herb_Stratum_Plot\\":null,\\"Herb_Stratum\\":null,\\"A_Per_Cover_Herb\\":null,\\"Dom_Species_Herb\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Herb_Stratum_Plot\\":null,\\"Herb_Stratum\\":null,\\"A_Per_Cover_Herb\\":null,\\"Dom_Species_Herb\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Herb_Stratum_Plot\\":null,\\"Herb_Stratum\\":null,\\"A_Per_Cover_Herb\\":null,\\"Dom_Species_Herb\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Herb_Stratum_Plot\\":null,\\"Herb_Stratum\\":null,\\"A_Per_Cover_Herb\\":null,\\"Dom_Species_Herb\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Herb_Stratum_Plot\\":null,\\"Herb_Stratum\\":null,\\"A_Per_Cover_Herb\\":null,\\"Dom_Species_Herb\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}}]},{\\"attachments\\":[null,null],\\"id\\":\\"$Veg_Woody_Repeat\\",\\"adds\\":[{\\"attributes\\":{\\"Woody_Stratum_Plot\\":null,\\"Woody_Stratum\\":null,\\"A_Per_Cover_Woody\\":null,\\"Dom_Species_Woody\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Woody_Stratum_Plot\\":null,\\"Woody_Stratum\\":null,\\"A_Per_Cover_Woody\\":null,\\"Dom_Species_Woody\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}}]},{\\"attachments\\":[null,null,null,null,null,null,null,null],\\"id\\":\\"$Soil_Profile_Description\\",\\"adds\\":[{\\"attributes\\":{\\"Soil_Matrix_Depth\\":\\"6\\",\\"Soil_Matrix_Color\\":\\"10Y/R 2/1\\",\\"Soil_Matrix_Percent\\":\\"100\\",\\"Soil_Redox_Color\\":null,\\"Soil_Redox_Percent\\":null,\\"Redox_Type\\":null,\\"Redox_Loc\\":null,\\"Soil_Texture\\":\\"Coarse_Sand\\",\\"Soil_Remarks\\":\\"Silty fine to coarse sand, dark brown\\",\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Soil_Matrix_Depth\\":null,\\"Soil_Matrix_Color\\":null,\\"Soil_Matrix_Percent\\":null,\\"Soil_Redox_Color\\":null,\\"Soil_Redox_Percent\\":null,\\"Redox_Type\\":null,\\"Redox_Loc\\":null,\\"Soil_Texture\\":null,\\"Soil_Remarks\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Soil_Matrix_Depth\\":null,\\"Soil_Matrix_Color\\":null,\\"Soil_Matrix_Percent\\":null,\\"Soil_Redox_Color\\":null,\\"Soil_Redox_Percent\\":null,\\"Redox_Type\\":null,\\"Redox_Loc\\":null,\\"Soil_Texture\\":null,\\"Soil_Remarks\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Soil_Matrix_Depth\\":null,\\"Soil_Matrix_Color\\":null,\\"Soil_Matrix_Percent\\":null,\\"Soil_Redox_Color\\":null,\\"Soil_Redox_Percent\\":null,\\"Redox_Type\\":null,\\"Redox_Loc\\":null,\\"Soil_Texture\\":null,\\"Soil_Remarks\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Soil_Matrix_Depth\\":null,\\"Soil_Matrix_Color\\":null,\\"Soil_Matrix_Percent\\":null,\\"Soil_Redox_Color\\":null,\\"Soil_Redox_Percent\\":null,\\"Redox_Type\\":null,\\"Redox_Loc\\":null,\\"Soil_Texture\\":null,\\"Soil_Remarks\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Soil_Matrix_Depth\\":null,\\"Soil_Matrix_Color\\":null,\\"Soil_Matrix_Percent\\":null,\\"Soil_Redox_Color\\":null,\\"Soil_Redox_Percent\\":null,\\"Redox_Type\\":null,\\"Redox_Loc\\":null,\\"Soil_Texture\\":null,\\"Soil_Remarks\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Soil_Matrix_Depth\\":null,\\"Soil_Matrix_Color\\":null,\\"Soil_Matrix_Percent\\":null,\\"Soil_Redox_Color\\":null,\\"Soil_Redox_Percent\\":null,\\"Redox_Type\\":null,\\"Redox_Loc\\":null,\\"Soil_Texture\\":null,\\"Soil_Remarks\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}},{\\"attributes\\":{\\"Soil_Matrix_Depth\\":null,\\"Soil_Matrix_Color\\":null,\\"Soil_Matrix_Percent\\":null,\\"Soil_Redox_Color\\":null,\\"Soil_Redox_Percent\\":null,\\"Redox_Type\\":null,\\"Redox_Loc\\":null,\\"Soil_Texture\\":null,\\"Soil_Remarks\\":null,\\"<myform\\":\\"{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}\\"}}]}]"', 3)
-----------------
{'attachments': [[{'editMode': 0, 'fieldName': 'Photo_1', 'fileName': 'Photo_1-20180321-204554.jpg'}, {'editMode': 0, 'fieldName': 'Photo_2', 'fileName': 'Photo_2-20180321-204609.jpg'}, {'editMode': 0, 'fieldName': 'Photo_3', 'fileName': 'Photo_3-20180321-204620.jpg'}, {'editMode': 0, 'fieldName': 'Photo_4', 'fileName': 'Photo_4-20180321-204814.jpg'}]], 'id': '%myform', 'adds': [{'attributes': {'Veg_Tree_Repeat_count': '4', 'Veg_Sapling_Repeat_count': '5', 'Veg_Herb_Repeat_count': '10', 'Veg_Woody_Repeat_count': '2', 'Soil_Profile_Description_count': '8', 'Sample_Date': 1521615600000, 'sample_type': 'Sample_Point', 'Sample_Point': 'SP-5', 'Investigators': 'Other', 'Other_Investigator': 'R. Tobias', 'Landform': 'Depression', 'Local_Relief': 'Concave', 'Slope_Class': '0-5', 'Bare_Ground': '20', 'Hydric_Soil_Ind': 'Thick_Dark_Surface', 'Restrictive_Layer': 'No', 'Hydric_Soil_Present': 'Yes', 'Remaks_soils': 'Saturation at 6 inches', 'Hydro_Indicator': 'Surface_Water,High_Water_Table,Saturation,Water-Stained_Leaves', 'Hydro_SW_Present': 'Yes', 'Hydro_WT_Present': 'Yes', 'Hydro_WT_Depth': 6, 'Hydro_Wet_Present': 'Yes', 'Hydro_Remarks': 'Wetland B', '>Veg_Tree_Repeat': '{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}', '>Veg_Sapling_Repeat': '{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}', '>Veg_Herb_Repeat': '{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}', '>Veg_Woody_Repeat': '{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}', '>Soil_Profile_Description': '{ca4b964a-0bf4-4004-a4f0-180e1375c3bf}'}, 'geometry': {'spatialReference': {'wkid': 4326}, 'x': -117.60430254451438, 'y': 47.64294090447325, 'z': 730.9949951171875}}]}
('Wetland Determination Beta', None, 3)
-----------------
‍‍‍‍‍

But then returns this error:

Traceback (most recent call last):  File "readDb.py", line 102, in <module>    surveys = readS123db(inDB)  File "readDb.py", line 24, in readS123db    jTransaction = json.loads(json.loads(row[1]))[0]  File "/Users/zeigert/miniconda3/lib/python3.6/json/__init__.py", line 348, in loads    'not {!r}'.format(s.__class__.__name__))TypeError: the JSON object must be str, bytes or bytearray, not 'NoneType'‍

My python knowledge is meager at best. Any ideas?

[JamesTedrick]

I haven't seen that error before with this script.  It most likely indicates there is an empty row given the line the error occurred on.  One way to check this would be to open the sqlite database in a db browser.

In terms of a quick code fix, you can place a

try:

statement at line 24, and indent the rest until return surveys; then, have an

except:

    pass

statement immediately before the return surveys line

[AndyZeigert]

I never had luck with the python script. Perhaps it's because my survey had several repeats?

I was able to open the .sqlite file in DB Browser for SQLite and export as JSON, then convert to GeoJson, then import into the "new" survey feature service. The schemas were the same, so the import was pretty smooth. I had to manually reattach photos, but fortunately it was only 7 records so it wasn't too bad. It was messy, and I hope I never have to do it again. But thanks for your help!

[JamesTedrick]

Hi Andy,

The script did not support repeats until this afternoon - mind if I ask you do a quick retry to test?

[AndyZeigert]

Hi, James. Sorry, I just now got to this. I didn't have any luck with the updated script, unfortunately.

I'm attaching the .sqlite file here, if you are interested in attempting to debug. It should only have 6 records or so, but several repeats.

Dropbox - a084e65cd9403d813e8ec667ca329a63.sqlite 

Thanks again for your help!

[JamesTedrick]

Hi Andy,

I did some updates after I posted to handle a couple of issues that cropped up in debugging.  Are you using python 3 (i.e., the python that comes with Pro)?  If so, there were a few things not python3 compatible; I've cleaned that up now.