Build CSV-to-JSON Conversion Pipelines in Bash

1. Why CSV to JSON in Bash and not Python

A CSV to JSON conversion sounds like a task where reaching for Python with pandas is the reflex. In practice, that is massively overpowered for many use cases: a cron job that pulls a CSV export from the ERP system once a night and sends it as JSON to a REST API does not need 200 megabytes of Python dependencies. The combination of mlr (Miller), jq, and Bash glue code handles the same CSV to JSON conversion with a single, immediately runnable script.

The reason this pipeline makes particular sense in Bash lies in the nature of the task: the data flow is linear, from one file to another, with a few transformation steps in between. That is exactly what Unix pipes are built for. A CSV to JSON conversion in Bash means, in practice, a chain of two or three specialized tools, each optimized for its part of the task, instead of a monolithic script in a general purpose language.

This article shows how a production ready CSV to JSON conversion is built in Bash: from the simple base conversion through type coercion and nesting to validation and safely handling bad rows in large files.

2. Basics: mlr as a CSV-to-JSON converter

mlr (Miller) is the central tool for every CSV to JSON conversion in the shell, because it natively understands CSV, TSV, and JSON and converts between the formats without you writing your own parsing logic. The basic call mlr --icsv --ojson cat file.csv reads CSV and outputs a JSON array, where each row becomes an object and the header row is automatically used as the keys. This replaces hand written awk, which quickly hits its limits with commas inside quoted fields.

The decisive advantage of mlr over a naive awk -F, approach is correct handling of RFC 4180 compliant CSV: fields with commas that are wrapped in quotes are recognized correctly, as are escaped quotes inside a field. Anyone building a CSV to JSON conversion with plain awk -F, produces broken output for every field containing a comma, without it being immediately obvious.

#!/usr/bin/env bash
# csv-to-json.sh — convert CSV export to a JSON array with Miller
set -euo pipefail

readonly INPUT_CSV="${1:?Usage: csv-to-json.sh <input.csv>}"
readonly OUTPUT_JSON="${INPUT_CSV%.csv}.json"

# --icsv: input is CSV, --ojson: output is a JSON array
# --quote-original preserves quoting exactly as found in the source file
mlr --icsv --ojson cat "$INPUT_CSV" > "$OUTPUT_JSON"

echo "Converted $(wc -l < "$INPUT_CSV") rows to $OUTPUT_JSON"

# Quick sanity check: is the result valid JSON?
jq empty "$OUTPUT_JSON" || { echo "[ERROR] Invalid JSON output" >&2; exit 1; }

This skeleton is already sufficient for simple, flat CSV files. But as soon as numbers get output as strings where an integer or float is actually expected, another processing stage becomes necessary, covered in the next section. That is the most common pitfall in every CSV to JSON conversion: CSV fundamentally has no data types, every field is initially a string.

3. Type coercion: handling numbers, booleans, and null values correctly

A naive CSV to JSON conversion outputs every value as a JSON string, including numbers and booleans. For an API expecting "price": 19.99 instead of "price": "19.99", this leads to validation errors or silent misinterpretation on the receiving side. mlr often detects numbers automatically when converting to JSON by default, but for mixed columns or leading zeros (for example postal codes), explicit control is needed.

The robust solution is to apply the type mapping explicitly with jq after the base conversion. With tonumber, strings become numbers, and with a conditional expression, text values like "true" and "false" become real booleans. For fields that can be empty, it matters to deliberately convert an empty string to null instead of leaving it as an empty string in the JSON, because many APIs distinguish between "field not set" and "field is an empty string".

#!/usr/bin/env bash
# convert-typed.sh — CSV to JSON with explicit type coercion via jq
set -euo pipefail

readonly INPUT_CSV="${1:?Usage: convert-typed.sh <input.csv>}"

mlr --icsv --ojson cat "$INPUT_CSV" | jq '
  map(
    .price      |= (if . == "" then null else (. | tonumber) end)
  | .quantity   |= (if . == "" then null else (. | tonumber) end)
  | .is_active  |= (. == "true" or . == "1")
  | .postal_code |= tostring
  )
' > "${INPUT_CSV%.csv}.typed.json"

echo "Typed conversion complete"

