0

I have a running script that convert excel to json format. But my problem is the excel file are more than 100 and each excel has multiple sheets, so I need to edit the file name in my script and run it again every excel file to convert in json format.

My question is, It's possible to run the script once only and convert all excel file inside the folder to .json format.( Json file name should be the same on excel file name)?

This is my script:

df = pd.concat(pd.read_excel('../Excel/Data1.xls', sheet_name=None, header=None))

#set custom culomn
df.columns = ["Menu", "Sub-Menu", "Sub-Menu-1"]
df = pd.DataFrame.to_json(df, orient='records',force_ascii=True,index=True)
json_list= json.loads(df)
def clean_nones(value):
    """
    Recursively remove all None values from dictionaries and lists, and returns
    the result as a new dictionary or list.
    """
    if isinstance(value, list):
        return [clean_nones(x) for x in value if x is not None]
    elif isinstance(value, dict):
        return {
            key: clean_nones(val)
            for key, val in value.items()
            if val is not None
        }
    else:
        return value
with open('../Json/Data1.json', 'w') as json_file:
    json.dump(clean_nones(json_list), json_file, ensure_ascii=False, indent=4)

Folder structure:

|_Excel
|     |_Data1.xls
|     |_Data2.xls
|     |_Data3.xls
|_Json
     |_Data1.json
     |_Data2.json
     |_Data3.json
Adbul
  • 23
  • 8
  • Does this answer your question? [How can I iterate over files in a given directory?](https://stackoverflow.com/questions/10377998/how-can-i-iterate-over-files-in-a-given-directory) – BeRT2me May 18 '22 at 04:04

1 Answers1

0

Try this,

import os
import pandas as pd

excel_folder_path = "C:/Users/Dell/Desktop/test xlsx files/"
json_folder_path = "C:/Users/Dell/Desktop/test json files/"
file_paths = [f"{excel_folder_path}{file_path}" for file_path in os.listdir(path = excel_folder_path)]
xlsx_paths = list(filter(lambda path:True if path.split(".")[-1] == "xlsx" else False, file_paths))

for index, excel_path in enumerate(xlsx_paths):
    pd.read_excel(excel_path).to_json(f"{json_folder_path}test_json{index}.json")

Just change the paths in excel_folder_path and json_folder_path and it should do the job.

Zero
  • 1,541
  • 1
  • 3
  • 13
  • Thank you my friend, I edit this script to fit what I need. Currently codes are working fine the problem is when saving to json file. Json file are not in order so I need for now is to get what are the excel file name should be the same to json. Ex: employee.xls file name and employee.json also to easier to identify in multiple files. – Adbul May 18 '22 at 07:15
  • @Adbul I don't get you – Zero May 18 '22 at 09:41