Trying to generating multiple json files using python script

[dzgn989]

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.

Output:
{
  "version": 1,
  "type": "material",
  "productCode": "MC012",
  "assetcode": "A019",
  "name": "Oak Veneer 01",
  "tags": "",
  "res": ""
}

Every folder name is in the following format.
FTJ_productCode_assetcode_name

So e.g.
FTJ_MC012_A019_OakVeneer01

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

Error:
Traceback (most recent call last):
  File "C:\Python312\Lib\site-packages\pandas\core\indexes\base.py", 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\COOLTEXTAGEN.py", line 12, in <module>
    folder_name = f"FTJ_{entry['productCode']}_{entry['assetcode']}_{entry['name'].replace(' ', '')}"
                         ~~~~~^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\pandas\core\series.py", line 1121, in __getitem__
    return self._get_value(key)
           ^^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\pandas\core\series.py", line 1237, in _get_value
    loc = self.index.get_loc(label)
          ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Python312\Lib\site-packages\pandas\core\indexes\base.py", 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

[Pedroski55]

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"]}'
        print(row)
        print(folder)
        # 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)

[Pedroski55]

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:
        print(d)
        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)

[buran]

Did you do print(excel_data) to see what DF you get and what are column names?

[deanhystad]

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