The postal_code field is deliberately forced back into a string with tostring, because leading zeros in postal codes would be lost during conversion to a number. This kind of explicit, field by field type rule is the core of a robust CSV to JSON conversion: every field gets a deliberate decision instead of relying on automatic heuristics that fail on edge cases like leading zeros or mixed data types.

4. Building nested JSON structures from flat CSV columns

CSV only knows flat tables, but modern APIs often expect nested objects. A typical requirement in a CSV to JSON conversion: columns like address_street, address_city, and address_zip should be merged into a nested address object instead of appearing as three separate top level fields in the JSON. jq handles this reshaping elegantly through object construction directly inside the filter expression.

A second common form of nesting arises when several CSV rows need to be combined into a single JSON object with an array field, for example several order lines into one order with an items array. For that, jq groups with group_by on a shared key such as order_id and builds an object with the nested array per group.

#!/usr/bin/env bash
set -euo pipefail

# Flatten address_* columns into a nested address object
mlr --icsv --ojson cat customers.csv | jq '
  map({
    id: .customer_id,
    name: .full_name,
    address: {
      street: .address_street,
      city: .address_city,
      zip: .address_zip
    }
  })
' > customers.nested.json

# Group order line items into a single order object with an items array
mlr --icsv --ojson cat order_lines.csv | jq '
  group_by(.order_id) | map({
    order_id: .[0].order_id,
    customer: .[0].customer_name,
    items: map({sku: .sku, quantity: (.quantity | tonumber)})
  })
' > orders.nested.json

This kind of nesting is the point where a CSV to JSON conversion clearly moves beyond a pure format change and starts performing real data modeling. The decision of which columns belong in a sub object should always be derived from the target schema of the receiving API, not from the arbitrary column order in the CSV source.

5. Using jq for post-processing and API formatting

After the base conversion and typing, one last step often remains: the JSON has to be brought into the exact payload format expected by the target API. Many REST APIs do not expect a raw array but a wrapper object like {"data": [...], "meta": {"count": N}}. For a CSV to JSON conversion feeding directly into an API import, this final formatting step is mandatory, otherwise the import fails with a schema error.

jq builds this wrapper in a few lines: {data: ., meta: {count: length, generated_at: now}} produces exactly the structure many bulk import endpoints expect from the raw array. The now function returns a Unix timestamp, which can be converted to ISO 8601 format with todate if needed, which most APIs prefer.

#!/usr/bin/env bash
set -euo pipefail

mlr --icsv --ojson cat products.csv | jq '{
  data: .,
  meta: {
    count: length,
    generated_at: (now | todate),
    source: "products.csv"
  }
}' > products.payload.json

echo "Payload ready: $(jq '.meta.count' products.payload.json) records"

6. Validation before import: schema checking with jq

A CSV to JSON conversion sent to an API without checking leads, on missing required fields, to a bulk import aborting midway through processing, which is especially problematic for non-transactional APIs and results in inconsistent data states. Before the actual submission, a validation stage pays off, checking with jq whether every object contains the expected required fields and whether the values are plausible.

The approach: a jq filter that finds every object missing a required field or with a value outside a plausible range, and outputs those as a separate error list. Only if this error list is empty does the CSV to JSON conversion proceed to the next step. This check is far cheaper than a failed API import with hundreds of records, half of which were already processed.

#!/usr/bin/env bash
set -euo pipefail

readonly JSON_FILE="products.typed.json"

# Find records missing required fields or with implausible values
invalid_records="$(jq '
  [.[] | select(
    (.sku == null or .sku == "") or
    (.price == null or .price < 0)
  )]
' "$JSON_FILE")"

invalid_count="$(echo "$invalid_records" | jq 'length')"

if (( invalid_count > 0 )); then
  echo "[ERROR] $invalid_count invalid records found:" >&2
  echo "$invalid_records" | jq -c '.[]' >&2
  exit 1
fi

echo "All records valid, proceeding with import"

7. Converting and uploading large CSV files in batches

Sending a CSV export with a hundred thousand rows as a single JSON array to an API regularly leads to timeouts or memory problems on the server side. For a CSV to JSON conversion at that scale, batching is mandatory: the file gets split into fixed size chunks, each chunk is converted individually and uploaded individually, with checkpointing between the batches.

mlr supports splitting large CSV files into smaller chunks with split, automatically keeping the header row in every part. Alternatively, a Bash loop with tail and head handles the same task manually, giving more control over batch size and progress.

#!/usr/bin/env bash
set -euo pipefail

