Exports

The export worked. Then the spreadsheet changed the data.

A gene name became a date. Your customer codes and leading zeros can meet the same fate.

Published 4 min read

Jump to the CSV export commands
A three-dimensional rendering of a DNA double helix

Photo: DNA Double Helix by NHGRI, National Human Genome Research Institute, Public domain, via Wikimedia Commons.

SEPT2 used to be a gene symbol. Open it in a spreadsheet with automatic conversion enabled and it could become 2-Sep. The cell still had a value. It just no longer identified the gene.

A 2016 Genome Biology study checked supplementary Excel files from 18 journals. Among 3,597 papers with spreadsheet gene lists, 704 had gene name errors: 19.6%. That was a finding about the selected papers, not all spreadsheets or all science.

The familiar version is a customer code such as 00123 becoming 123. The export may contain the right bytes. The next program can still interpret them differently.

Export SQL query results to CSV

These examples assume an existing customers table with id, name and postal_code columns. Use a new output filename: exports can overwrite an existing file. Postal codes and identifiers should remain text when leading zeros matter.

SQLite: write a local CSV file

Inside the sqlite3 shell
.headers on
.mode csv
.once customers-export.csv
SELECT id, name, postal_code FROM customers ORDER BY id;

The shell writes the next query result to the named file. Headers name the columns; they do not specify their types.

PostgreSQL: export with psql

Inside psql
\copy (SELECT id, name, postal_code FROM customers ORDER BY id) TO 'customers-export.csv' WITH (FORMAT csv, HEADER true)

The psql command \copy writes on the client machine using your local file permissions and database query privileges. Server-side COPY ... TO writes on the database host and has additional file-access restrictions. They are easy to confuse when a file does not appear where you expect.

The import is where types can change

Use the spreadsheet's text/CSV import flow and explicitly set identifier columns to text. Check the preview before loading. Quoting "00123" in a CSV does not reliably force a spreadsheet to preserve it as text.

CSV has fields and records but no standard column-type declaration. Commas, quotes and embedded line breaks need correct escaping. A record can span several physical lines, so counting newline characters is not a dependable row-count check.

Check values, not just the row count

Python: inspect the exported file
import csv

with open('customers-export.csv', newline='', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    assert reader.fieldnames == ['id', 'name', 'postal_code']
    for row in reader:
        print(repr(row['id']), repr(row['postal_code']))

Compare a few known records with the query result, especially leading zeros, long identifiers, non-ASCII text and multiline fields. If a spreadsheet is part of the handoff, repeat that check after its import and export. A file with the right number of rows can still contain the wrong values.

For untrusted free-text fields, also consider spreadsheet formula interpretation. A CSV writer handles delimiters and quotes; it does not guarantee that a spreadsheet will treat every cell as inert text. Use a controlled import or a delivery format that lets you set cell types.

CSV export questions

Does CSV preserve SQL data types?
No. The receiver needs an import schema or explicit type settings. Text that resembles a date or number may otherwise be converted.
Can one CSV row contain a line break?
Yes. A quoted field may contain line breaks. Parse the file with a CSV reader rather than splitting it into lines.
Why use ORDER BY in an export?
It makes the output order explicit. Without ORDER BY, a database does not promise a stable row sequence.

Sources