jsoncsvjq

Converting csv to json with jq


I'm currently practicing converting plain text to csv, and them csv to json. I need to do the conversion only with jq and basic Linux commands.

I didn't have any major problems converting plain text to csv, but further conversion is a bit tricky. Primarily because the first line and the last one are out of standard.

Here is my current csv:

Asserts Samples
1;expecting command finishes successfully (bash exe);7ms
1;expecting command finishes successfully (loading bin, execute);27ms
0;expecting command fails (bash exe, execute);23ms
...
0;expecting command prints some message (bash exe);12ms
0;expecting command prints some message (loading bin, execute);26ms
5;2;71.43;136ms

I need to convert this csv to the following format:

{
 "testName": "Asserts Samples",
 "tests": [
   {
     "name": "expecting command finishes successfully (bash exe)",
     "status": false,
     "duration": "7ms"
   },
...
   {
     "name": "expecting command prints some message (loading bin, execute)",
     "status": true,
     "duration": "26ms"
   }
 ],
 "summary": {
   "success": 5,
   "failed": 2,
   "rating": 71.43,
   "duration": "136ms"
 }
}

So far I have figured out:

Long attempts have failed to make a json structure with a corresponding arrays and objects structure. I would be glad for advice or guidance.


Solution

  • You can use jq with raw input option (-R) and -s to slurp the entire file as a single string. Then you split that into array of lines. You access test lines by $lines[1:-1] (we skip 1-st row) and for each test line, split it by ";" and map the fields to the desired keys. Since in your data "0" represents a successful test (status true), you can convert the status using (.[0] == "0"). Then deal with summary line (use -1 index to get it regardles of line count) and you should be good.

    Combining the above should give you this monster:

    jq -R -s '
      (split("\n") | map(select(length > 0))) as $lines |
      {
        testName: $lines[0],
        tests: ($lines[1:-1] | map(
          (split(";") | {
            name: .[1],
            status: (.[0] == "0"),
            duration: .[2]
          })
        )),
        summary: ($lines[-1] | split(";") | {
          success: (.[0] | tonumber),
          failed: (.[1] | tonumber),
          rating: (.[2] | tonumber),
          duration: .[3]
        })
      }
    ' input.csv
    

    Once run on your input data output should be

    {
      "testName": "Asserts Samples",
      "tests": [
        {
          "name": "expecting command finishes successfully (bash exe)",
          "status": false,
          "duration": "7ms"
        },
        {
          "name": "expecting command finishes successfully (loading bin, execute)",
          "status": false,
          "duration": "27ms"
        },
        {
          "name": "expecting command fails (bash exe, execute)",
          "status": true,
          "duration": "23ms"
        },
        {
          "name": null,
          "status": false,
          "duration": null
        },
        {
          "name": "expecting command prints some message (bash exe)",
          "status": true,
          "duration": "12ms"
        },
        {
          "name": "expecting command prints some message (loading bin, execute)",
          "status": true,
          "duration": "26ms"
        }
      ],
      "summary": {
        "success": 5,
        "failed": 2,
        "rating": 71.43,
        "duration": "136ms"
      }
    }