Change JSON Content using any programming language or script or by tools to get data in excel format?

Question

I have a .JSON file (maybe wrong JSON structure file, but the extension is .JSON) When I use JSON beautifier we see as below:

 object {2}
    https://example.com/product/Detail/1        [3]
    https://example.com/product/Detail/2        [3]

Each of above lines has Three (3) details (Property):

https://example.com/product/Detail/1        [3]

    0   :   ACETATE                                
    1   :   SUSPENSION PARENTERAL 40 mg/1mL                                 
    2   :   INJECTION

without any consideration that this is a correct JSON file or not, I need an .xlsx file and so try to convert it using JSON to CSV or EXCEL Online tools and see following:

as we can see there is wrong conversion, we should have:

I means we should have two (2) rows and for each row we should have three (3) columns (property).

What is the easiest way to convert this .JSON file to Excel to get the above (Second picture)? is there any piece of code, scripts, or tools to can handle it easily?

Update:

This is the JSON file for two records.

{
  "https://example.com/product/Detail/1": [
    "METHYLPREDNISOLONE ACETATE",
    "METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL",
    "INJECTION, SUSPENSION",
    "PARENTERAL",
    "البرز دارو",
    "البرز دارو",
    "ریال",
    "ریال",
    "1 AMPULE in 1 CARTON",
    "METHYLPREDNISOLONE ACETATE 40 mg/1mL",
    "1397/12/24",
    "60000",
    "60000",
    "06260152433031",
    "3230858996456396",
    "",
    "H02AB04"
  ],
  "https://example.com/product/Detail/2": [
    "METHYLPREDNISOLONE ACETATE",
    "METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL",
    "INJECTION, SUSPENSION",
    "PARENTERAL",
    "کارخانجات دارو پخش",
    "کارخانجات دارو پخش",
    "ریال",
    "ریال",
    "1 AMPULE in 1 BOX",
    "METHYLPREDNISOLONE ACETATE 40 mg/1mL",
    "1400/09/22",
    "60000",
    "60000",
    "06260132425704",
    "2903849236347759",
    "",
    "H02AB04"
  ]
}

