Convert multiple JSON files to a single .CSV file

Question

I am trying to convert 2000 JSON files of the same dimensionality to .csv and merge it to a single .csv file. What would be the best place to look at? Please assist.

Answer 1

I had a bunch of .json files with the same problem. My solution was to use a bash script to loop all of the files, and use jq on each to convert to individual csv files. Something like

i=1
for eachFile in /path/to/json/*.json; do
    cat json-$i.json | jq -r '.[] | {column1: .path.to.data, column2: .path.to.data} | [.[] | tostring] | @csv' > extract-$i.csv
    echo "converted $i of many json files..."
    ((i=i+1))
done

Then you can cat and >> all of those in a similar loop to a single .csv file. Something like

i=1
for eachFile in /path/to/csv/*.csv; do
    cat extract-$i.csv >> concatenate.csv
    ((i=i+1))
done

If you are crafty enough, you can combine those into a single script... edit: in fact, it's just a matter of adding a > to the first script, and using a single file name, so cat json-$i.json | jq -r '.[] | {column1: .path.to.data, column2: .path.to.data} | [.[] | tostring] | @csv' >> output.csv

Answer 2

There's this great program from Withdata which unfortunately costs a bit of money, but there is a 30 day free trial if you just need a quick fix. Its called DataFileConverter and there is a guide on their website as to how to change json files specifically into .csv. If you're looking for a free program try this repository https://github.com/evidens/json2csv. It's written in python but can still be used with the directions.

Answer 3

Using pathlib and pandas

from pathlib import Path

import pandas as pd

path = "/path/to/files/root/directory/"
files = list(Path(path).rglob("*json.gz"))

pd.concat((pd.read_json(file, compression="gzip") for file in files), ignore_index=True).to_csv(f"{path}/final.csv")