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
#1
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

Attached Files

.zip   FOR FORUMS.zip (Size: 25.85 KB / Downloads: 62)
Reply
#2
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)
Reply
#3
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)
Reply
#4
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

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


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

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