readonly INPUT_CSV="large-export.csv"
readonly BATCH_SIZE=5000
readonly API_ENDPOINT="https://api.example.com/v1/products/bulk"

header="$(head -n1 "$INPUT_CSV")"
total_lines="$(($(wc -l < "$INPUT_CSV") - 1))"
batch_num=0

for (( offset = 1; offset <= total_lines; offset += BATCH_SIZE )); do
  batch_num=$(( batch_num + 1 ))
  batch_file="$(mktemp)"

  { echo "$header"; tail -n +$((offset + 1)) "$INPUT_CSV" | head -n "$BATCH_SIZE"; } > "$batch_file"

  json_payload="$(mlr --icsv --ojson cat "$batch_file")"

  curl -sf -X POST "$API_ENDPOINT" \
    -H "Content-Type: application/json" \
    -d "$json_payload" \
    -o "/var/log/import-batch-${batch_num}.json" \
    || { echo "[ERROR] Batch $batch_num failed" >&2; rm -f "$batch_file"; exit 1; }

  echo "[OK] Batch $batch_num uploaded (offset $offset)"
  rm -f "$batch_file"
done

8. Detecting bad rows instead of aborting the pipeline

Real world CSV exports almost always contain a few bad rows: an incorrectly quoted column, a mismatched field count, an encoding problem. A CSV to JSON conversion that aborts completely on the first broken row is impractical in production, because a single bad record then blocks processing of ten thousand correct rows. The better approach is isolating bad rows, logging them, and continuing processing with the remaining rows.

mlr emits an error message on stderr for a structurally inconsistent row (wrong field count) and aborts by default. With the flag --allow-ragged-csv-input, mlr tolerates rows with a differing field count, padding missing fields as empty and filling extra fields with generic key names. For a production ready CSV to JSON conversion, this gets combined with a downstream check that detects these incomplete rows and logs them separately.

#!/usr/bin/env bash
set -euo pipefail

readonly INPUT_CSV="messy-export.csv"
readonly GOOD_JSON="clean-records.json"
readonly BAD_LOG="rejected-rows.log"

# Tolerate ragged rows instead of aborting on the first structural error
raw_json="$(mlr --icsv --ojson --allow-ragged-csv-input cat "$INPUT_CSV")"

# Split into records with a valid sku vs. everything else
echo "$raw_json" | jq '[.[] | select(.sku != null and .sku != "")]' > "$GOOD_JSON"
echo "$raw_json" | jq -c '.[] | select(.sku == null or .sku == "")' > "$BAD_LOG"

rejected_count="$(wc -l < "$BAD_LOG")"
if (( rejected_count > 0 )); then
  echo "[WARN] $rejected_count rows rejected, logged to $BAD_LOG" >&2
fi

echo "Clean records: $(jq 'length' "$GOOD_JSON")"

This pattern, separating good and bad records instead of aborting the entire CSV to JSON conversion, matches the common dead letter queue principle from data processing: faulty units are isolated and reprocessed manually later, while the bulk of the data continues flowing unhindered.

9. Tools compared: mlr, jq, awk, and Python

The choice of the right tool for a CSV to JSON conversion depends heavily on the complexity of the transformation and the size of the file. For simple, flat conversions, mlr alone is often sufficient. As soon as type coercion, nesting, or validation come into play, jq adds the missing capabilities.

In practice, this shows: mlr alone covers the base conversion, jq alone cannot parse CSV, but the combination of both tools via a pipe covers practically every use case of a CSV to JSON conversion that would otherwise require significantly more effort to rebuild in Python.

10. Summary

A solid CSV to JSON conversion in Bash combines a handful of specialized tools into a robust pipeline: mlr handles correct parsing of RFC 4180 CSV including quoted commas, jq handles type coercion, nesting, and validation. Explicit conversion of strings to numbers and booleans prevents validation errors on the API side, and nested objects and arrays are built with a few lines of jq filter logic instead of complex programming.

For large files, batching with checkpointing belongs in the standard toolkit, so a timeout or an API error does not destroy the entire processing run. Bad rows should be isolated and logged instead of aborting the entire CSV to JSON conversion. Together, these building blocks produce a pipeline that runs just as reliably in production environments as a Python solution, but without additional runtime dependencies.

11. FAQ: CSV-to-JSON Conversion Pipelines