Exporting Python Barcode Scan Data to CSV and Excel

Exporting your scan data is the final piece of the puzzle. While a database is great for storage, most team members prefer to see results in a spreadsheet.

In Python, the pandas library is the gold standard for this. It can read directly from your SQLite database and convert that data into a professional-looking Excel or CSV file in just a few lines of code.

📊 Exporting Scan Data to CSV and Excel

1. Installation

To handle Excel files (.xlsx), pandas needs a helper library called openpyxl.

pip install pandas openpyxl

2. The Export Script

This function connects to your inventory.db, pulls all the recorded scans, and saves them to your choice of format.

import sqlite3
import pandas as pd

def export_scans(format="csv"):
    # 1. Connect to the database
    conn = sqlite3.connect("inventory.db")
    
    # 2. Use Pandas to read the table directly into a DataFrame
    # A DataFrame is like a virtual spreadsheet in memory
    df = pd.read_sql_query("SELECT * FROM scans", conn)
    
    # 3. Export based on the requested format
    if format.lower() == "csv":
        df.to_csv("scan_report.csv", index=False)
        print("✅ Report exported to scan_report.csv")
    elif format.lower() == "excel":
        df.to_excel("scan_report.xlsx", index=False, engine='openpyxl')
        print("✅ Report exported to scan_report.xlsx")
    
    conn.close()

# export_scans("excel")

---

3. Adding Value: Auto-Export on Exit

You can modify your scanner script from the previous article to automatically generate a report whenever the user quits (presses 'q'). This ensures the "Manager’s Report" is always up-to-date at the end of a shift.

# Inside your main live_scanner_with_db() function:
# ... after the while loop breaks ...

print("Shutting down scanner...")
cap.release()
conn.close()
cv2.destroyAllWindows()

# Trigger the auto-export
print("Generating end-of-session report...")
export_scans("excel")

---

4. Why Use Pandas Over the Built-in csv Module?

While Python has a built-in csv module, using pandas offers several advantages for production environments:

• Header Handling: It automatically uses your database column names as the spreadsheet headers.
• Data Cleaning: If you have malformed data or empty timestamps, pandas can fix them in one line before exporting.
• Multiple Formats: One library handles CSV, Excel, JSON, and even HTML tables.
• Performance: It is highly optimized for large datasets (e.g., thousands of inventory items).

---

📚 Sources & References

• [1.1] Pandas Documentation: Comparison with SQL - Official guide on moving data between SQL and DataFrames.
• [1.2] OpenPyXL Documentation: Optimized Modes - Technical details for writing large Excel files.
• [1.3] Real Python: Reading and Writing Files in Pandas - Comprehensive tutorial on all supported export formats.

---