将Excel文件转换为JSON文件的最佳方法? [英] Best way to convert excel file into a JSON file?

问题描述

我将数据保存在Microsoft Excel文件中.我需要将这些数据转换为Lambda函数可以解析的数据.

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

我认为最好的方法是将excel文件转换为JSON文件(然后我的Lambda函数可以读取和解析它).

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?

要将Excel数据文件转换为JSON文件，我发现了一些方便的在线转换器工具，例如这一个.似乎可行.

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.

但是，该转换器和其他转换器在数据中有换行的任何地方添加\r，在数据中有引号的任何地方添加\. (换行符，尤其是引号需要包含在数据中)

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)

因此，要正确读取JSON文件中的数据，我必须摆脱对原始数据的这些更改.

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?

一旦原始数据被更改(通过添加如我上面提到的\r和\之类的东西)，删除它就变得很麻烦.我可以查找/替换以摆脱更改，但这增加了一些步骤，这些步骤可能会变得非常昂贵.而且使用正则表达式可能会增加效果.

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.

**请注意，我可能需要一种创建实际文档的方法(因此在客户端浏览器中生成数据的程序将无法工作).我希望创建一个实际的文档，然后Lambda可以对其进行分析. **

** 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. **

推荐答案

要从excel工作表创建json，通常我会使用excel CONCAT在excel工作表中准备json，然后将其复制.对于标头来说，这可能不是一个完美的方法，但是对于通常构成json较大部分的键和值，它却能很好地发挥作用.

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

请注意，CHAR(34)代表" 然后您可以将其从所有行的角点向下拖动.

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

这篇关于将Excel文件转换为JSON文件的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！