Exporting survey tables from SQLite file

754
6
12-15-2022 06:58 AM
dannigart
New Contributor II

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.

dannigart_0-1671115665175.png

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

 

6 Replies
DougBrowning
MVP Esteemed Contributor

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

0 Kudos
dannigart
New Contributor II

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

0 Kudos
DougBrowning
MVP Esteemed Contributor

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.

0 Kudos
dannigart
New Contributor II

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? 

0 Kudos
DougBrowning
MVP Esteemed Contributor

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.

0 Kudos
dannigart
New Contributor II

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"). 

dannigart_0-1671127689052.png

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

0 Kudos