PDF to SQL: Load Statement Rows into a Database

There is no direct path from PDF to SQL. Convert the PDF's table to a CSV first, create a table with proper date and decimal columns, then bulk load the CSV into a staging table and copy new rows across under a unique key. That last step lets you reload any statement without creating duplicate transactions.

Converting the PDF to a CSV the database can load

A CSV is the bridge because every SQL database has a bulk loader for it and none reads PDF pages. A PDF stores text at positions on a page, not rows and columns. A converter turns those positions into rows, and CSV is the simplest file that carries them into a database.

The CSV for a database load has stricter needs than one meant for a person. A spreadsheet user can ignore a stray "Page 2 of 6" line. A loader cannot: it either fails on the line or inserts it as a transaction. Before loading, the file should meet these conditions:

• One header line, then data. No title rows, no repeated column headings from each page.
• The same column count on every line. Empty values are written as nothing between two commas, not left out.
• Plain numbers. No currency symbols, no thousands separators, a minus sign rather than brackets or a trailing "DR" for debits.
• Dates in one format, ideally year-month-day, such as 2026-05-14, which databases parse without guessing the order.
• UTF-8 encoding, so accented payee names load as written.

Plain-number rules are where statements most often fail. Banks print debits in many ways, and a loader expecting a number will reject "(61.20)" or "61.20-". Clean those during conversion, not in SQL afterwards.

Opening the file once before the load saves a failed run. A text editor shows the raw lines, which is what the loader sees. If you do not have Excel on the machine, viewing a CSV file without Excel lists simple ways to look at it. FastBankConvert is getting ready to open and is built to produce CSV from statements, with a warning before download when the balances do not add up; Get started puts you on the list.

Designing the SQL table with date and decimal column types

A statement table needs a date type for dates, an exact decimal type for money, and text for everything else, including account and check numbers. Getting the types right at creation is what makes later queries trustworthy: sums come out exact, dates sort by time, and identifiers keep their leading zeros.

Money is the column most often typed wrong. Floating-point types store approximations. The PostgreSQL documentation on numeric types says plainly that real and double precision are inexact, and recommends numeric for monetary amounts and other quantities where exactness is required. Other databases have the same split, usually called DECIMAL or NUMERIC. The same reasoning applies when money passes through JSON on its way to a database, which storing money amounts in JSON covers.

Here is an example table in PostgreSQL. Names and sizes are illustrative.

CREATE TABLE statement_line (
  account_id   text          NOT NULL,
  statement_end date         NOT NULL,
  line_no      integer       NOT NULL,
  posted_on    date          NOT NULL,
  description  text          NOT NULL,
  amount       numeric(14,2) NOT NULL,
  balance      numeric(14,2),
  check_no     text,
  PRIMARY KEY (account_id, statement_end, line_no)
);

A few choices in that table are deliberate:

• One signed amount column. Debits are negative, credits positive. Two columns, debit and credit, work too, but one signed column makes sums a single expression.
• Balance is nullable. Some statements, such as many credit card statements, print no running balance per line.
• Check number is text. It is an identifier, so a padded value such as 001042 keeps its zeros.
• The primary key includes a line number. The reason is in the duplicates section below.

How do you bulk load the statement CSV into SQL?

Use the database's bulk loader rather than row-by-row inserts. PostgreSQL has COPY, and its command-line client has \copy. MySQL has LOAD DATA, and SQL Server has BULK INSERT and an import wizard. Each reads the whole CSV in one command, with options for the delimiter, the header line and how empty values are treated.

In PostgreSQL, the difference between the two commands is where the file lives. The PostgreSQL COPY reference explains that COPY with a file name reads a file on the database server itself and needs special privileges, while psql's \copy reads a file on your own machine and sends it through. For a statement CSV on your laptop, \copy is the one you want.

Load into a staging table first, not straight into the real one:

CREATE TEMP TABLE stage (LIKE statement_line);

\copy stage (account_id, statement_end, line_no, posted_on, description, amount, balance, check_no) FROM 'statement-2026-05.csv' WITH (FORMAT csv, HEADER true)

