Python Forum
Trying to generating multiple json files using python script
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Trying to generating multiple json files using python script
Hi everyone. This is my first post here.

I am zero in python. I am trying to create script by myself and I thought chatgpt would help but I failed. Here's what I'm trying to do.

I have 23 values each for productcode, assetcode, and name neatly arranged in an excel file. How do I generate 23 json files?

I want to generate these types of json file for every product code. So eventually I will have 23 json files.

{ "version": 1, "type": "material", "productCode": "MC012", "assetcode": "A019", "name": "Oak Veneer 01", "tags": "", "res": "" }
Every folder name is in the following format.

So e.g.

Every json file should go into its own individual folder. The name of the json file should be same as the "name", i.e. Oak Veneer 01.json in this example.

I copy pasted this code by getting help from chatgpt and made a .py file.

import json
import os
import pandas as pd

# Read data from Excel file
excel_file = "mywork.xlsx"
excel_data = pd.read_excel(excel_file)

# Loop through the data and create JSON files and folders
for index, entry in excel_data.iterrows():
    # Create folder name
    folder_name = f"FTJ_{entry['productCode']}_{entry['assetcode']}_{entry['name'].replace(' ', '')}"
    os.makedirs(folder_name, exist_ok=True)  # Create folder if it doesn't exist

    # Create JSON content
    json_content = {
        "version": 1,
        "type": "material",
        "productCode": entry["productCode"],
        "assetcode": entry["assetcode"],
        "name": entry["name"],
        "tags": "",
        "res": ""

    # Create 23 JSON files with different names
    for i in range(1, 24):
        json_file_path = os.path.join(folder_name, f"{entry['name'].replace(' ', '')}{i}.json")

        # Write JSON content to file
        with open(json_file_path, "w") as json_file:
            json.dump(json_content, json_file, indent=4)

        print(f"Generated JSON file: {json_file_path}")
I am getting the following error.

I am getting this error

Traceback (most recent call last): File "C:\Python312\Lib\site-packages\pandas\core\indexes\", line 3805, in get_loc return self._engine.get_loc(casted_key) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "index.pyx", line 167, in pandas._libs.index.IndexEngine.get_loc File "index.pyx", line 196, in pandas._libs.index.IndexEngine.get_loc File "pandas\\_libs\\hashtable_class_helper.pxi", line 7081, in pandas._libs.hashtable.PyObjectHashTable.get_item File "pandas\\_libs\\hashtable_class_helper.pxi", line 7089, in pandas._libs.hashtable.PyObjectHashTable.get_item KeyError: 'productCode' The above exception was the direct cause of the following exception: Traceback (most recent call last): File "D:\freshaa\", line 12, in <module> folder_name = f"FTJ_{entry['productCode']}_{entry['assetcode']}_{entry['name'].replace(' ', '')}" ~~~~~^^^^^^^^^^^^^^^ File "C:\Python312\Lib\site-packages\pandas\core\", line 1121, in __getitem__ return self._get_value(key) ^^^^^^^^^^^^^^^^^^^^ File "C:\Python312\Lib\site-packages\pandas\core\", line 1237, in _get_value loc = self.index.get_loc(label) ^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Python312\Lib\site-packages\pandas\core\indexes\", line 3812, in get_loc raise KeyError(key) from err KeyError: 'productCode'
What should I do to fix this? I checked multiple times the column title of productCode in my excel file but it is totally fine.

I am attaching the a zip file containing folders, the python file and the excel file

Attached Files

.zip   FOR (Size: 25.85 KB / Downloads: 62)
You can try like this:

import csv 
import json
from pathlib import Path

# save your Excel file as csv
The first 7 column headers should be something like this
version	  type	 productCode	assetcode	name	tags	res
# path to your csv
mycsv = '/home/pedro/myPython/json/csv/products.csv'
#read csv file in as a dictionary and save each dictionary to json
with open(mycsv, encoding='utf-8') as csvf: 
    #load csv file data using csv library's dictionary reader
    csvReader = csv.DictReader(csvf)
    for row in csvReader:
        folder = f'FTJ_{row["productCode"]}_{row["assetcode"]}_{row["name"]}'
        # create the folders if they do not exist
        Path(f'/home/pedro/myPython/json/json/{folder}').mkdir(parents=True, exist_ok=True)
        # the save path
        mypath = Path(f'/home/pedro/myPython/json/json/{folder}/{folder}.json')
        #add this python dict to json array
        with open(mypath, "w") as outfile: 
            json.dump(row, outfile)
Lately, I have been learning about generators, so I thought, "I could do this with a generator or two."

This is the same as above, but using a generator:

import json
from pathlib import Path

# save your Excel file as csv
The first row 7 column headers should be something like this
version	type	productCode	assetcode	name	tags	res

mycsv = '/home/pedro/myPython/json/csv/products.csv'

with open(mycsv) as lines:
    # get rid of the " and the newline character from the csv and split on ,
    column_names = next(lines).replace('"', '').rstrip().split(',')
    mydict = (dict(zip(column_names,t.replace('"', '').rstrip().split(','))) for t in lines)
    for d in mydict:
        folder = f'FTJ_{d["productCode"]}_{d["assetcode"]}_{d["name"]}'
        Path(f'/home/pedro/myPython/json/json/{folder}').mkdir(parents=True, exist_ok=True)
        mypath = Path(f'/home/pedro/myPython/json/json/{folder}/{folder}.json')
        with open(mypath, "w") as outfile: 
            json.dump(d, outfile)
Did you do print(excel_data) to see what DF you get and what are column names?
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Why are your generating multiple json files instead of one json file that contains all the information?

Possibly Related Threads…
Thread Author Replies Views Last Post
  Using a script to open multiple shells? SuchUmami 9 974 Apr-01-2024, 10:04 AM
Last Post: Gribouillis
  python convert multiple files to multiple lists MCL169 6 1,882 Nov-25-2023, 05:31 AM
Last Post: Iqratech
  splitting file into multiple files by searching for string AlphaInc 2 1,167 Jul-01-2023, 10:35 PM
Last Post: Pedroski55
  Is there a *.bat DOS batch script to *.py Python Script converter? pstein 3 3,974 Jun-29-2023, 11:57 AM
Last Post: gologica
Question Need help for a python script to extract information from a list of files lephunghien 6 1,331 Jun-12-2023, 05:40 PM
Last Post: snippsat
  script to calculate data in csv-files ledgreve 0 1,278 May-19-2023, 07:24 AM
Last Post: ledgreve
  Python Script to convert Json to CSV file chvsnarayana 8 2,929 Apr-26-2023, 10:31 PM
Last Post: DeaD_EyE
  Merging multiple csv files with same X,Y,Z in each Auz_Pete 3 1,431 Feb-21-2023, 04:21 AM
Last Post: Auz_Pete
  unittest generates multiple files for each of my test case, how do I change to 1 file zsousa 0 1,086 Feb-15-2023, 05:34 PM
Last Post: zsousa
  Find duplicate files in multiple directories Pavel_47 9 3,790 Dec-27-2022, 04:47 PM
Last Post: deanhystad

Forum Jump:

User Panel Messages

Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020