nothing wrong with the conversion, it converts to column instead to row. — Mang Hartono, Jul 10 '22 at 10:02
You've listed three entirely different languages and one framework, please pick one. Please do not post pictures of code or data, please paste as text only. Please also show your current attempt, or explain why you couldn't find the answer on Google, as all these languages have well documented JSON libraries? — Charlieface, Jul 10 '22 at 10:13
Which bit do you not agree with, I'd love to know which bit is problematic. This one https://meta.stackoverflow.com/questions/354931/when-is-it-appropriate-to-tag-multiple-languages-in-my-question or this one https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors-when-asking-a-question or this one https://idownvotedbecau.se/noresearch/? — Charlieface, Jul 10 '22 at 13:45
@user355834, assuming that your final sheet is in Excel, the simplest way to solve it is to [transpose (rotate)](https://support.microsoft.com/en-us/office/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744) your sheet. — Mang Hartono, Jul 12 '22 at 06:53
@MangHartono it's very difficult for 5000 rows... it's better to read JSON and then convert it or any other solution except you promote here. thanks so much for your help — , Jul 12 '22 at 07:21
I can build you a python script, can you add detail to your question, by giving a small portion of your JSON? — Mang Hartono, Jul 12 '22 at 08:57
@MangHartono very nice. I attach and upload JSON file for two records. I need to convert to the second picture. I put a bounty on this question also. — , Jul 12 '22 at 10:31
Hi... please put the json as text inside the question itself. — TheMaster, Jul 12 '22 at 10:35
It makes it easy to copy and test in any code... Are you open to using Google sheets? — TheMaster, Jul 12 '22 at 10:48
@TheMaster when I have 5000 records, it's not easily done in Google Sheet. — , Jul 12 '22 at 10:52
5k records shouldn't be a issue. Try [`ImportJSONFromSheet`](https://github.com/bradjasper/ImportJSON) for Google sheets. If you want excel only, try https://stackoverflow.com/questions/6627652/parsing-json-in-excel-vba — TheMaster, Jul 12 '22 at 11:01
@TheMaster when convert this json to CSV or XLSX we have so many rows, we should change it to get attribute for each link in one row and many columns. I try both features and not succeed. — , Jul 12 '22 at 12:07
@TheMaster is it possible to say how import using importjson in google sheet using json file on Google Drive? — , Jul 12 '22 at 14:10
If the file is shared publicly in. Google drive, you can use the URL in importjson. — TheMaster, Jul 12 '22 at 14:14
Thanks @TheMaster for your all helps, not works in google drive. this is my main problem. would you please post it as an answer and show me? — , Jul 12 '22 at 14:20
@user355834, check the solution that I provided and let me know if I can be of any further assistance. — Mang Hartono, Jul 15 '22 at 05:14

TheMaster · Answer 1 · 2022-07-13T02:01:19.263

To convert a json to a excel sheet, there are wide variety of tools. I'll list some common ways to do this:

python

Here we can use openpyxl. The idea is to create a array of rows and .append each row

from openpyxl import Workbook
import json

wb = Workbook()
ws = wb.active
dic = json.loads('''
{
  "https://example.com/product/Detail/1": [
    "METHYLPREDNISOLONE ACETATE",
    "METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL",
    "INJECTION, SUSPENSION",
    "PARENTERAL",
    "البرز دارو",
    "البرز دارو",
    "ریال",
    "ریال",
    "1 AMPULE in 1 CARTON",
    "METHYLPREDNISOLONE ACETATE 40 mg/1mL",
    "1397/12/24",
    "60000",
    "60000",
    "06260152433031",
    "3230858996456396",
    "",
    "H02AB04"
  ],
  "https://example.com/product/Detail/2": [
    "METHYLPREDNISOLONE ACETATE",
    "METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL",
    "INJECTION, SUSPENSION",
    "PARENTERAL",
    "کارخانجات دارو پخش",
    "کارخانجات دارو پخش",
    "ریال",
    "ریال",
    "1 AMPULE in 1 BOX",
    "METHYLPREDNISOLONE ACETATE 40 mg/1mL",
    "1400/09/22",
    "60000",
    "60000",
    "06260132425704",
    "2903849236347759",
    "",
    "H02AB04"
  ]
}
        ''')

for product in dic:
    ws.append([product]+dic[product])
# print([(lambda a: [b.value for b in a])(a) for a in ws['A1':'Z2']])
wb.save('test.xlsx')

node.js

Use sheetsjs. Convert the json to a aoa( array of arrays) using Array.map and write to the file:

const XLSX = require('xlsx'),
  wb = XLSX.utils.book_new(),
  json = /*If necessary use JSON.parse()*/ {
    'https://example.com/product/Detail/1': [
      'METHYLPREDNISOLONE ACETATE',
      'METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL',
      'INJECTION, SUSPENSION',
      'PARENTERAL',
      'البرز دارو',
      'البرز دارو',
      'ریال',
      'ریال',
      '1 AMPULE in 1 CARTON',
      'METHYLPREDNISOLONE ACETATE 40 mg/1mL',
      '1397/12/24',
      '60000',
      '60000',
      '06260152433031',
      '3230858996456396',
      '',
      'H02AB04',
    ],
    'https://example.com/product/Detail/2': [
      'METHYLPREDNISOLONE ACETATE',
      'METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL',
      'INJECTION, SUSPENSION',
      'PARENTERAL',
      'کارخانجات دارو پخش',
      'کارخانجات دارو پخش',
      'ریال',
      'ریال',
      '1 AMPULE in 1 BOX',
      'METHYLPREDNISOLONE ACETATE 40 mg/1mL',
      '1400/09/22',
      '60000',
      '60000',
      '06260132425704',
      '2903849236347759',
      '',
      'H02AB04',
    ],
  },
  data = Object.entries(json).map(([k, v]) => [k, v].flat()),
  ws = XLSX.utils.aoa_to_sheet(data);
XLSX.utils.book_append_sheet(wb, ws);
XLSX.writeFileXLSX(wb, 'test.xlsx');

Live snippet creating downloadable xlsx from given data:

/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/ 
  const json = /*If necessary use JSON.parse()*/ {
      'https://example.com/product/Detail/1': [
        'METHYLPREDNISOLONE ACETATE',
        'METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL',
        'INJECTION, SUSPENSION',
        'PARENTERAL',
        'البرز دارو',
        'البرز دارو',
        'ریال',
        'ریال',
        '1 AMPULE in 1 CARTON',
        'METHYLPREDNISOLONE ACETATE 40 mg/1mL',
        '1397/12/24',
        '60000',
        '60000',
        '06260152433031',
        '3230858996456396',
        '',
        'H02AB04',
      ],
      'https://example.com/product/Detail/2': [
        'METHYLPREDNISOLONE ACETATE',
        'METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL',
        'INJECTION, SUSPENSION',
        'PARENTERAL',
        'کارخانجات دارو پخش',
        'کارخانجات دارو پخش',
        'ریال',
        'ریال',
        '1 AMPULE in 1 BOX',
        'METHYLPREDNISOLONE ACETATE 40 mg/1mL',
        '1400/09/22',
        '60000',
        '60000',
        '06260132425704',
        '2903849236347759',
        '',
        'H02AB04',
      ],
    },
    rows = Object.entries(json).map(([k, v]) => [k, v].flat()),
    wb = XLSX.utils.book_new(),
    ws = XLSX.utils.aoa_to_sheet(rows);
  console.table(rows)
  XLSX.utils.book_append_sheet(wb, ws);
  XLSX.writeFile(wb, 'json.xlsx');

<!-- https://meta.stackoverflow.com/a/375985/ -->    <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js" referrerpolicy="no-referrer"></script>
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js" referrerpolicy="no-referrer"></script>

google-sheets google-apps-script

Use importjson or use array.map technique shown above to create a array of arrays and setValues() the aoa.

@David It will create a direct download on your device. Edit: It should create output now on the console. — TheMaster, Jul 13 '22 at 01:58
No, here with the snippet it doesn't seem to work. I use chrome as browser — David, Jul 13 '22 at 02:06
@David Thanks for testing. Probably due to zero click restrictions... I only tested in Firefox mobile(most restricted browser) at the time and it worked....so I thought it'll work in everywhere else. You're right. Tested in chrome and it didn't offer a download... — TheMaster, Jul 13 '22 at 02:18

score 1 · Answer 2 · answered Jul 12 '22 at 15:20

Here's a simple class in PHP that should do just what you want. It can be modified easily if you need to. It reads the JSON file and creates a CSV file in an uploads folder.

<?php

namespace App;

class CSVFromJson
{
    private $separator = ",";
    private $filename = 'export';
    private $json_file;

    public function __construct(string $file_path, string $filename = null, string $separator = null)
    {
        $this->json_file = $file_path;
        $this->filename = $filename ?? $this->filename;
        $this->separator = $separator ?? $this->separator;
    }

    public function execute()
    {
        $this->process_file();
    }

    private function process_file()
    {
        $json_content = file_get_contents($this->json_file);
        $json_content = json_decode($json_content);
        $csv_content = "";

        foreach ($json_content as $key => $value) {
            $csv_content .= $key . $this->separator;
            
            foreach ($value as $data) $csv_content .= $data . $this->separator;
            $csv_content = substr($csv_content, 0, -1);
            $csv_content .= "\n";
        }

        $csv_content = substr($csv_content, 0, -1);

        if (!file_exists(dirname(__FILE__, 2) . '/uploads')) {
            mkdir(dirname(__FILE__, 2) . '/uploads');
        }

        $file = $this->filename . '_' . time() . '.csv';
        $path = dirname(__FILE__, 2) . '/uploads' . DIRECTORY_SEPARATOR . $file;
        $csv_file = fopen($path,"w") or die('Unable to read file');
        fwrite($csv_file, $csv_content);
        fclose($csv_file);

        return $path;
    }
}

Here's a sample:

use App\CSVFromJson;

$JSONCSVExporter = new CSVFromJson(__DIR__ . '/test.json');
$JSONCSVExporter->execute();

In PHP you could use [fputcsv](https://www.php.net/manual/en/function.fputcsv.php). Also --like [my answer with javascript code](https://stackoverflow.com/a/72955096/1019850)-- this solution doesn't escape and ignores many details. I think that's fine according to the question, as that was about the general logic, but using the code without further efforts isn't advisable. — David, Jul 13 '22 at 01:45
Yes David. This is a really basic solution, and I did say it could be modified as needed. This solves the immediate problem and assumes the data is correctly formatted. Thank you. — Zubair Idris Aweda, Jul 13 '22 at 07:34

Mang Hartono · Answer 3 · 2022-07-13T10:32:52.470

How to solve your problem:

first, save your JSON data as data.json file.
copy the following python script below, and don't forget to import the pandas and openpyxl packages.
run the script.

Python script for your need:

import pandas as pd
from openpyxl import load_workbook

dataJsonFile = 'data.json' #change the file name according to your need
xlsxTempFile = 'temp.xlsx'
xlsxFinal = 'result.xlsx'

#create and load xlsx file from JSON data file
pd.read_json(dataJsonFile).to_excel(xlsxTempFile)
xlsxFile = load_workbook(xlsxTempFile)
sheet = xlsxFile.active

#processing xlsx file
title = [ #change the title according to your need
  'url',
  'x01',
  'x02',
  'x03',
  'x04',
  'x05',
  'x06',
  'x07',
  'x08',
  'x09',
  'x10',
  'x11',
  'x12',
  'x13',
  'x14',
  'x15',
  'x16',
  'x17'
  ]

for i in range(len(title)):
  sheet.cell(i+1, 1).value = title[i] #add title
xlsxFile.save(xlsxTempFile) #save it

df = pd.read_excel(xlsxTempFile, engine='openpyxl')

dfTranspose = df.T #transpose the dataframe
dfTranspose.to_excel(xlsxFinal, header=None) #save it to final xlsx

The result xlsx file:

Script explanation:

using pandas to convert your JSON data to temp.xlsx file.
read the temp.xlsx file using openpyxl and add the details for title.
open the resulting temp.xlsx using pandas again and then transpose it.
write the result to result.xlsx.

Hope this help.

score 1 · Answer 4 · answered Jul 13 '22 at 18:53

In nodeJS you can do

let json = {
    "https://example.com/product/Detail/1": [
      "METHYLPREDNISOLONE ACETATE",
      "METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL",
      "INJECTION, SUSPENSION",
      "PARENTERAL",
      "البرز دارو",
      "البرز دارو",
      "ریال",
      "ریال",
      "1 AMPULE in 1 CARTON",
      "METHYLPREDNISOLONE ACETATE 40 mg/1mL",
      "1397/12/24",
      "60000",
      "60000",
      "06260152433031",
      "3230858996456396",
      "",
      "H02AB04"
    ],
    "https://example.com/product/Detail/2": [
      "METHYLPREDNISOLONE ACETATE",
      "METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL",
      "INJECTION, SUSPENSION",
      "PARENTERAL",
      "کارخانجات دارو پخش",
      "کارخانجات دارو پخش",
      "ریال",
      "ریال",
      "1 AMPULE in 1 BOX",
      "METHYLPREDNISOLONE ACETATE 40 mg/1mL",
      "1400/09/22",
      "60000",
      "60000",
      "06260132425704",
      "2903849236347759",
      "",
      "H02AB04"
    ]
  }
  
let {writeFileSync} = require('fs')



let csv = ''

let keys = Object.keys(json)

keys.forEach((n)=>{
    csv+=n+',';
    json[n].forEach((m)=>{
        csv+=m+','
    })
    csv+='\n'
})
console.log(csv,"written to out.csv")
writeFileSync('out.csv',csv)

score 1 · Answer 5 · answered Jul 16 '22 at 21:03

You can use Excel’s built-in “Power Query Editor”, without coding. Here the steps with a similar, but smaller, JSON file (data.json):

{
  "SET 1": [
    "YYY",
    "XXX",
    "ZZZ"
  ],
  "SET 2": [
    "QQQ",
    "WWW",
    "EEE"
  ]
}

Open the JSON file from “Data > Get Data > From File” menu.
Convert into a table (click the “Into Table” button).
Expand the 2nd column (the button inside the “value” column title) with the “Extract values” option.
Select a custom separator character (not used in the JSON text), and press OK.
Split the “value” column by delimiter.
Select the custom delimiter and the split into columns options. Press OK.
Press the “Close & Load” button at the "Home" ribbon
The results:

The behind script can be found too (there is a code after all). Open the “Power Query Editor” again for the same source (you can double click it on the “Queries & Connections” side pane) and select it from the “View > Advanced Editor” ribbon menu button. The script language is called: “Power Query M language”. This above example steps will give this script:

let
    Source = Json.Document(File.Contents("E:\temp\data.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Value", each Text.Combine(List.Transform(_, Text.From), "~"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Value", Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}})
in
    #"Changed Type"

Marie Stephen Leo · Answer 6 · 2022-07-19T08:18:47.480

You could use the pandas library from Python to easily parse json files into tabular data. Then write the pandas dataframe to an excel file.

First you'd need to install pandas and openpyxl by running the below from the terminal

pip install pandas openpyxl

Then you can use pandas to read the json file from disk and save it to an excel file

import pandas as pd
df = pd.read_json("data.json", orient="index")
df.to_excel("data.xlsx", header=False)

The excel file looks like this: screenshot of excel file

David · Answer 7 · 2022-07-13T01:36:54.997

This is a simple JavaScript solution, you can adjust linebreaks in the line csv += "<br><br>\n";.

For large datasets it is likely possible to append the current line to the result, so that the result is not gathering more than a line, this would keep the required memory small and work on streams too. Needs some more work though.

If you use it on a website, you've to set <meta charset="UTF-8" /> in the header, else your Arabic characters are obstructed.

Warning: the code is working only on the provided example and doesn't consider many things like escaping or objects as values, numbers are just treated as strings. It doesn't work recursively either, nor it does provide a download link. All these features are possible and it's advised to spend more efforts on the code to implement those. Many problems and features are mentioned on this side: How to convert JSON to CSV format and store in a variable

var jsonObj = {
  "https://example.com/product/Detail/1": [
"METHYLPREDNISOLONE ACETATE",
"METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL",
"INJECTION, SUSPENSION",
"PARENTERAL",
"البرز دارو",
"البرز دارو",
"ریال",
"ریال",
"1 AMPULE in 1 CARTON",
"METHYLPREDNISOLONE ACETATE 40 mg/1mL",
"1397/12/24",
"60000",
"60000",
"06260152433031",
"3230858996456396",
"",
"H02AB04"
  ],
  "https://example.com/product/Detail/2": [
"METHYLPREDNISOLONE ACETATE",
"METHYLPREDNISOLONE ACETATE INJECTION, SUSPENSION PARENTERAL 40 mg/1mL",
"INJECTION, SUSPENSION",
"PARENTERAL",
"کارخانجات دارو پخش",
"کارخانجات دارو پخش",
"ریال",
"ریال",
"1 AMPULE in 1 BOX",
"METHYLPREDNISOLONE ACETATE 40 mg/1mL",
"1400/09/22",
"60000",
"60000",
"06260132425704",
"2903849236347759",
"",
"H02AB04"
  ]
}

// console.log(jsonObj);

var properties;
var row = [];
var rows = [];
var n = 0;
var csv = '';
for(url in jsonObj) {
properties = jsonObj[url];
row[0] = url;
csv += '"' + url + '"';
for(index in properties) {
    row[index + 1] = properties[index];
    csv += ', "' + properties[index] + '"';
}
rows[n++] = row;
csv += "<br><br>\n";

}
// console.log(csv);
$('#csv').html(csv);

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js" integrity="sha512-894YE6QWD5I59HgZOGReFYm4dnWc1Qt5NtvYSaNcOP+u1T9qYdvdihz0PPSiiqn/+/3e7Jo4EaG7TubfWGUrMQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
   


<div id="csv">
</div>

Change JSON Content using any programming language or script or by tools to get data in excel format?

7 Answers7