Batch conversion of Excel files to JSON [Python]

Note: This is a note for Python beginners.

I converted xlsx to JSON using a plugin called ʻexcel2json` in Python.

ʻExcel2json` has limited functionality, just convert Excel data to JSON. Since the document only describes how to convert, there seems to be no particular setting.

As you can see from the reference site, there was a way to use Pandas, but this time I wanted the conversion method to be good, so I tried using ʻexcel2json`. It seems that integers are also converted to floating point numbers. Please also visit the following sites.

reference:

import excel2json

xlsx_path = 'C:/Users/username/Documents/xlsx_files/hoge.xlsx'
excel2json.convert_from_file(xlsx_path)
# -->  C:/Users/username/Documents/xlsx_files/Sheet1.json is output

The Excel sheet name becomes the output file name as it is. I wanted to be honest with the output file name specification. This specification may only be used for limited purposes ...

Batch conversion of files !!

I wanted to be able to export an Excel file from a line-of-sight analysis software called Tobii and analyze the line-of-sight data with my own program. So I wanted to convert multiple Excel files output from the software to JSON all at once. For the time being, I also put the code for simultaneous conversion.

When I loop and convert multiple files, I always try to overwrite the JSON file created (same name), so I have to rename it every time. If there are multiple sheets, Sheet2.json will appear, so you need to take measures. In my case, there was only one sheet named Data, which is enough for the specification.

import excel2json
import pathlib
import os
from halo import Halo
from shutil import move

spinner = Halo(spinner='dots')  #Round and round of the loading screen

path = 'C:/Users/username/Documents/xlsx_files'    #path containing xlsx files
xlsx_files = list(pathlib.Path(path).glob('*.xlsx'))  #Make a list of xlsx files

for i in xlsx_files:
  xlsx_path = '%s/%s' % (path, i.name)  #xlsx file path and name
  json_path = '%s/output/%s.json' % (path, i.name[:-5])  #Output destination path of json file

  if os.path.exists(json_path):  #Skip if you already have a json file
    print('Skip to %s' % i.name)
  else:
    try:
      spinner.start('Converting: %s' % i.name)
      excel2json.convert_from_file(xlsx_path)  #Convert to json
    except Exception as inst:  #Conversion failure
      spinner.fail(inst)
    else:  #Successful conversion
      move('%s/Data.json' % path, json_path)  #Data.Rename and move json
      spinner.succeed('Success: %s.json' % i.name[:-5])

reference: -How to get only the file name in the folder with python -Python memo: Show spinner (in process) in terminal with halo

Recommended Posts

Batch conversion of Excel files to JSON [Python]
Convert Excel data to JSON with python
Convert "number" of excel date to python datetime
Summary of how to import files in Python 3
Convert json to excel
[Python] I want to merge Excel files anyway (pandas.merge)
[Python] I tried to get Json of squid ring 2
[Introduction to Python] How to parse JSON
MP3 to WAV conversion with Python
Handle Excel CSV files with Python
Summary of go json conversion behavior
Python logging and dump to json
Easy to see difference of json
Conversion of string <-> date (date, datetime) in Python
[Python] Summary of how to use pandas
Comparison of Japanese conversion module in Python3
I want to batch convert the result of "string" .split () in Python
Reading and writing JSON files with Python
Try to operate Excel using Python (Xlwings)
[Easy Python] Reading Excel files with openpyxl
Read and write JSON files in Python
I want to color a part of an Excel string in Python
Python #JSON
Convert HEIC files to PNG files with Python
[Easy Python] Reading Excel files with pandas
[Bash] Redirection of multiple lines to multiple files
Summary of how to use Python list
[Python2.7] Summary of how to use subprocess
Why you should use urlopen instead of urlretrieve to download Python files
[Python] Add comments to standard input files
[Introduction to Data Scientists] Basics of Python ♬
[Question] How to use plot_surface of python
How to find out what kind of files are stored in S3 in Python
About the handling of ZIP files including Japanese files when upgrading from Python2 to Python3
How to get a list of files in the same directory with python
Edit Excel from Python to create a PivotTable
[Introduction to Udemy Python 3 + Application] 26. Copy of dictionary
Convert FBX files to ASCII <-> BINARY in Python
[Python] How to use two types of type ()
Just add the python array to the json data
Import Excel file from Python (register to DB)
[Introduction to Udemy Python 3 + Application] 19. Copy of list
How to create a JSON file in Python
[Python] How to read excel file with pandas
How to generate a Python object from JSON
Summary of how to use MNIST in Python
Reading and writing CSV and JSON files in Python
[Introduction to Python] How to handle JSON format data
Convert UTF-8 CSV files to read in Excel
How to specify attributes with Mock of python
Excel X Python The fastest way to work
How to get dictionary type elements of Python 2.7
Simple comparison of Python libraries that operate Excel
Batch convert PSD files in directory to PDF
Upload files to Google Drive with Lambda (Python)
Speed: Add element to end of Python array
Summary of studying Python to use AWS Lambda
List of Python code to move and remember
Summary of Excel operations using OpenPyXL in Python
[Introduction to Python] Basic usage of lambda expressions
Updated to Python 2.7.9