<link rel="stylesheet" href="https://fonts.googleapis.com/css2?family=Outfit:wght@600;700;800&family=Plus+Jakarta+Sans:wght@400;500;600;700&display=swap">
💻 Developer guide

How to Export SQL Query Results to CSV and JSON Without Breaking the Data

Export SQL results to CSV and JSON correctly: RFC 4180 quoting, Excel delimiters and UTF-8 BOM, NULL vs empty, dates, large numbers and native export commands.

📅 Published 2026-11-15⏱️ 5 min read🛠️ 4 free tools

Getting data out of a database looks like the easy part. Then the CSV opens in Excel with every row squashed into column A, names with accents turn into garbage, ZIP codes lose their leading zeros, and a comment field containing a comma shifts half the columns to the right. JSON exports have their own versions of the same problems.

This guide explains the rules that make exports survive the trip: how CSV quoting actually works, why Excel behaves differently by region, how to represent NULL, dates and large numbers, how to shape JSON for the consumer, and which database commands export directly without a round trip through a GUI.

CSV quoting rules (RFC 4180)

CSV has no single official standard, but RFC 4180 describes the version almost every tool reads. The rules are short, and nearly every broken CSV breaks one of them:

  • Fields are separated by commas and records by line breaks (CRLF in the RFC, though LF is widely accepted).
  • A field that contains a comma, a double quote or a line break must be wrapped in double quotes.
  • A double quote inside a quoted field is written as two double quotes: "She said ""hi""".
  • Spaces are part of the field. "a, b" and a, b are different values to a strict parser.
  • The first line may be a header row with column names; the file does not say whether it is, so state it in whatever reads the file.
  • Never build CSV by joining values with commas yourself. Use your language’s CSV writer (csv in Python, a CSV library in Node, the database’s own export), which applies these rules for you.
id,name,comment
1,Asha,"Paid, but asked for invoice"
2,"O'Neill, Sam","Said ""call me Monday"""
3,Ravi,"Line one
line two"

Excel, delimiters and the UTF-8 BOM

Excel does not read CSV the way the RFC describes; it uses the list separator from the operating system’s regional settings. In regions where the decimal separator is a comma, such as much of Europe, the list separator is a semicolon, so a comma-separated file opens as a single column. Options: export with semicolons for those users, add a first line reading sep=, (an Excel-only hint that other tools treat as data), or import through Data > From Text/CSV, where you choose the delimiter.

Encoding is the other common failure. Excel on Windows often assumes a legacy code page for CSV unless the file starts with a UTF-8 byte order mark (BOM, the bytes EF BB BF). Without it, names like José or Müller appear as José. Adding a BOM fixes Excel but can confuse scripts that do not expect it, so add it only to files meant for spreadsheet users.

Excel also converts values it thinks it recognises: leading zeros are dropped from 007, long numbers such as card or account numbers become 1.23E+15, and values like 3-4 or MARCH1 turn into dates. Importing through the text import wizard and setting those columns to Text avoids this.

NULL, empty strings, booleans and dates

CSV has no types and no NULL. Most exporters write NULL as an empty field and an empty string as "" (an empty quoted field), but not all readers preserve that difference. If the distinction matters, agree on an explicit marker such as \N, which PostgreSQL COPY and MySQL use by default, and document it.

Booleans come out as t/f from PostgreSQL, 1/0 from MySQL and SQL Server, and true/false from many drivers. Pick one form and convert in the query if the consumer cares.

Export dates and timestamps in ISO 8601 (2026-11-15 and 2026-11-15T09:30:00Z), never in a locale format like 15/11/2026 that the reader has to guess. Include the time zone or convert to UTC first; a timestamp without a zone is ambiguous as soon as the file crosses a border.

Shaping JSON exports

The default JSON shape for a result set is an array of objects, one object per row with column names as keys. It is self-describing and easy to consume, at the cost of repeating every key on every row. For very large exports, an object with a "columns" array and a "rows" array of arrays is much smaller, and newline-delimited JSON (one object per line, often called NDJSON or JSON Lines) can be streamed and processed one row at a time.

JSON keeps types that CSV loses, which brings its own decisions. NULL becomes null. Numbers stay numbers, except where precision matters: integers above 2^53 - 1 (for example 64-bit IDs) lose precision in JavaScript, and DECIMAL money values can pick up floating-point noise, so export both as strings. Dates have no JSON type and should be ISO 8601 strings.

When rows have a parent-child relationship, such as orders and their items, consider building nested JSON in the query rather than exporting two flat result sets and joining them in code.

Export directly from the database

For anything bigger than a screenful, let the database write the file. It handles quoting, encoding and NULLs consistently and is far faster than copying from a results grid.

-- PostgreSQL, server-side file (needs file-write privileges on the server)
COPY (SELECT id, email, created_at FROM users)
TO '/tmp/users.csv' WITH (FORMAT csv, HEADER true);

-- PostgreSQL via psql, file written on your own machine
\copy (SELECT id, email FROM users) TO 'users.csv' WITH (FORMAT csv, HEADER true)

-- PostgreSQL JSON: array of objects
SELECT json_agg(u) FROM (SELECT id, email FROM users) AS u;

-- SQL Server JSON
SELECT id, email FROM users FOR JSON PATH;

-- MySQL JSON
SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'email', email)) FROM users;

Checking and converting exports

When the data you have is SQL text rather than a live connection, such as a seed file, a dump excerpt or rows pasted from a bug report, the Neemtools SQL to CSV and SQL to JSON converters turn INSERT statements or a copied result grid into a file you can use, with RFC 4180 quoting for CSV and typed values for JSON. After a migration or a change to an export query, open the old and new CSV files in Excel Compare to confirm that only the expected cells changed.

Exports often contain personal data. Select only the columns the recipient needs, mask or drop sensitive fields, send files over encrypted channels rather than as email attachments, and delete local copies when you are done.

More guides you might like

Ready to try it?

Open the free SQL to CSV and apply what you just read. No sign-up, nothing uploaded.

Open SQL to CSV →

← All guides