Skip to main content

Upload ArcGIS Survey123 data retrieved from a phone to ArcGIS Online - Part 2

Have you ever run into a situation where you couldn’t access your ArcGIS Survey123 data from your phone or tablet? Don’t panic—you’re not alone, and we’re here to guide you through it. In this blog post we will delve deep into the process of recovering ArcGIS Survey123 data from your phone. To make sure when it happens to you, you know how to recover it.

Introduction 

This blog is Part 2 of a 2-part series on The Ultimate Guide to Recovering ArcGIS Survey123 Data from Your Phone. This second part will cover how to upload the ArcGIS Survey123 data retrieved from a device to ArcGIS Online. If you do not yet have the ArcGIS Survey123 data retrieved from your phone, please refer to the previous blog post here.

Recovering your ArcGIS Survey123 data as CSV files using Python

If you’ve updated a survey schema but have users who haven’t yet sent in all their entries, they will encounter errors when trying to submit their data. Fortunately, there’s a solution to this issue: you can export your SQL database as CSV files and upload them to ArcGIS Online as a feature layer, either directly or through ArcGIS Pro

To get started, download this Python script from GitHub and open it in a code editor like Visual Studio Code. If you are using VS Code, make sure to install Python both as an extension in the editor (instruction below) and from the Microsoft Store.

 A screenshot of Visual Studio Code’s extensions. With a red bound box over the extension icon in an tool bar to the left with a number 1 point to it. Then another red box around the python extension at the top of the list to the right of the tool bar with a number 2 pointed towards it.

How to download Python on Visual Studio Code

Once you have the Python script open in the editor, be sure to trust the file/folder. We can now start making small adjustments to the script to match your environment. Start by updating the IN_DB variable on line 11 to point to the directory of your SQL database from your phone. Next, set the OUT_DIR variable to the file path where you’d like the CSV files to be saved (example below).

A screenshot of python code stating “# Constants for easy running IN_DB = r'C:\Users\jtishler\OneDrive - Esri Canada\Desktop\a084e65cd9403d813e8ec667ca329a63.sqlite' OUT_DIR = r'C:\Users\jtishler\OneDrive - Esri Canada\Desktop\outs'”

Example code

Scroll down to around line 103. Here, you will see that the code is currently configured to only pull surveys with status codes 1 and 3 – these represent surveys in your outbox or those that have encountered submission errors. If you’d prefer to export all surveys, you can modify line 103 to the following:

for row in cur.execute('SELECT name, data, status from Surveys where status = 0 or status = 1 or status = 2 or status = 3 or status = 4'):”

This change will include all surveys from your phone. (See image below for the updated code block).

A screenshot of multiple lines of python code with a highlighted section stating “For row in cur.execute('SELECT name, data, status from Surveys where status = 0 or status = 1 or status = 2 or status = 3 or status = 4'):”

Example code

Once you have updated the code, click ‘Run’ in the top right corner.

After the script has run, navigate to the directory you specified for the CSV files (Example below). You will find several CSV files, organized by survey status. Files in the ‘Adds’ folder correspond to status codes 0,1, and 3, while those in the ‘Updates’ folder represent status codes 2 and 4. (Note: Repeats will also be exported as a separate CSV file Showing a parent global ID to Identify which records they pertain to.)

A screenshot of a file explorer on a computer with several files and a red bounding box around a CSV file with an arrow pointing towards text stating “Adds=0,1 and 3”. Then a Green bounding box around a second CSV file with an arrow point towards text stating “update = status codes 2 and 3”.

Recovering your data to ArcGIS Online as-is

Now that your data has been converted, head over to ArcGIS Online and ‘Add a New Item.

A screenshot of the content page of ArcGIS Online with a red bound box and an arrow around the new item button in the top left.

Next, drag the CSV file containing the survey entries you want to upload. Choose to add it as a hosted feature layer and use the X and Y geometry fields to map the points. Once done, save the file with your preferred name. Your entries will now be successfully restored to ArcGIS Online (see demonstration below).

Gif of how to publish the csv file on ArcGIS Online.

Using CSV files with ArcGIS Pro to fully recover the survey data

What if you have a complex survey with attachments and/or polygons?

Don’t worry! It is still possible to recover everything using ArcGIS Pro. In the next 2 sections, we will discuss how to use the recovered Excel sheets to retrieve points with attachments, as well as polygons or lines.

Recovering Points and Attachments

With this workflow, you can easily recover attachments and spatial point data from your phone’s surveys. For this to work, you will need:

  1. The converted CSV files you retrieved from the Python script and
  2. The folder survey attachments you extracted from your phone.

