Convert excel to json python

Convert EXCEL to JSON and JSON to EXCEL in Python

Excel table data can be represented as an array of objects in the form of JSON. Each object represents a row in the table. JSON (JavaScript Object Notation) is the most used structured data exchange format nowadays. JSON data is a way of representing objects or arrays. It is easy to read and parse, and even with Excel. If you want to transfer the tabular data or store the structured data into tabular form, it requires converting formats from EXCEL to JSON and JSON to EXCEL. In this article, i will demonstrate how to convert EXCEL to JSON and JSON to EXCEL in Python.

The following topics are covered in this article:

Python EXCEL to JSON and JSON to EXCEL API — Installation#

In order to convert a JSON file to an Excel sheet and an Excel file to JSON format, GroupDocs.Conversion provides APIs for conversion process. In this article, we will use the Python SDK of GroupDocs.Conversion Cloud API for converting JSON into XLSX file and Excel file to JSON format. It is a feature-rich, platform independent documents and images conversion Python library. It provides quick conversion of images and documents of any supported file format in high-quality like word-processing documents, spreadsheets, presentations, images, and many more.

You can install Python conversion SDK into your Python application using the following command in the console:

pip install groupdocs_conversion_cloud 

Please get your Client ID and Secret from the dashboard before following the mentioned steps. Once you have your ID and secret, add code in your python application:

How to Convert EXCEL to JSON using Python#

You can convert excel to json file by following the simple steps mentioned below:

Upload the File#

Firstly, upload the excel file to the cloud using the code example given below:

As a result, the uploaded excel file will be available in the files section of your dashboard on the cloud.

Excel to JSON Conversion in Python#

The following steps allow converting the excel files to json format programmatically in the Python applications.

• Firstly, create an instance of the ConvertApi
• Then, create convert settings instance using ConvertSettings
• Next, provide the files storage name
• Set input excel file path and output format as “json”
• Then, set the load_options and output_path
• After that, create the ConvertDocumentRequest with ConvertSettings as an argument
• Finally, convert pdf to excel by calling the convert_document() with ConvertDocumentRequest

The following code sample shows how to change Excel to JSON format using Python:

Finally, the above code sample will save the JSON file on the cloud. This is the best way to convert xlsx to json file.

Download the Converted File#

The above code sample will save the converted excel to json file on the cloud. You can download it using the following code sample:

How to Convert JSON to EXCEL using Python#

You can easily convert JSON files to Excel files (.xlsx) using Python SDK. The following steps listed are for converting the JSON file to Excel file in your Python applications.

• First, create an instance of the ConvertApi
• Then, create convert settings instance using ConvertSettings
• Next, provide the your cloud storage name
• Set input JSON file path and output format as “excel”
• Then, set the output file path
• Now, create the ConvertDocumentRequest with ConvertSettings as an argument
• Finally, convert pdf to excel code online by calling the convert_document() method with ConvertDocumentRequest

The following code sample shows how to turn JSON format to excel file using Python:

Finally, the above code sample will save the Excel file on the cloud.

Free Online JSON and Excel Converter#

How to Convert Excel to JSON Array Online? Excel To JSON Converter converts excel file to JSON online. There is a free online Excel to JSON converter and online JSON to Excel converter free. It has been developed using the Groupdocs.Conversion Cloud REST APIs.

Conclusion#

To conclude, you learned how to convert the JSON files to excel format and also the conversion of excel files to JSON format programmatically . Now you understand:

• how to convert XlSX to JSON programmatically;
• programmatically upload files and download converted files;
• how to convert JSON to XLSX using python ;

In addition, you can learn more about GroupDocs.Conversion file format conversion API using the documentation, or by examples available on GitHub. We also provide an API Reference section that lets you visualize and interact with our APIs directly through the browser.

Ask a question#

You can ask your queries about how to convert Excel to JSON and JSON to Excel format, via our Free Support Forum

See Also#

Источник

Convert Excel to JSON using Python

In this tutorial, we are going to see how to convert an Excel file to JSON format using Python. pandas is a Python library used to accomplish this task. The pandas library is used for data analytics.

First of all, we need to install the Pandas library:

Now, import the libraries to use in the code.

import pandas import json

Note: json is a built-in module in Python so no need to install it again.

Using the read_excel() function from the pandas library read the excel file. Pass the file location path as an argument. sheet_name is an attribute used to select the excel sheet from the workbook. [The excel file must have the extension .xlsx or .xls ]

Suppose we have an IRIS dataset.

Excel = pandas.read_excel('Jupyter\IRIS.xlsx', sheet_name='Worksheet')

to_json( ) function

to_json() is a function from the pandas library used to convert the object to JSON string.
orient is a parameter of the function which specifies the JSON string format. There are various JSON string formats eg. values, records, index, table, split, etc. ‘index’ is the default JSON string format.

Json = Excel.to_json(orient='records')

Finally, print the converted JSON string.

Now save the created JSON file.
Open the ‘JSON.json’ file in write mode to add content to it.
Note: open function creates the new file at a given location and opens it in specified read or write mode.

out_file = open("F:\Aakanksha\Jupyter\JSON.json", "w")

dump( ) function

