7

I'm trying to extract information from a JSON file and write some of the contents to a CSV file.

Here is an example of my text

"data":{"headers":{"sender":"[email protected]"
"to":"[email protected]"
"subject":"Help with this project"
"x-received-time":"14144273245408"
"received":"from abc.com ()\r\n        by mail.mail.com with SMTP (Postfix)\r\n        for [email protected];\r\n        Mon
"from":"\"Help with this project\" <[email protected]>"
"date":"Mon, 27 Oct 2014 09:03:14 -0500"
"id":"1414427328-2345855-frank"
"to":"[email protected]"
"time":14144273245408
"subject":"Help with this project"
"fromfull":"[email protected]"

I want to grab the contents from: to, fromfull, id, subject, date and write it to a csv file where To is column A, fromfull is column B, and so forth.

Can anyone offer any assistance? This is a JSON response.

7
  • Your content looks like JSON. Consider looking for ways to convert JSON to CSV.
    – muru
    Oct 27, 2014 at 18:05
  • Will the data always be laid out the way you've shown it? Or did you simply do that for clarity purposes?
    – Chuck R
    Oct 27, 2014 at 18:21
  • This is always the layout. There is more information within the file than just this, but it will always be laid out pretty much identical to this. Prior to this layout, I ran a SED to break everything out into multiple lines. So if I extract the data after that SED, then the layout should always look like this. Oct 27, 2014 at 18:26
  • try piping your output through json_pp, less work.
    – ppetraki
    Oct 27, 2014 at 19:26
  • 3
    Check out jq. This will be as simple as something like: jq '. | @csv' myfile.json.
    – David
    Oct 27, 2014 at 22:42

6 Answers 6

16

You can convert this JSON to CSV in a single line with jq.

jq '.data.headers | [.sender, .to, .subject, ."x-received-time", 
.received, .from, .date, .id, .to, .subject, .fromfull] 
+ [(.time | tostring)] | join(", ")'

Breakdown:

  • .data.headers - Emit headers as an object
    • If data contained an array of headers it would be .data[].headers
  • […string keys list…] - Emit string values as an array
  • + [(.time | tostring)] - Emit time as a string and add to the array
  • join(", ") - Join the array values using a comma and a space
    • Substitute your favorite delimiter here

Update 2022:

jq supports @csv (Comma Separated Value) or @tsv (Tab Separated Value) formatter. The same above code can be written as:

jq -r '.data.headers | [.sender, .to, .subject, ."x-received-time", 
.received, .from, .date, .id, .to, .subject, .fromfull] 
+ [(.time | tostring)] | @csv'
4
  • 1
    You have to parse the output to | sed 's/"//g' to get rid of the quotes at the end
    – rubo77
    Nov 19, 2016 at 3:00
  • 2
    Nice tool to develop this: jqplay.org
    – rubo77
    Nov 19, 2016 at 8:05
  • jq also has a dedicated @csv filter, very nicely described here: stackoverflow.com/questions/32960857/…
    – tjb
    Dec 17, 2018 at 9:34
  • actualy, without the "-r" parameter, JQ will create a single cell with these values. You want to use "jq -r ... > data.csv" to output Comma Seperated values correctly. This way you can also handle newline characters "\n" Jul 31, 2020 at 12:11
7

You can use the following perl command to create the CSV output, open a terminal and type:

perl -n0e '@a= $_ =~ /"date":(".*?").*?"id":(".*?").*?"to":"(.*?)".*?".*?"subject":(".*?").*?"fromfull":"(.*?)"/gs;  while (my @next_n = splice @a, 0, 5) { print join(q{,}, @next_n)."\n"}' inputfile.txt

It will work even if you have multiple headers in your input file.

Note that only the last "to": field is taken into account (it seems that your headers provide the info twice)

The command output:

"Mon, 27 Oct 2014 09:03:14 -0500","1414427328-2345855-frank",[email protected],"Help with this project",[email protected]
4
  • This is what I'm looking for, but I need to add a few more fields as what you posted accounts for To and FromFull, but not ID, Subject, or Date. It looks like you are adding \"to\":\"(.*?)\".*? to indicate a new structure, but I can't pick out the exact syntax to add additional fields. THANK YOU so much, as I don't know PERL much at all. Oct 27, 2014 at 18:43
  • As indicated in the original post: to, fromfull, id, subject, date Oct 27, 2014 at 19:05
  • I basically just need to create a spreadsheet with basic topical information from emails. Oct 27, 2014 at 19:06
  • @BeMyFriend: Sorry I missed your edit, please check the new command Oct 27, 2014 at 19:13
