My first ‘baby step’ in automating tasks in Python for my Watson Assistant Project

I wrote my own Exel to JSON file converter!

For my current project using IBM Watson Assistant, I needed to import a big number of JSON files containing dialogs/intents/keywords… into my WA instance.

First I tried to find some free apps or websites to do this task for me, but suddenly I wanted to have some pleasure in doing it myself. I figured out that many examples are written in Python and decided to write my own Python code for this task (I’m not a Python developer, never 😆).

So I started copying/pasting/adapting code from different sites, and guess what; it worked 👍😅.

Hereafter is my basic code which works great for me. I passed 2 hours copying/pasting/adapting/debugging (including the installation of PyCharm community/free edition and doing pip installations) and voilà!

import os
import jpype

path = "/mypath"
fname = []
excelCounter = 0
arrayExtension = ['.xlsx','.xls','.xlsm']

jpype.startJVM()

def convert2json(arg1, arg2):
    from asposecells.api import Workbook
    arg1 = path + '/' + arg1
    workbook = Workbook(arg1+arg2)
    jsonfile = arg1 + '.json'
    workbook.save (jsonfile)
    print("JSON file created = %s" % jsonfile)



for root, d_names, f_names in os.walk(path):
    for f in f_names:
       fname.append(os.path.join(root, f))
       split_tup = os.path.splitext(f)
       file_name = split_tup[0]
       file_extension = split_tup[1]
       if file_extension in arrayExtension:
          excelCounter = excelCounter + 1
          print("File to be converted = %s" % f)
          print("excelCounter = %s" % excelCounter)
          convert2json(file_name, file_extension)

jpype.shutdownJVM()

/mypath/Dev/PycharmProjects/exportExcel2JSON/xxxprj/bin/python /mypath/PycharmProjects/exportExcel2JSON/main.py 

File to be converted = Current-Intent-Entity-Training3.xls
excelCounter = 1
JSON file created = /maypath/Current-Intent-Entity-Training3.json
File to be converted = Current-Intent-Entity-Training.xlsx
excelCounter = 2
JSON file created = /maypath/Current-Intent-Entity-Training.json
File to be converted = Current-Intent-Entity-Training2.xlsx
excelCounter = 3
JSON file created = /maypath/Current-Intent-Entity-Training2.json

Process finished with exit code 0

My takeaways;

1. This is so far from being perfect, I know, I’ll work on my work 😏
2. there are lots of great and useful libraries for Python
3. Python is great for string and file operations (and for sure other stuff, I’m talking about my very basic level)
4. I’ll do more of this in the future 👍👊😆💪