topic Re: How do I retrieve data from an orphaned survey? in ArcGIS Survey123 Questions

How do I retrieve data from an orphaned survey?

AndyZeigert — Tue, 14 Aug 2018 18:55:42 GMT

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?

Re: How do I retrieve data from an orphaned survey?

JamesTedrick — Tue, 14 Aug 2018 20:23:32 GMT

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 CSV files .

Re: How do I retrieve data from an orphaned survey?

AndyZeigert — Tue, 14 Aug 2018 20:31:44 GMT

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?

Re: How do I retrieve data from an orphaned survey?

JamesTedrick — Tue, 14 Aug 2018 21:02:22 GMT

Hi Andy,

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

Re: How do I retrieve data from an orphaned survey?

AndyZeigert — Sun, 12 Dec 2021 10:38:59 GMT

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?

Re: How do I retrieve data from an orphaned survey?

JamesTedrick — Sun, 12 Dec 2021 17:00:28 GMT

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

Re: How do I retrieve data from an orphaned survey?

AndyZeigert — Fri, 17 Aug 2018 21:59:05 GMT

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!

Re: How do I retrieve data from an orphaned survey?

JamesTedrick — Fri, 17 Aug 2018 22:22:30 GMT

Hi Andy,

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

Re: How do I retrieve data from an orphaned survey?

AndyZeigert — Mon, 27 Aug 2018 19:31:50 GMT

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!

Re: How do I retrieve data from an orphaned survey?

JamesTedrick — Tue, 28 Aug 2018 18:30:56 GMT

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.

Re: How do I retrieve data from an orphaned survey?

AndyZeigert — Sun, 12 Dec 2021 10:39:02 GMT

I'm using Python 3.6.1. I tried pulling the latest version of your script and it stopped on the following:

Traceback (most recent call last):
  File "readDb.py", line 132, in <module>
    writeCSV(surveys, outDir)
  File "readDb.py", line 98, in writeCSV
    with open(addfile, 'w') as outFile:
FileNotFoundError: [Errno 2] No such file or directory: './out/survey_Wetland_Arid_Veg_Tree_Repeat_adds.csv'‍‍‍‍‍‍‍‍‍‍‍‍

Do I need to create the ./out directory or file first?

If it matters, I'm running this script on a mac running macOS 10.13.6.

Update: Actually, I just created the out directory and the script processed just fine. Looking over the CSVs now.