ExceltoJSON

Converts Excel file containing json data into a JSON file

Excel Format

Each key will be in the First Column, and each value data will be in the succeeding columns

keys	values
key1	value1
key2	value1
key3

JSON format= { "key1":"value1" , "key2":"value1" , "key3":"" }

What if the value is a list?

Here we will be placing each element in the list(value) on each cell to the right of the key cell

keys	values
key1	value1
key2	value1	value2	value3
key3

JSON format = { "key1":"value1" , "key2":["value1", "value2", "value3"] , "key3":"" }

Sometimes JSON has an empty list as value, we need to tell the program that this key has no values, but the value is a list. Or else the program puts "" instead of [].
So we will be adding a separator (here '_') in the key as a suffix to tell the program that this key is an exception

keys	values
key1	value1
key2	value1	value2	value3
key3_

JSON format = { "key1":"value1" , "key2":["value1", "value2", "value3"] , "key3":[] }

Dealing with a dictionary inside a list as a value

If key3 has 3 other attributes like name,id,address. then the json file will have a dictionary of keys(name,id,address) inside a list as a value. To deal with this we will use a separator and distribute the attribute row wise (key3_name, key3_id, key3_address)

keys	values
key1	value1
key2	value1	value2	value3
key3_name	Rajesh
key3_id	3027
key3_address	Dubai main rd	Dubai bustand	Dubai

JSON format = { "key1":"value1" , "key2":["value1", "value2", "value3"] , "key3":[ { "name":"Rajesh", "id":3027, "address":["Dubai main rd", "Dubai bustand", "Dubai"] } ] }

note:

notice how 3027 is not a string format
Values will be unchanged, As excel supports int, str, and bool. The same DataType will be in JSON

REQUIREMENTS:

Pandas

json