RaqForum 41 No.
93 View  • 
     ...

Convert a json string to a csv file

 

Below is a multilayer json file, where the 1st member of values field contains field names corresponding to the other members under this field:

{
  "range": "products!A1:E4",
  "majorDimension": "ROWS",
  "values": [
    [
      "product_name",
      "price"
    ],
    [
      "Rugrats - Bedtime Bash [VHS]",
      "36.95"
    ],
    [
      "Teenage Mutant Ninja Turtles II - The Secret of the Ooze [VHS]",
      "29.89"
    ],
    [
      "Teenage Mutant Ninja Turtles II - The Secret of the Ooze [VHS]",
      "29.89"
    ]
  ]
}
{
  "range": "products!A1:E4",
  "majorDimension": "ROWS",
  "values": [
    [
      "product_name",
      "price"
    ],
    [
      "Rugrats - Bedtime Bash [VHS]",
      "36.95"
    ],
    [
      "Teenage Mutant Ninja Turtles II - The Secret of the Ooze [VHS]",
      "29.89"
    ],
    [
      "Teenage Mutant Ninja Turtles II - The Secret of the Ooze [VHS]",
      "29.89"
    ]
  ]
}

Use Java to convert this json file to a standard format csv file:

range,majorDimension,product_name,price
products!A1:E4,ROWS,Rugrats - Bedtime Bash [VHS],36.95
products!A1:E4,ROWS,Teenage Mutant Ninja Turtles II - The Secret of the Ooze [VHS],29.89
products!A1:E4,ROWS,Teenage Mutant Ninja Turtles II - The Secret of the Ooze [VHS],29.89

Write the following SPL script:

A

1

=json(file("source.json").read())

2

=A1.values.m(2:).new(A1.range:range,A1.majorDimension:majorDimension,~1:product_name,~2:price)

3

=T("result.csv":A2)

A1: Read the json file as a string and convert it to a json object.

A2: Get members from the 2nd to the last under A1’s values field and generate a two-dimensional table based on this sequence. ~1 represents the 1st member.

A3: Write the two-dimensional table, including the field names, to a csv file.

Read How to Call a SPL Script in Java to find how to integrate SPL into a Java application.

Source: https://stackoverflow.com/questions/72197515/trying-to-convert-a-json-file-or-string-to-a-csv-file-gives-an-empty-csv-file


 
 •  93 View  •