Converting JSON to custom formatted CSV

Questions
1

I have a folder of about a million JSON files and I wrote this to convert them quickly to CSV, but the formatting is not correct for the software I’m feeding them into.

Here’s the code I’m using:

Do[(Export["CSV_OUT_" <> FileBaseName@jsonlist[[i]] <> ".csv", 
Import[jsonlist[[i]]]]),{i, 1, Length[jsonlist]}];

The result looks like this:

“”“from”" → {““address_city”” → ““MORGANVILLE””, ““address_country”” → ““UNITED STATES””, ““address_line1"” → ““123 TEST ST””, ““address_line2"” → ““STE 1"”, ““address_state”” → ““NJ””, ““address_zip”” → ““07751"”, ““company”” → “”””, ““name”” → ““ELENA CROSS””}” “”“id”” → ““sfm_c4kjaugl7u8psvqfatp0"”” “”“imb_code”" → ““897714123456789"”” “”“mail_date”" → “”“”" “”“mail_type”" → ““usps_first_class””" etc.

The correct results would look like this in CSV format:
from address_city,address_country,address_line1,address_line2,address_state,address_zip,company,name,id,imb_code,mail_date,mail_type,object,press_proof,size,target_delivery_date,to address_city,address_country,address_line1,address_line2,address_state,address_zip,company,name MORGANVILLE,UNITED STATES,123 TEST ST,STE 1,NJ,7751,ELENA CROSS,sfm_c4kjaugl7u8psvqfatp0,897714123456789,usps_first_class,

How can I export correctly formatted CSV files like the one above quickly?

sfm_c4kjaugl7u8psvqfatp0.json (803 Bytes)

2

Hi Bob,

CSV can only represent tabular data. The JSON sample contains hierarchical data, the “from” and “to” fields have nested keys and values. The usual way to deal with this is to unnest the nested structure by prefixing each field name in the inner structure with the field name of the outer structure.

unnest[prefix_String, assoc_Association] := 
 AssociationThread[prefix <> "." <> # & /@ Keys@assoc, Values@assoc];

toCSV[json_Association] := 
 json // 
   KeyValueMap[
    If[MatchQ[#2, _Association], 
      unnest[#1, #2], <|#1 -> #2|>] &] // {Flatten@Keys@#, Flatten@Values@#} &

json = Import["~/Downloads/sfm_c4kjaugl7u8psvqfatp0.json", "RawJSON"]
Export["sfm_c4kjaugl7u8psvqfatp0.json.csv", toCSV[json]]

This will work for one level of nesting, if there is more than one level then unnest has to be recursively called.

3

Can this be wrapped in a Do function to work on a folder of files?

4

Sure, something like this

jsonFiles = FileNames["*.json", "path/to/folder"];

Export[# <> ".csv", toCSV@Import[#, "RawJSON"]] & /@ jsonFiles