Exporting survey tables from SQLite file

[dannigart]

Hi there,

I would like to view either csv. outputs or excel workbook outputs of the survey responses 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 the code provided by @JamesTedrick found here: https://github.com/tedrick/ReadS123DB will provide exactly what I need, however I am not sure how to make it run properly as I have no experience using Python.

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.

I found and used the response for issue: Error OUT_DIR does not exist" however this did not change anything.

Any help would be greatly appreciated!

Thank you, 

Danni

[DougBrowning]

You can use a free SQLLite browser like this one  https://sqlitebrowser.org/

Or even an online reader like this one https://sqliteviewer.app/

I am not sure if SQL management studio can do it?

Hope that helps

[dannigart]

Hi Doug, 

Thanks for your response. Do you know how to extract the data tables (similar to the format of how you can download them from the Survey123 website) from the methods you recommend? I am unable to, as the solutions online result in the SQLite file exported as a csv file, in which the data is still stored under the data column as one record for each survey submission. In contrast I need each survey table exported as separate csv files.

Also, an online reader will not work as I am hoping to be able to review all survey responses from my device while in the field and off the grid. 

Thank you, 

Danni

[DougBrowning]

You want to look at the SQL Lite on the tablet?  That is a pretty interesting workflow.  Do some searches on a SQL Lite viewer on a tablet is all I can think of.  I doubt anything can make a csv on a tablet though.

[dannigart]

I'll have the mobile device to complete the survey on survey123 field app, and then a laptop to copy the SQLite file. I'm just trying to extract the data tables from an SQLite file (on my laptop), however the DB Browser and SQLiteStudio are simply exporting the SQLite file as a csv which is not what I need. Do you know how to extract the individual survey tables from the SQLite file on a Windows laptop/PC? 

[DougBrowning]

Its been several years since I have done it but in the SQL Lite browser I had you could dig in and find the table you want and export just that table.  I think that is what you want?  Sorry you kinda lost me.  I would try a few more browsers to find that you need.

[dannigart]

Maybe the format of my SQLite file is different than to what you are familiar with. The SQLite file consists of one table, each row is information about each survey submitted however the actual responses associated with each survey question are stored into one cell in JSON format. From the picture below, the SQLite file contains a table called "Surveys" with 7 rows, each one a survey submission.  Exporting this table as a csv is not helpful because the actual answers to the survey questions are still hidden in JSON format within one cell (column labeled "data"). 

If this is not familiar to you, maybe I'll go back to my original option of using the python code provided by @JamesTedrick .  

[EvanR]

@dannigart, were you able to get this working? I used it successfully yesterday after finding it through this thread, and it created a set of CSVs just as I had hoped (for a complex survey with repeats).

If you download and unzip the whole repo, just copy your target sqlite database into that directory and create a subdirectory called "out" (which will fix the "Error OUT_DIR does not exist" problem). It should look like this (changes outlined in red):


The other thing you will need to do is to edit the script so that it knows the name of your target sqlite database (that's why it's creating that empty sqlite file). You can open readDb.py in any basic text editor or IDE (Notepad, Notepad++, VSCode, IDLE, PyCharm, etc.) to make this change. On line 11, the "IN_DB" variable is declared, which tells the script the name of your target sqlite database. Here's what that line will look like: 

IN_DB = r'./a084e65cd9403d813e8ec667ca329a63.sqlite'

All you need to do is replace 'a084e65cd9403d813e8ec667ca329a63.sqlite' with the name of your target sqlite database, save the change, and THEN run readDb.py (may need to close it first if not editing with an IDE). For the purposes of my example, you would change line 11 to:

IN_DB = r'./YOUR_S123_BACKUP_FROM_MOBILE_DEVICE.sqlite'