The HEADER true option tells the loader the first line holds column names, so it is skipped rather than loaded. The same reference describes a MATCH setting that goes further, raising an error unless the header names match the table's columns in order. That is a good guard against a file whose columns shifted.

By default, COPY stops at the first bad row and loads nothing from the file. That is the safe behaviour for financial data. A partial load of a statement is worse than none, because the gap is hard to see. Fix the bad line in the CSV and run the load again.

The column list in the command is worth writing out even when it matches the file. If a future file adds a column, the load fails loudly instead of placing values in the wrong fields.

Stopping duplicate statement rows on the next SQL load

Duplicates come from loading the same statement twice, which happens more often than anyone plans. A unique key that identifies each statement line, plus an insert that skips rows already present, makes every load safe to repeat. Load the same file five times and the table holds its lines once.

The key is the hard part. The obvious choice, date plus amount plus description, is wrong for bank data. Two coffees at the same shop for the same price on the same day are two real transactions with identical fields. A key on those fields would silently drop the second one.

A key built from the statement instead works:

The line number is assigned during conversion, counting from 1 down the statement. Two identical coffees get lines 36 and 37, so both survive. The same statement converted again produces the same numbers, so a reload matches the existing rows.

With the key in place, moving rows from staging into the real table is one statement:

INSERT INTO statement_line
SELECT * FROM stage
ON CONFLICT (account_id, statement_end, line_no) DO NOTHING;

One caution. If a statement is converted again with a different tool, or after a fix that adds a missed line, the line numbers can shift. In that case, delete the old statement's rows by account and statement end date, and load the new version whole. Treat a statement as the unit you replace, not single lines.

What should you query after each statement load?

After each load, run three checks: the row count matches the CSV, the amounts sum to the change in balance for the period, and no dates fall outside the statement period. Each is a short query. Together they catch the usual failures, a dropped row, a misread amount and a wrong year, before anyone builds a report on the data.

Example queries for one statement, with illustrative values:

-- 1. Row count: compare with the number of data lines in the CSV
SELECT count(*) FROM statement_line
WHERE account_id = 'chk-4417' AND statement_end = '2026-05-31';

-- 2. Net movement: should equal closing minus opening balance
SELECT sum(amount) FROM statement_line
WHERE account_id = 'chk-4417' AND statement_end = '2026-05-31';

-- 3. Dates outside the period
SELECT line_no, posted_on FROM statement_line
WHERE account_id = 'chk-4417' AND statement_end = '2026-05-31'
  AND posted_on NOT BETWEEN '2026-05-01' AND '2026-05-31';

The second query is the strongest. If the statement opened at 3,200.00 and closed at 2,945.60, the sum of amounts must be exactly -254.40. With numeric columns there is no rounding drift to excuse a small gap, so any difference is a real missing or wrong row.

Where the statement prints a running balance, a window function can find the exact line where things go wrong, by comparing each printed balance with the opening balance plus a running sum of amounts. The first line where they disagree is where to look.

Scanned statements and OCR digits in a PDF to SQL load

A scanned statement adds a risk the loader cannot see: the numbers in the CSV may be wrong but well-formed. Optical character recognition (OCR), the step that reads characters from an image, can turn an 8 into a 3 and produce a valid amount. The database accepts it without complaint, so the checks after loading carry the whole burden.

The balance query from the previous section is the main defence. A misread digit in one amount changes the period's net movement, so the sum no longer matches the change in balance. A misread digit in a printed running balance shows up in the window-function check as a single bad line, followed by lines that agree again.

Some misreads pass every arithmetic check. A wrong digit in a date that still falls inside the period, or in a check number, changes no total. Those need a person to compare a sample of rows with the page. Common digit errors in bank statement OCR lists the confusions that turn up most, which tells you where to look first.

It also helps to record where each statement came from. A small source column on a statements table, holding a value such as scan or digital, lets you rerun extra checks on scanned months later, or exclude them from an analysis until someone has reviewed them. The column costs nothing at load time and answers the question "can I trust this month?" a year from now.

For a database that will feed reports for years, ask for the bank's own downloaded PDF whenever you can. A digital PDF carries the exact characters the bank printed, which removes the OCR step and its errors from the load entirely.