Convert a SQLite database to Excel with Python

Convert a SQLite database to Excel with Python

Sooner or later the stock ledger ends up inside a database, and the person who needs it does not open databases. The accountant wants the month by category before closing; the owner wants a sheet they can sort, filter and add up on the counter computer; a supplier asks for the movements of one product to settle a difference in a delivery. The data is there, correct and in one place, but it is locked inside a file that does not open with a double click. This program does the translation: it reads a SQLite database and writes an Excel workbook with one sheet per table plus an index sheet, and it leaves one SQL query in a CSV so it can go out by mail.

It is written for the owner of a small business, for the accountant who receives the boxes of receipts and for the storekeeper who counts and writes things down. You do not need to program in order to follow it: the six steps are printed on screen. Two things are needed, Python and one library, and nothing else is installed or paid for.

What the ZIP contains

Why a workbook and not a picture of the database

A spreadsheet is where the numbers get used, not where they should be kept. The database holds the version that counts: each product exists once, a price is corrected in a single place and nothing is written down twice. The workbook is the copy that travels, to the accountant, to the bank, to the supplier. When a sheet comes back with handwritten corrections, the corrections are applied to the database and the workbook is exported again; the sheet is never the one in charge.

The second gain appears the first time somebody adds up a column. Each value is converted by the declared type of its column before it leaves the database, so a price reaches Excel as a number and not as text, which is what breaks the sums of most exports done by hand. The header stays in sight while you scroll, the automatic filter works without touching anything, and the index sheet says how many rows and columns each table carries. Row order is the same on every run, and that is what makes two monthly exports comparable.

What the program does

1. Prepares the database: with no argument it builds salida/ejemplo.db with the two tables of the package, products and movements, filled from the two CSVs.
2. Reads the schema: it lists the user tables, leaving the internal ones out, and asks each of them for its columns with PRAGMA table_info.
3. Reads the rows: every table ordered by its primary key, each value converted to the declared type of its column.
4. Writes the workbook: a single salida/inventario.xlsx, with the index sheet and one sheet per table.
5. Exports the query: it runs one query grouped by category and leaves it in salida/resumen_categorias.csv.
6. Closes the connection and summarises what it wrote: the database it read, how many tables, how many sheets and which files were left on disk.

How to run it

Install Python 3.11 or newer from the official site, tick the box that adds Python to the system, and install the only missing library with pip install openpyxl. Unzip the package into a comfortable folder, open the terminal there and type python sqlite_a_excel.py: with no argument the program builds its own sample database from the two CSVs and converts it, so the first run always has something to show. To convert your own database, pass its path with python sqlite_a_excel.py path/to/your_database.db. The workbook is redirected with --excel, and a query of your own goes to CSV with --consulta and --csv. If python does not answer on Windows, try py sqlite_a_excel.py.

The code, explained

The program reads from top to bottom and fits on a screen and a half. These are the five pieces worth understanding.

One: the imports. Four imports and a block that fails with a clear message. The engine is the important one: sqlite3 already ships inside Python, so the stock file is a single file you copy like a photograph. The only thing to add is openpyxl, the library that writes Excel files; if it is missing, the program says so and stops instead of leaving an empty workbook.

import argparse                                    # argparse: command line options, to accept a database or a query
import csv                                         # csv: writes the CSV of the query
import sqlite3                                     # sqlite3: the database ships inside Python, nothing to install
from pathlib import Path                           # pathlib: paths that work on Windows, Linux and Mac

# openpyxl is the library that writes Excel files in Python: it is the only one
try:
    import openpyxl                                # the openpyxl version is shown in the summary at the end
    from openpyxl import Workbook                  # Workbook: the Excel workbook that is going to be written
    from openpyxl.styles import Font, PatternFill  # Font and PatternFill: the bold header and its fill
    from openpyxl.utils import get_column_letter   # get_column_letter: turns column number 1 into the letter A
except ImportError:
    print("The openpyxl library is missing, and that is the one that writes the Excel file.")
    print("Install it with:  pip install openpyxl")
    raise SystemExit(1)

Two: which tables there are. SQLite keeps the list of its own tables in a table called sqlite_master, and the internal ones, whose name starts with sqlite_, are filtered out. What comes back is the user list, sorted by name: movements and products. Nothing is hard-coded, so pointing the program at another database needs no editing.

    sql = ("SELECT name FROM sqlite_master WHERE type = 'table' "
           "AND name NOT LIKE 'sqlite_%' ORDER BY name")
    return [registro[0] for registro in cursor.execute(sql)]

