Data recovery from SQLite file

270
3
12-13-2019 12:13 PM
CurtisOverholt
New Contributor II

Hi All,

I encountered an issue with Survey123 and thankfully been able to extract the SQLite file that contains the data.

The survey123 form sent blank information to the feature class and I am hoping to somehow extract this information and import it into the form feature class. I have searched and seen that you can convert to CSV using python with this file 

https://community.esri.com/external-link.jspa?url=https%3A%2F%2Fgithub.com%2Ftedrick%2FReadS123DB 

However, my python skills are poor and i'm not sure how to run this file.

I can open it up, and see the text but I'm not sure how to correctly set the paths. Does it matter where I store the file? I have the sqlite database just on my C:\Temp 

I can see at the top of the file it has these parameters where I believe you should enter your paths, but I'm just not good enough to understand this yet.

#Constants for easy running
IN_DB = r'./a084e65cd9403d813e8ec667ca329a63.sqlite'
OUT_DIR = r'./out'‍‍‍‍‍

If my path is C:\Temp 

Should this be the edited to this 

IN_DB = r'C:/Temp/a084e65cd9403d813e8ec667ca329a63.sqlite' and saved?‍

What about the OUT_DIR ? 

 

Does the file have to be placed somewhere specific ?

Lastly,

How do I import that data back to the form?

 James Tedrick‌ : 

0 Kudos
3 Replies
Jim-Moore
Esri Regular Contributor

Hi Curtis

Before heading down the Python route, I just thought I'd check if you've checked out the Recover data from a mobile device section in the Survey123 documentation?

Troubleshoot—Survey123 for ArcGIS | Documentation 

The steps described here can help you recover data from the SQLite database and then resubmit each record.

Best regards,

Jim

0 Kudos
CurtisOverholt
New Contributor II

Hi Jim,

I tried this workflow. 

Recover data from a mobile device

When I did the steps, however, when I went to hit 'fix database' there is no prompt for me to look up the SQLite file. I'm using Survey123 for ArcGIS (desktop) 3.6.153

I was looking the workflow below where it says to use SQL to update the path, but my path doesn't seem like it would be affected

PATH: /var/mobile/Containers/Data/Application/8A723410-E546-42CC-B7D4-90F97E082B0E/Documents/ArcGIS/My Surveys/2e80645d696a430e868903bfae471ef9/esriinfo/Survey123Name.xml

SQL Code Update

UPDATE Surveys SET path = REPLACE (path,"/sdcard", "C:/Users/Me") where path LIKE "/sdcard%"

 Any Ideas?

0 Kudos
Jim-Moore
Esri Regular Contributor

Hi Curtis

The Fix Database option operates on the database stored in the app's Databases folder, for example C:\Users\<Username>\ArcGIS\My Surveys\Databases (on Windows). Refer to the Storage settings section here: Quick reference—Survey123 for ArcGIS | Documentation.

The idea is to copy the database from your mobile device to the Databases folder on your computer then use the Fix Database option in the desktop field app. Open the survey to check that the survey records have been restored, then submit from there. As mentioned in the recovery steps, you must have the survey(s) downloaded to the field app before using Fix Database.

The second set of steps should only be necessary if the above does not work and refer to updating the paths stored in the SQLite database itself.

Cheers,

Jim

0 Kudos