如何在Microsoft Excel或Google Sheets中从JSON或CSV字符串中解析出单个值？

Question

我将拥有数百个单元格，它们将使用一个web服务调用来获取股票数据。然而，数据仅以JSON或CSV格式返回。使用以下示例，如何从每个动态JSON或CSV字符串中仅获取"uHigh"值，知道该数字值的长度可能每次都不同？

[{"date":"2020-01-22","uClose":1485.95,"uOpen":1491,"uHigh":1503.21,"uLow":1484.93,"uVolume":1610846,"close":1485.95,"open":1491,"high":1503.21,"low":1484.93,"volume":1610846,"change":1.55,"changePercent":0.1044,"label":"Jan 22","changeOverTime":0.001044}]

或者我可以解析诸如CSV之类的文件。

"date,uClose,uOpen,uHigh,uLow,uVolume,close,open,high,low,volume,change,changePercent,label,changeOverTime,symbol
2020-01-22,1485.95,1491,1503.21,1484.93,1610846,1485.95,1491,1503.21,1484.93,1610846,1.55,0.1044,Jan 22,0.001044,GOOG"

据我所知，Excel 提供了 FILTERXML 方法，但没有提供 JSON 或 CSV 的方法。那么，在 "=something-here" 单元格中我该输入什么来解析出只有 uHigh 值的内容呢？

如果需要，我可以使用 Google Sheets 进行操作。

- stackonfire

---

你是在使用 Sheets 还是 Excel？你有一个 excel-vba 标签，使用 JSON 解析器解析 JSON 将会很简单。然后你的代码行将会是这样的：myuHigh =JSON(1)("uHigh") 但是 VBA 在 Sheets 中不起作用。 - Ron Rosenfeld

Answer 1

对于Google Sheets

如果你想要从JSON中提取数据 在Google Sheets中，你可以使用像这样的REGEXEXTRACT：

• 将JSON放入单元格A1
• 将需要查找的键放在单元格B1
• 将此公式放在单元格C1： =REGEXEXTRACT(A1, """" & B1 & """ *: *(.*?),")

你可以通过更改单元格B1中的键来测试它。

如果你想要从CSV中提取数据，你可以使用如下公式：

• 将CSV放入单元格A1
• 将需要查找的键放在单元格B1
• 将此公式放在单元格C1：=VLOOKUP(B1, TRANSPOSE(ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(A1, char(10))), ","))), 2, FALSE)

你可以通过更改单元格B1中的键来测试它。

免责声明：这两种方法仅适用于JSON/CSV是有效/标准格式的情况。以下几点需要注意:

• 如果JSON的值被引号引起来并且包含逗号，那么这个方法将会失败。例如: {"key" : "value, value"}.
• 如果CSV中使用的字段分隔符逗号也用于键/值对，则此方法将会失败。

对于Excel

如果你想要从JSON中提取数据 在Excel中，你可以使用SEARCH、FIND和MID的组合，像这样：

• 将JSON放入单元格A1
• 将需要查找的键放在单元格B1
• 将此公式放在单元格C1：=TRIM(MID(A2,FIND(":", A2, FIND(""""&B2&"""", A2))+1,SEARCH(",",A2,FIND(":", A2, FIND(""""&B2&"""", A2)))-FIND(":", A2, FIND(""""&B2&"""", A2))-1))

- IMTheNachoMan

---

无法用于JSON。
A1中的JSON：[{"value":"abc","type":"COOL"}]。
B1中的值：value。
C1中的公式：=REGEXEXTRACT(A1, """" & B1 & """ *: *(.*?),")。 C1显示#NAME?，当我点击警告时，它显示“无效名称错误”。 - Sumit

你需要对Excel进行哪些自定义设置才能使其正常工作？我是否需要添加一些脚本？因为它对我来说并不是开箱即用的：https://imgur.com/a/ibHV2pz - Sumit

啊，我明白了。在你的回答中提到这一点可能是值得的。谢谢！ :) - Sumit

我添加了一些在Excel中应该有效的内容。 在我的测试中它能够正常工作。 - IMTheNachoMan

谢谢你为我节省了很多工作。这个在Excel中运行得很好。 - teemran

Answer 2

提取“uHigh”之后的值

将JSON格式类型数据放入A2

在B2中输入公式：

=LOOKUP(9^9,0+MID(A2,SEARCH("uHigh",A2)+7,ROW($1:$99)))

- bosco_yip

---

我认为你的意思是将JSON格式类型的数据放入A2中--你的B2函数引用了A2而不是A1。 - IMTheNachoMan

Answer 3

我学到了可以在CSV字符串上使用Split，然后通过数字从结果字符串数组中提取所需的项。另一个人已经将其功能化，如下所示：

查找逗号分隔列表中的第n个项目

当然，仍然希望能够在一个更简单的调用中完成它。

- stackonfire