dump() function converts the Python object into a desired JSON object. You must pass the JSON string and output file you have opened before.
indent is a function parameter that improves the readability of JSON files.

json.dump(Json, out_file, indent = 6)

Now close the opened file.

Источник

Преобразование Excel в JSON в Python

Есть много способов преобразовать файл Excel в данные JSON в Python. В этом руководстве мы рассмотрим два модуля python для преобразования файлов Excel в JSON:

Преобразование файла Excel в файлы JSON с использованием модуля excel2json-3

Это очень простой модуль для преобразования файлов Excel в файлы JSON. Содержимое таблицы Excel преобразуется в строку JSON и сохраняется в файле.

Имя файлов происходит от имен листов Excel. Итак, если есть два листа с именами «Numbers» и «Cars», файлы JSON будут называться Numbers.json и Cars.json соответственно.

Модуль поддерживает преобразование файлов в форматах .xls и .xlsx. Мы можем прочитать файл Excel из файловой системы, а также URL-адрес.

Мы можем установить этот модуль с помощью команды PIP.

В нашем примере я создал файл Excel с именем «records.xlsx», имеющий три листа.

Вот сценарий для преобразования этого файла Excel в файлы JSON.

import excel2json excel2json.convert_from_file('records.xlsx')

Скрипт создает три файла JSON.

Если вам нужно прочитать файл Excel по URL-адресу, используйте функцию convert_from_url().

Ограничения модуля excel2json-3

• Плагин имеет очень ограниченные возможности.
• Нет возможности пропустить какой-либо лист, строки или столбцы. Это затрудняет использование файлов Excel большего размера.
• JSON сохраняется в файлы. В большинстве случаев мы хотим преобразовать в JSON и использовать его в нашей программе, а не сохранять в виде файла.
• Целые числа преобразуются в числа с плавающей запятой.

Преобразование листа Excel в строку JSON с помощью модуля Pandas

Модуль Pandas предоставляет функции для чтения таблиц Excel в объект DataFrame. Есть много опций для указания заголовков, чтения определенных столбцов, пропуска строк и т.д.

Мы можем использовать функцию to_json() для преобразования объекта DataFrame в строку JSON. Давайте посмотрим на простой пример, чтобы прочитать лист «Сотрудники» и преобразовать его в строку JSON.

import pandas excel_data_df = pandas.read_excel('records.xlsx', sheet_name='Employees') json_str = excel_data_df.to_json() print('Excel Sheet to JSON:\n', json_str)

Excel Sheet to JSON: ,"EmpName":,"EmpRole":>

Итак, данные JSON создаются с ориентацией столбцов. Если вы хотите создать строку JSON с построчной ориентацией, передайте значение параметра «orient» как «records».

json_str = excel_data_df.to_json(orient='records')

Заключение

Если у вас есть простой и хорошо структурированный файл Excel, и вы хотите преобразовать его в файлы JSON, используйте модуль excel2json-3. Но если вам нужен больший контроль над тем, как данные Excel читаются и конвертируются в строку JSON, используйте модуль pandas.

Источник

Python Excel to JSON Conversion

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

Converting Excel File to JSON Files using excel2json-3 Module

It’s a very simple module to convert excel files to JSON files. The contents from the excel sheet are converted to JSON string and saved in a file. The name of the files is derived from the excel sheet names. So, if there are two sheets with the name “Numbers” and “Cars”, the JSON files will be named as Numbers.json and Cars.json respectively. The module supports both .xls and .xlsx file formats for conversion. We can read the excel file from the file system as well as the URL. We can install this module using the PIP command.

For our example, I have created an excel file named “records.xlsx” having three sheets. Here is the script to convert this excel file to JSON files.

import excel2json excel2json.convert_from_file('records.xlsx') 

The script creates three JSON files. Employees.json

Numbers.json

If you have to read the excel file from a URL, use convert_from_url() function.

Limitations of excel2json-3 module

• The plugin has very limited features.
• There are no options to skip any sheet, rows, and columns. This makes it hard to use with bigger excel files.
• The JSON is saved into files. Most of the times, we want to convert to JSON and use it in our program rather than saving it as a file.
• The integers are getting converted to the floating point numbers.

Converting Excel Sheet to JSON String using Pandas Module

Pandas module provides functions to read excel sheets into DataFrame object. There are many options to specify headers, read specific columns, skip rows, etc. You can read more about it at Pandas read_excel() – Reading Excel File in Python. We can use the to_json() function to convert the DataFrame object to JSON string. Let’s look at a simple example to read the “Employees” sheet and convert it to JSON string.

import pandas excel_data_df = pandas.read_excel('records.xlsx', sheet_name='Employees') json_str = excel_data_df.to_json() print('Excel Sheet to JSON:\n', json_str) 

Excel Sheet to JSON: ,"EmpName":,"EmpRole":> 

So, the JSON data is created with the orientation of columns. If you want to create the JSON string with row-wise orientation, pass the “orient” parameter value as “records”.

json_str = excel_data_df.to_json(orient='records') 

Conclusion

If you have a simple and well-structured excel file and you want to convert it to JSON files, use the excel2json-3 module. But, if you want more control on the way the excel data is read and converted to JSON string, use the pandas’ module.

References

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Источник