Download the scripts and tests (ZIP)
Python scripts, tests, and ready-to-run merge, join, and date practice files with expected results. No signup required.
A customer export can contain an ID such as 00127, a postal code such as 02108, and a balance such as 12.50. All three look numeric. Converting all three to numbers changes the first two identifiers and can change the representation of the balance. A format conversion should not silently make those decisions.
This guide produces a JSON array of objects from a local CSV. Every cell stays a string by default. Empty cells remain empty strings, with an explicit option to map them to JSON null. Duplicate column names and records with the wrong number of fields stop the conversion before an output file is created. The source is opened only for reading.
Tested: Python 3.10 on Windows, September 11, 2026. Standard library only. The sample records below are invented.
Decide what the receiving system expects
This script is useful when a receiver accepts text fields or when you want a faithful intermediate representation before applying a schema. It does not infer integers, dates, booleans, or currency. If an API requires a numeric amount, add a separate, explicit conversion for that named field and validate its allowed values. Do not apply a numeric conversion to every cell that happens to contain digits.
| CSV value | Default JSON value | With --empty-as-null |
|---|---|---|
00127 |
"00127" |
"00127" |
12.50 |
"12.50" |
"12.50" |
| empty cell | "" |
null |
literal null |
"null" |
"null" |
| one space | " " |
" " |
The distinction between a blank field and a missing field matters. A row ending in a comma contains an empty last field. A row that omits that field entirely has the wrong width and is rejected. Neither quoted nor unquoted empty fields carry enough information to recover a database’s original NULL policy; choose the flag based on your export contract.
Make a small input file
Save this as UTF-8 customers.csv. Use a text editor so a spreadsheet does not remove leading zeros before Python sees the file.
customer_id,name,postal_code,balance,note
00127,"Park, Mina",02108,12.50,
00128,Renée,00042,0.00,null
Copy the complete script below into csv_to_json.py in the same directory, or extract the downloadable example package and use examples/csv_to_json.py as the script path.
python csv_to_json.py customers.csv customers.json
The success message is Wrote 2 records to customers.json. The output is:
[
{
"customer_id": "00127",
"name": "Park, Mina",
"postal_code": "02108",
"balance": "12.50",
"note": ""
},
{
"customer_id": "00128",
"name": "Renée",
"postal_code": "00042",
"balance": "0.00",
"note": "null"
}
]
To map only empty cells to null, choose a new output filename:
python csv_to_json.py customers.csv customers-null.json --empty-as-null
Only the first record’s note becomes null. The second record still contains the string "null". Spaces are preserved, and column names are used exactly as written. A name containing only whitespace is rejected; id and id are distinct names and are not automatically combined.
Complete conversion script
"""Convert a UTF-8 CSV to a JSON array, preserving every cell as text."""
import argparse
import csv
import json
from pathlib import Path
def convert(source, destination, empty_as_null=False):
source, destination = Path(source), Path(destination)
records = []
with source.open(encoding='utf-8-sig', newline='') as handle:
reader = csv.reader(handle, strict=True)
header = next(reader, None)
if not header or any(not name.strip() for name in header):
raise ValueError('A nonblank header is required for every column')
if len(set(header)) != len(header):
raise ValueError('Duplicate column names are not allowed')
for number, row in enumerate(reader, 2):
if len(row) != len(header):
raise ValueError(f'Record {number}: expected {len(header)} fields, got {len(row)}')
records.append(dict(zip(header, [
None if empty_as_null and value == '' else value for value in row
])))
# Validate and serialize before creating any output. Never replace a file.
rendered = json.dumps(records, ensure_ascii=False, indent=2, allow_nan=False) + '\n'
with destination.open('x', encoding='utf-8', newline='\n') as handle:
handle.write(rendered)
return len(records)
def main():
parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument('source', type=Path)
parser.add_argument('destination', type=Path)
parser.add_argument('--empty-as-null', action='store_true')
args = parser.parse_args()
try:
count = convert(args.source, args.destination, args.empty_as_null)
except (OSError, UnicodeError, ValueError, csv.Error) as exc:
parser.exit(1, f'Conversion failed: {exc}\nIf writing failed, inspect the destination for partial output.\n')
print(f'Wrote {count} records to {args.destination}')
if __name__ == '__main__':
main()
Python’s CSV reader documentation explains the default string behavior and the newline='' file-opening requirement. We use a normal reader and check headers before building dictionaries, so repeated header names cannot silently overwrite a field. The JSON encoder documentation describes serialization options. Here, indentation makes inspection easier and ensure_ascii=False keeps Unicode names readable in UTF-8 output.
Check the result before importing it
Run this independent check beside the default output:
import json
from pathlib import Path
records = json.loads(Path('customers.json').read_text(encoding='utf-8'))
assert len(records) == 2
assert records[0]['customer_id'] == '00127'
assert records[0]['postal_code'] == '02108'
assert records[0]['name'] == 'Park, Mina'
assert records[0]['note'] == ''
assert records[1]['note'] == 'null'
print('Sample output verified')
The downloadable tests also cover a UTF-8 byte-order mark, quoted commas and embedded newlines, Unicode, existing-output refusal, source preservation, header-only input, duplicate or blank headers, short and long rows, malformed quoting, and invalid UTF-8. These are functional checks, not a performance benchmark.
Errors and practical limits
An empty file fails because there is no header. A valid header with no data creates []. A blank physical line outside a quoted field is rejected as a zero-field record. Error record numbers count CSV records including the header, not physical lines: a quoted cell can span several lines.
The program accepts comma-delimited UTF-8 input, optionally with a byte-order mark. It does not guess encodings or separators. Export a semicolon-delimited or legacy-encoded source correctly before using it. Python’s CSV parser also imposes a field-size limit; this script retains that limit rather than accepting arbitrarily large cells.
The entire dataset and serialized JSON text are held in memory. Use this approach for files that comfortably fit in available memory. For larger datasets, design a streaming writer with a clear incomplete-file protocol, or use a receiver that supports JSONL. Repeatedly appending standalone JSON objects does not produce a JSON array. Our JSONL validation guide covers that separate format.
Output creation uses exclusive mode: an existing destination, including the input itself, is not overwritten. Validation failures create no destination. A disk or process failure during writing can still leave a partial file, so accept output only after successful completion and parsing. This is not a transactional database import. It also preserves duplicate records; use the separate CSV deduplication guide when removing repeated keys is part of your task.