Skip to main content

Exporting Python Barcode Scan Data to CSV and Excel

· 4 min read
Serhii Hrekov
Serhii Hrekov
software engineer, creator, artist, programmer, projects founder

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​