6

Since you are working with JSON files, why not parse it as such? Install nodejs-legacy and create a NodeJS script such as:

#!/usr/bin/env node
// parseline.js process lines one by one
'use strict';
var readline = require('readline');
var rl = readline.createInterface({
  input: process.stdin,
  output: process.stdout,
  terminal: false
});

rl.on('line', function(line){
    var obj = JSON.parse(line);
    // add the fields which you want to extract here:
    var fields = [
        obj.data.headers.to,
        obj.data.headers.subject,
        // etc.
    ];
    // print the fields, joined by a comma (CSV, duh.)
    // No escaping is done, so if the subject contains ',',
    // then you need additional post-processing.
    console.log(fields.join(','));
});

Assuming you have a valid JSON string on each line of a file:

node parseline.js < some.txt

Or if you really want to read a single file and parse fields from that:

#!/usr/bin/env node
// parsefile.js - fully read file and parse some data out of it
'use strict';
var filename = process.argv[1]; // first argument
var fs = require('fs');
var text = fs.readFileSync(filename).toString();
var obj = JSON.parse(text);
// add the fields which you want to extract here:
var fields = [
    obj.data.headers.to,
    obj.data.headers.subject,
    // etc.
];
// print the fields, joined by a comma (CSV, duh.)
// No escaping is done, so if the subject contains ',',
// then you need additional post-processing.
console.log(fields.join(','));

Then run it with:

node parsefile.js yourfile.json > yourfile.csv
4
  • If you have a shabang in place can't you just ./parseline.js < some.txt? Oct 28, 2014 at 10:33
  • @BenjaminGruenbaum Yes you can, but then you first need to chmod +x parseline.js first.
    – Lekensteyn
    Oct 28, 2014 at 15:04
  • to be fair I was pointing that out as a question out of politeness, I thought it could be a good edit for your question but you might disagree, good answer nontheless. Oct 28, 2014 at 15:45
  • @BenjaminGruenbaum I deliberately omitted it to avoid explaining the need to add the executable bit (which is not related to the post) and to clarify which program is responsible for it. You can of course make it executable (recommended to save typing!) or put it in $PATH as needed.
    – Lekensteyn
    Oct 28, 2014 at 15:50
3

You can use jsonv from GitHub

And then the following command:

cat YOUR_JSON_FILEname | jsonv to,fromfull,id,subject,date > output.csv
1

Here's a gawk script I just whipped up for you!

#!/usr/bin/gawk -f
BEGIN {
  FS="\""
  output=""
  nodata=1
}

/^"data"/{
  if( ! nodata )
  {
    gsub("|$","",output)
    print output
    nodata=0
  }
  output=""
}

/^"[^d][^a][^t][^a]/{
  if ( $2 == "to" || $2 == "fromfull" || $2 == "id" || $2 == "subject" || $2 == "date" )
    output=output$4"|"
}

END{
  gsub("|$","",output)
  print output
}

It should work on a file with a bunch of like entries. If you want to add other items to the list, just add them in the if statement. I did find one problem with your data set though: the dates. They contain commas so it can't be a true CSV. Instead I just separated it with another character.

1

Here is an awk implementation:

   awk -F ":" '{gsub("\"","",$1);key=$1;sub(key " ","");gsub("\\","",$0);value[key]=$0; if ("fromfull"== key) print value["from"] ";" value["to"] ";" value["fromfull"] ";" value["id"] ";" value["subject"] ";" value["date"] ;}' jsonFile > csvFile

This script read line until found "fromfull" line, than print csv line, so it should works also with multiple sequences.

This is the result:

  ""Help with this project" <[email protected]>";"[email protected]";"[email protected]";"1414427328-2345855-frank";"Help with this project";"Mon, 27 Oct 2014 09 03 14 -0500"
2
  • This was really close and worked for the most part. Just the perl implementation came out slightly better formatted. Thanks though! Oct 27, 2014 at 19:18
  • You are welcome. I update my answer, but @SilvainPineau solution is better, also because awk behaviour on `\\` is strange due to this bug.
    – Lety
    Oct 27, 2014 at 22:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .