json2csv output multiple jsons to one csv

Question

I am using json2csv to convert multiple json files structured like

{
  "address": "0xe9f6191596bca549e20431978ee09d3f8db959a9",
  "copyright": "None",
  "created_at": "None"
  ...
}

The problem is that I need to put multiple json files into one csv file.

In my code I iterate through a hash file, call a curl with that hash and output the data to a json. Then I use json2csv to convert each json to csv.

mkdir -p curl_outs
{ cat hashes.hash; echo; } | while read h; do 
    echo "Downloading $h"
    curl -L https://main.net955305.contentfabric.io/s/main/q/$h/meta/public/nft > curl_outs/$h.json; 
    node index.js $h; 
    json2csv -i curl_outs/$h.json -o main.csv;
done

I use -o to output the json into csv, however it just overwrites the previous json data. So I end up with only one row.

I have used >>, and this does append to the csv file.

json2csv -i "curl_outs/${h}.json" >> main.csv

But for some reason it appends the data's keys to the end of the csv file I've also tried

cat csv_outs/*.csv > main.csv

However I get the same output.

How do I append multiple json files to one main csv file?

You might try changing your curl command to add a following `echo` to add a newline to the curl output. Something like: `curl -L https://your.url.here; echo; | tee curl_outs/$h.json;`. You may have some luck investigating the `-w` for curl per the accepted answer here: https://stackoverflow.com/questions/12849584/automatically-add-newline-at-end-of-curl-response-body — j_b, Jun 11 '22 at 18:06

Zach Young · Accepted Answer · 2022-06-11T18:07:56.940

It's not entirely clear from the image and your description what's wrong with >>, but it looks like maybe the CSV file doesn't have a trailing line break, so appending the next file (>>) starts writing directly at the end of the last row and column (cell) of the previous file's data.

I deal with CSVs almost daily and love the GoCSV tool. Its stack subcommand will do just what the name implies: stack multiple CSVs, one on top of the other.

In your case, you could download each JSON and convert it to an individual (intermediate) CSV. Then, at the end, stack all the intermediate CSVs, then delete all the intermediate CSVs.

mkdir -p curl_outs
{ cat hashes.hash; echo; } | while read h; do 
    echo "Downloading $h"
    curl -L https://main.net955305.contentfabric.io/s/main/q/$h/meta/public/nft > curl_outs/$h.json; 
    node index.js $h; 
    json2csv -i curl_outs/$h.json -o curl_outs/$h.csv;
done

gocsv stack curl_outs/*.csv > main.csv;

# I suggested deleting the intermediate CSVs
# rm curl_outs/*.csv
# ...

I changed the last line of your loop to json2csv -i curl_outs/$h.json -o curl_outs/$h.csv; to create those intermediate CSVs I mentioned before. Now, gocsv's stack subcommand can take a list of those intermediate CSVs and give you main.csv.

Hi, I like the idea of this. Can you please explain what individual (intermediate) means? — Buddy Bob, Jun 11 '22 at 16:11
Also, im quite new to bash. How would I stack a directory of csv's? — Buddy Bob, Jun 11 '22 at 16:29

json2csv output multiple jsons to one csv

1 Answers1