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:

• Python Excel to JSON Conversion - JournalDev
• excel2json-3 · PyPI（Excel to JSON Converter）

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])

• If you do not create the output destination folder (output folder) first, you will get angry (that's right).
• When Excel is output from Tobii software, the sheet name is fixed to Data.

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