Best way to convert excel file into a JSON file?

Question

I have data saved in a Microsoft excel file. I need to turn that data into something that a Lambda function can parse.

I think the best way to do this is to convert the excel file into a JSON file (and then my Lambda function can read and parse it).

What's the best way to do this?

To convert the excel data file into a JSON file, I have found some handy online converter tools, like this one. It seems to work.

However, that converter and others add in \r wherever there are line breaks in the data, and \ wherever there are quotes in the data. (the line breaks and especially quotes need to be in the data)

So to properly read the data in the JSON file, I have to then get rid of these changes to the raw data.

Is there another way to do this? Such as a converter that does not change the raw data in this way? Or some method other than a converter?

Once the raw data has been changed (by adding in stuff like \r and \ like I mention above), it becomes cumbersome to remove it. I can do a find/replace to get rid of the changes, but that adds steps that can become costly time wise. And using regex could add performance hits.

**EDIT: Note that I probably need a method that creates an actual document (so a program that produces the data in a client browser would not work). I am looking to create an actual document that my Lambda can then analyze. **

Answer 1

To create a json from excel sheet, I usually prepare the json in excel sheet using excel CONCAT and then copy it. It may not be a perfect way for headers , however it works well for keys and values which generally make up a bigger portion of json.

=CONCAT(CHAR(34),A2,CHAR(34),":",CHAR(34),B2,CHAR(34))

note that CHAR(34) is stands for " and you can drag it to down from corner for all the rows.