Скачать или смотреть How to Easily Convert Multiple Tables from Excel into JSON

Learn how to transform Excel tables into JSON format effortlessly using Python and Pandas. This guide breaks down the process step by step.
---
This video is based on the question https://stackoverflow.com/q/69909970/ asked by the user 'Med_Maliari' ( https://stackoverflow.com/u/16591551/ ) and on the answer https://stackoverflow.com/a/69910544/ provided by the user 'Tranbi' ( https://stackoverflow.com/u/13525512/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to convert multiple tables from Excel into JSON

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Easily Convert Multiple Tables from Excel into JSON

When working with data, it's not uncommon to find yourself needing to convert Excel tables into a more flexible format like JSON. This is particularly useful for developers and data analysts who need to work with data structures in web applications or data processing. In this guide, we’ll guide you through the process of converting multiple tables from Excel into the JSON format using Python.

Understanding the Problem

Imagine you have two tables in an Excel file:

Companies Information Table: This contains details about companies including their names, member numbers, authorized capital, and email addresses.

Users Information Table: This lists users linked to these companies, detailing their names, roles, nationalities, and associated member numbers.

Your goal is to transform this data into JSON format where each company's details are associated with their respective users.

Example Tables

Companies Information Table:

titleMember_NoAuth_CapitalEmailKUWAIT ENERGY CO. KSCC100018127000000mansour.aboukhamseen-koc.com.kwSALEM INTERNATIONAL ADVERTISING CO.10002110000NULLPOWER TOUCH GEN. TRAD. & CONT. CO.100026500000NULLUsers Information Table:

namerolenationalityMember_Noمنصور احمد محمد بوخمسينChairman of the board of DirectorsKUWAIT100018محمد عادل التميميVice Chairman and Chief ExecutiveCANADA100018SALEM M. AL-AJMIAuthorized partner with Doc.KUWAIT100021ريم محمد سالم الحريصPartnerKUWAIT100026محمدSpecial Power of AttorneyKUWAIT100026سالمSpecial Power of AttorneyKUWAIT100026The Solution

To effectively convert these tables from Excel to JSON, we can use Python along with the Pandas library. Below, we will walk through the steps to achieve this conversion.

Step 1: Install Pandas

Make sure you have Pandas installed in your Python environment. You can install it using pip:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Import Necessary Libraries

In your Python script, start by importing the required libraries:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Read Excel Files

Next, read the Excel files containing your data. You can have both tables in a single Excel file with multiple sheets or separate files:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Convert Tables to JSON Format

Now, it's time to convert the two DataFrames into a JSON-compliant format. We will first convert the companies' table into a list of dictionaries and then associate each company with its respective users.

[[See Video to Reveal this Text or Code Snippet]]

Step 5: Output the JSON

Finally, we can convert the structured data into JSON format:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of Key Components

to_dict(orient='records'): This converts the DataFrame into a list of dictionaries, where each row is represented as a dictionary.

Data Association: We use a filter on the users' DataFrame to obtain all users that belong to each company based on the Member_No.

ensure_ascii=False: This option helps in preserving non-ASCII characters (e.g., Arabic characters in the user names).

Conclusion

Transforming Excel tables into JSON format can streamline your data processing tasks, and the process with Python's Pandas makes it efficient and straightforward. By following the steps outlined above, you should be able to convert multiple tables seamlessly.

Feel free to adapt the scripts for your own data structure and enjoy the flexibility of using JSON!