First, you will need to edit the CSV file in Microsoft Excel:

  • Remove empty rows. You can filter for blank rows and delete them. (Here's a step-by-step guide on how to do this.)

  • Convert epoch time to date-time format. Use the following Excel formula to convert the epoch value into a readable date and time:
    =TEXT((EPOCH/1000 + ("1/1/1970"-"1/1/1900"+1)*86400) / 86400,"DD/MM/YYYY HH:MM:SS")
  • Ensure photo filenames are listed under the picture field. If there are images attached to the records, make sure the filenames are correctly listed under the picture field. This is crucial for attaching the images to the corresponding points in your new feature layer. (For more on this, refer to the “Create a replicate survey from an SQLite database in ArcGIS Pro" section).

  • Rename and save. Save the workbook as a CSV file in the same folder as your attachments.

Now, upload the CSV file to ArcGIS Online and add it to your ArcGIS Pro project. Once it is added, right-click the table and select Create Points from Table > XY Table to Point.

Screenshot of ArcGIS Pro after a table was right clicked showing 2 lists of options with an arrow from the option “Create points from Table” and a red bounding box over the option “XY Table To Point.”

Now you can display your data by choosing X field as x_geometry and Y field as y_geometry.

A screenshot of arcgis pro tool “XYtable to point” with several entres noted as follows: an input table point to a csv file, an output feature class point to a folder location, an X_field pointing to the field X_geometry, the y field pointing to the y_geometry field, z field pointing to nothing and a coordinate system selected as GCS_WGS_1984.

Example tool

To recover your attachments, use the Enable Attachments tool with your new point layer.

A screenshot of arcgis pro tool “enable attachements” with one entry stated as an iput dataset pointing to the xytable to point feature class.

Example tool

Next, use the Add Attachments tool to associate the correct attachments to your points. For this, set both the Input Dataset and Match Table to the point layer. Then, link the Match Path Field to the picture field, and finally, set the Working Folder to the directory where your CSV file and attachments are stored.

A screenshot of arcgis pro tool “add attachments” with several entries noted as follows: an input dataset pointing to the xytable to point feature class, a match table pointing to a the same xytable to point feature class. A match path field pointing to the picture field and a working folder entry point to a folder named “arcgispro”.

Example tool

The images should now be attached to their corresponding points.

A screenshot of an entry in ArcGIS Pro with several fields including a picture at the bottom showing the attachment that was added to the point.

Example entry

Following a similar process as previous sections, you can upload this data as a feature layer to ArcGIS Online from ArcGIS Pro. Simply right-click on the layer, select Sharing and then choose Share as Web Layer.

Screen shot of ArcGIS Pro after a point feature was right-clicked showing 2 lists of options with an arrow from the option “Sharing” and a red bounding box over the option “Share As Web Layer”.

Make sure to Analyze the layer for errors or warnings. If you receive the error “Unique numeric IDs are not assigned”, simply click the three dots on the error and select 'Auto-Assign IDs Sequentially.’ One you’ve cleared the Analyze window, you can safely Publish the layer.

Screen shot of ArcGIS Pro error after the error was right-clicked showing a list of options with an red bounding box over the option “Auto-assign ID’s Sequentially”

Recovering Lines and Polygons

With this workflow, you can recover polygon or line data from surveys on your phone that were created using GeoShape or GeoTrace questions. To follow this process, you will need the CSV files you converted using Python. (Note: This process can be time-consuming – if you don’t need your polygons, you can skip these steps.)

We will start by editing the Shape field to prepare the data for ArcGIS Pro. In a blank cell next to the last field, use the TEXTSPLIT function to separate the points by commas (example formula: “=TEXTSPLIT(B3,",")”). After splitting, copy all the rows that spill out and paste them into a new row with just the values. Then, copy this data again and paste it into another cell using Transpose to paste it vertically.

A screenshot of an excel sheet options with two options that are pointed out. One pointing to a clipboard with numbers 123 with a red bounding box around it with an arrow that states “values” pointing towards the box. The other option is a clip board with a down arrow and right arrow in the corner of the clipboard with a green bounding box around it with an arrow that states Transpose pointing towards the box.

Paste options in Excel

Next, apply TEXTSPLIT again, but this time use a space instead of a comma in a new field (example formula: “=TEXTSPLIT(B3," ")”). Once you’ve split the data, straighten it out by copying and pasting it one last time into a new worksheet with just the values. Insert a new row at the top with six fields labeled:  Point_X, Point_Y, Start_X, Start_Y, End_X and End_Y.

You should already have the Point_X and Point_Y. The Start_X might currently be set to Z geometry (which defaults to 0), so copy the Point_X and Point_Y into the Start_X and Start_Y fields, respectively. For End_X and End_Y, copy the next set of points down the list (of Point_X/Y), all the way to the end.

A Screen shot of an excel sheet with six column titled as follows. Point_X, Point_Y Start _X, Start_Y End_X and End_Y. with seval corrdinats under each column. The second set of coordinates under Point_X and Point_Y has a red bounding box around it with an arrow point at the first set of coordinates in the columns End_X and End_Y.

Then, copy the first point’s Start_X and Start_Y as the last point’s End_X and End_Y. Your data should now look something like this:

A Screen shot of an excel sheet with six column titled as follows. Point_X, Point_Y Start _X, Start_Y End_X and End_Y. with seval corrdinats under each column.

Example result

Save this workbook as a CSV file and import it into ArcGIS Pro. (Note: This process is for one polygon – if you have multiple polygons, you’ll need to repeat the steps above for each one).

Add the CSV to your ArcGIS Pro project and use XY Table to Point to create a point layer.

A screenshot of arcgis pro tool “XYtable to point” with several entries noted as follows: an input table point to a csv file, an output feature class point to a folder location, an X_field pointing to the field point_X, the y field pointing to the Point_Y field, z field pointing to nothing and a coordinate system selected as GCS_WGS_1984.

Example tool

Next, use XY to Line to create the line feature. If you’re only working with lines, you can stop here and proceed to the end of this section. 

A screenshot of arcgis pro tool “XY to line” with several entries noted as follows: an input table point to the XY table to point file, an output feature class pointing to a folder location, a Start X_field pointing to the field Start_X, a Start Y_field pointing to the field Start_Y, a End X_field pointing to the field End_X, a End Y_field pointing to the field End_Y, a line type with Geodesic selected a ID with nothing selected and a coordinate system selected as GCS_WGS_1984.

Example tool

Now, use Feature to Polygon to create the polygon.

A screenshot of arcgis pro tool “Feature to polygon” with a few entries noted as follows: an input feature pointing to the xy to line feature, an output feature class pointing to a folder location, the option “preserve attributes is checked off and the entry label features has nothing for it.

Example tool

Finally, you can upload the feature to ArcGIS Online (as seen in the ‘Recovering Points and Attachments’ section).

Manually recovering your data using an SQL browser

This step should be considered an absolute last resort, as it takes the longest to complete. Only try this method if the previous approaches do not work for you.

To start this workflow, you need to download a database browser like DB browser for SQLite. This will allow you to freely edit the SQL database and actively recover your data.

Once you have downloaded the program of your choice, load the SQL database file from your phone and export it as a CSV.

Gif of how to export CSV file using DB browser for SQLite.

Once exported, open the file in Microsoft Excel using the Add Data tool. A pop-up window should open – simply select ‘Load.’

A screenshot of an excel sheet with the option “get data” selected showing two list of options with the options “From File” and From Text/CSV” pointed out with red arrows.

You will notice that the data for each record is in the data column.

This data can be parsed out using either the Text to Columns tool in Microsoft Excel or the Text Split equation. After splitting the data, the next step is to clean it up manually and organize it into a format that is easy to read in ArcGIS Online. This involves reconstructing the fields and data.

If the Python script is executed correctly, your data will resemble the example survey shown below. If you are only interested in the locations of the points, you can simply extract the x and y fields. (Note: Please keep in mind that this process can be tedious and should only be undertaken if the previous options fail to yield results).

A screenshot of an excel sheet with several Columns with separated data for each entry in the database.

After you have successfully reconstructed your data, you can re-upload it to ArcGIS Online by following the steps seen in the ‘Recovering your data to ArcGIS Online as-is' section.

Final Thoughts

You now have a way to recover your surveys in case of corruption or deletion. However, to avoid coming back to this blog, make sure to always enable the delete protection feature and back up your survey data frequently.

I hope you can now rest easy knowing there is a way to recover your survey items – even after deletion!

Extra Resources

  • GeoSnap: How to Recover ArcGIS Survey 123 Records from a Phone Using Python
  • Support article: Create a polygon from an XY data table
  • Support article: Create a replicate survey from an SQLite database in ArcGIS Pro

About the Author

Jordan is an Associate GIS Analyst at Esri Canada. He focuses on using his knowledge to create, analyze and map data with Esri technology in support of organizations and customers. Jordan has a diploma in Geographic Information Systems from Fleming College as well as a bachelor’s degree in Forestry from the University of New Brunswick. Before coming to Esri Canada, he activated his passion in GIS by pursuing the technology side of forestry. In his free time, he enjoys hiking, baking and learning new things about technology.

Profile Photo of Jordan Tishler