converting csv to json not working as expected?

Question

I am following this article to convert csv to json. But I am not getting required output.

step 1 -

$ echo -e "foo,bar,quux\n1,2,3\n4,5,6\n7,8,9" > foo.csv

Test Step 2 -

$ jq -R 'split(",")' foo.csv | jq -cs

The output is as expected as below -

[["foo","bar","quux"],["1","2","3"],["4","5","6"],["7","8","9"]]

Next i copied the code from jq cookbook in to csv2json.jq and executed below command.

$ jq -cR 'split(",")' foo.csv | jq -csf csv2json.jq

But i am not getting expected output, I am getting below output -

[["foo","bar","quux"],["1","2","3"],["4","5","6"],["7","8","9"]]

Could someone help me fix this. Thanks in advance.

The expected or required output is -

[
 {
   "foo": 1,
   "bar": 2,
   "quux": 3
 },
 {
   "foo": 4,
   "bar": 5,
   "quux": 6
 },
 {
   "foo": 7,
   "bar": 8,
   "quux": 9
 }
]

@Fravadona - thanks for the comment. I have added the expected output to the Q. — CodeBot, Aug 22 '23 at 11:16
Should you have to use jq for this? why not use other tools that can do it natively as opposed to write separate functions with jq? — Inian, Aug 22 '23 at 11:18
@Inian, I am fine with other tools too as long as the delimiter can be changed. The delimiter for my project data is `;` but NOT `,`. Does the other tools support this. — CodeBot, Aug 22 '23 at 11:25

score 3 · Answer 1 · answered Aug 22 '23 at 11:20

3

I would use Miller (available here for several OSs) for this task:

mlr --c2j cat foo.csv

Output:

[
{
  "foo": 1,
  "bar": 2,
  "quux": 3
},
{
  "foo": 4,
  "bar": 5,
  "quux": 6
},
{
  "foo": 7,
  "bar": 8,
  "quux": 9
}
]

answered Aug 22 '23 at 11:20

Fravadona

1

Exactly what I was going to propose as an alternate to jq – Inian Aug 22 '23 at 11:21
thanks for suggestion. In my original data file the seperator is `;` . Does miller support changing the delimiter. +1 – CodeBot Aug 22 '23 at 11:22
It does: `mlr --c2j --ifs=';' cat foo.csv` – Fravadona Aug 22 '23 at 11:25
@Fravadona - thanks. Above worked for few files. But for few other files I am getting error `mlr: mlr: CSV header/data length mismatch 43 != 5 at filename abc.csv row 9.` I am sure the data is correct and not corrupted as it works with my other utils. – CodeBot Aug 22 '23 at 11:30
@CodeBot it means that the ninth record has `43` fields instead of the expected `5`. You might need to edit your CSV with a text editor to see what's wrong in there – Fravadona Aug 22 '23 at 11:54

1 Answers1