Data recovery from SQLite file

[CurtisOverholt]

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‌ : 

[Jim-Moore]

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

[CurtisOverholt]

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?

[Jim-Moore]

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

[TSya]

Hi Jim Moore,

I have copy the database from the mobile device to the Databases folder (which is in sqlite file) on my computer then use the Fix Database option in the desktop field app.  The survey(s) also has been downloaded to the field app before using Fix Database. However, when I open the survey,  to check that the survey records have been restored, there is no survey was restored.

How we can input the sqlite file in the survey to be resubmitted?

Any advise would appreciate.

Regards,

TS

[dannigart]

I have no experience with using Python and I also require csv. outputs from the SQLite file. I understand that I can resubmit surveys by copying the SQLite file into the database folder of the Survey123 Field App. However, I need to be able to review the survey submissions at the end of the day in table format without internet (similar format as if you were to export from the Survey123 website). I think this code will provide exactly what I need, however I am not sure how to make it run properly.

When double click the readDb file, it opens for a second and then creates the SQLite file seen below. However there is no output file(s) of the data.

Any help would be greatly appreciated!

Thank you, 

Danni