Three: reading the rows. Every table is read ordered by its primary key and, when there is no single key, by each of its columns. That line is what makes two exports comparable: without a fixed order the same database would land in the sheet in a different order on every run. Values are converted on the way out using the type that PRAGMA table_info declared, which is why a whole number arrives in Excel as a number.

    tipos = [columna[2] for columna in columnas]
    sql = f'SELECT * FROM "{tabla}" ORDER BY {orden_por(columnas)}'
    return [
        [convertir(valor, tipo) for valor, tipo in zip(registro, tipos)]
        for registro in cursor.execute(sql)
    ]

Four: writing the sheet with openpyxl. The header goes in with a bold font and a fill, the data starts on row two and each cell is written by type: amounts get a thousands format and text stays text. Three details matter for whoever opens the file: the frozen pane keeps the header visible while scrolling, each column width is fitted to its longest value with a cap, and the automatic filter is left over the header.

    for numero, fila in enumerate(filas, start=2):
        for posicion, valor in enumerate(fila, start=1):
            celda = hoja.cell(row=numero, column=posicion, value=valor)
            # Numbers are written as numbers; only text stays as text
            if isinstance(valor, float):
                celda.number_format = "#,##0.00"
            elif isinstance(valor, int):
                celda.number_format = "#,##0"
    # Frozen pane: the header stays in sight while you scroll down the sheet
    hoja.freeze_panes = "A2"
    # Each column width is fitted to the longest thing inside it
    for posicion, nombre in enumerate(nombres, start=1):
        medidas = [len(str(nombre))] + [len(str(fila[posicion - 1])) for fila in filas]
        letra = get_column_letter(posicion)
        hoja.column_dimensions[letra].width = min(max(medidas) + 2, ANCHO_MAXIMO)
    # Automatic filter on the header: you can filter and sort without touching anything
    ultima = get_column_letter(len(nombres))
    hoja.auto_filter.ref = f"A1:{ultima}{len(filas) + 1}"

Five: the query that leaves as CSV. The other half of the trade is one question answered with a file. This query joins the movements with the products, keeps the outgoing ones through a parameter, groups by category and returns the count, the units and the amount, largest first. The CSV is written in UTF-8 with a byte order mark, which is how Excel opens accents on a double click; over the sample data it comes out with three rows.

SQL_CONSULTA = (
    f'SELECT p."{COL_P_CATEGORIA}" AS "Category", '
    f'COUNT(*) AS "Movements", '
    f'SUM(m."{COL_M_UNIDADES}") AS "Units", '
    f'SUM(m."{COL_M_UNIDADES}" * m."{COL_M_PRECIO}") AS "Amount" '
    f'FROM "{TABLA_MOVIMIENTOS}" m JOIN "{TABLA_PRODUCTOS}" p '
    f'ON p."{COL_P_CODIGO}" = m."{COL_M_CODIGO}" '
    f'WHERE m."{COL_M_TIPO}" = ? '
    f'GROUP BY p."{COL_P_CATEGORIA}" '
    f'ORDER BY "Amount" DESC, "Category"'
)

What you will see on screen

The six steps are printed with fixed-width tables, so nothing drifts. A slice of the third and fourth, and the closing summary:

------------------------------------------
Table                      Rows    Columns
------------------------------------------
movements                    14          7
products                      8          5
------------------------------------------

CLOSING
========================================================================
  Database read: salida/ejemplo.db
  User tables: 2
  Workbook sheets: 3
  Excel file: salida/inventario.xlsx
  CSV file: salida/resumen_categorias.csv

And the CSV of the query, with the header exactly as the query named it:

Common mistakes and tips

• If the program stops saying the openpyxl library is missing, that is the whole problem: run pip install openpyxl and start again.
• Running it twice in a row makes no mess: the sample database is deleted and rebuilt, so the files on disk are always the ones of the last run.
• SQLite keeps dates as text in ISO format, such as 2026-01-05, and that is how they reach Excel: they sort correctly, but they are not Excel dates. If you need them as dates, add a column with DATEVALUE; the database needs no change.
• Do not move the program on its own. It looks for its data next to itself, so to run it from another folder, move the whole package.
• Before trying changes on a real database, copy that file into another folder: one file is the whole backup. The program only reads the database, it never writes to it.

When this is no longer enough

This is the right starting point once the stock ledger has to live in one place and the workbook has to be handed over without retyping anything. A moment comes, though, when the business asks for more: several people working at the same time, invoicing, purchasing, reports the accountant can print on their own. That is when a program built for it — such as Kardex Tauro, which is free and helps you bring the stock ledger into order — becomes the sensible choice: underneath it is still an SQLite database, only you no longer have to write it yourself. Until that moment, this ZIP and Kardex Tauro share the same idea: the data of the business is stored once, and stored properly.

Download the package, run it once and read the screen calmly. It is six steps, and at the end the database you already have opens as a workbook anybody can read.