Recover and convert .qcr files to CSV from the QuickCapture mobile app using the ArcGIS Pro Python Command Prompt

Summary

In the ArcGIS QuickCapture mobile app, sending records from the app to ArcGIS Online can fail and return errors, requiring users to recover the unsent records and export them so they can be appended to the feature layer or published as a new feature layer. The unsent records are stored locally on mobile devices in a QuickCapture Recovery (.qcr) file and can be recovered via email. This article provides the workflow to recover the unsent records and convert them to a CSV file using the ArcGIS Pro Python Command Prompt, and CSV is selected because it is one of the supported file formats that can be used in both ArcGIS Pro and ArcGIS Online.

Procedure

Note:
This workflow is applicable on iOS and Android devices.

1. In the QuickCapture mobile app, under the error message, tap the SEND VIA EMAIL option to send the unsent records to an email address as a .qcr file.

Note:
The unsent records can be sent to the email address used to sign in to the QuickCapture mobile app or another email address.

2. Download the .qcr file from the receiver's email address and save it on the local machine.
3. In File Explorer, browse to the .qcr file. In this example, it is 'Failed records.qcr'.
   
4. Convert the .qcr file to a .zip file.
   1. Right-click the .qcr file, click Rename and name it 'Failed records.zip'.
   2. In the pop-up message, click Yes to convert the .qcr file into .zip file.
      
5. Click the .zip file on the local machine and locate the .sqlite file inside the .zip file. In this example, it is projects_db.sqlite.
   
6. Download and install DB Browser for SQLite to inspect the status field of the unsent record.

Note:
Records with negative status values commonly represent records that failed to submit.

7. In the Windows search box on the taskbar, search for and right-click Python Command Prompt > Run as administrator.
8. In the Python Command Prompt, convert the SQLite database to a .csv file.
   1. Copy and paste the following code and press Enter to enter the Python interpreter prompt.

python

2. Locate the .sqlite file.

os.chdir(r"C:\Users\Your_folder directory")

3. Import the libraries.

import os
import sqlite3
import csv

4. Connect to the .sqlite file.

conn = sqlite3.connect("projects_db.sqlite")
cursor = conn.cursor()

5. Set the condition rule to select the unsent records.

cursor.execute("""
  SELECT *
  FROM Features
  WHERE Status < 0
  """)

6. Retrieve the query results from the .sqlite file.

column_names = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()

7. Write the unsent records output into a .csv file.

output_csv = "failed_records.csv"
  with open(output_csv, "w", newline="", encoding="utf-8") as f:
  writer = csv.writer(f)
  writer.writerow(column_names)
  writer.writerows(rows)

8. Close the .sqlite database connection.

conn.close()

9. Display a message indicating the conversion to the .csv file is successful.

print("Conversion completed. The SQLite database has been successfully converted to a CSV file.")

The following code is the full script.

# Change to the folder containing the SQLite database
os.chdir(r"C:\Users\Anie\Desktop\Testing_folder\UNSENT_RECORDS\test123SQLite")

# Import required libraries
import os
import sqlite3
import csv

# Connect to the QuickCapture SQLite database
conn = sqlite3.connect("projects_db.sqlite")
cursor = conn.cursor()

# Query unsent records (negative status values)
cursor.execute("""
    SELECT *
    FROM Features
    WHERE Status < 0
""")

# Get column names and records
column_names = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()

# Write the results to a CSV file
output_csv = "failed_records.csv"
with open(output_csv, "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerow(column_names)
    writer.writerows(rows)

# Close the database connection
conn.close()

# Confirmation message (no record count)
print("Conversion completed. The SQLite database has been successfully converted to a CSV file.")

The output shows the .csv file converted from the .sqlite file.