将JSON数组导入到Excel中

发布于 2025-02-05 11:54:16 字数 488 浏览 1 评论 0原文

我正在尝试将带有以下格式的JSON文件导入Excel:

[
    [1,2,3,4],
    [5,6,7,8]
]

我想获得一个带有2行和4列的电子表格,其中每一行包含内部数组的内容作为单独的列值,例如

A列B列B列C列D
1234
5678

尽管这似乎是一个容易解决的问题,但我似乎找不到正确的PowerQuery语法,或者找到涵盖此情况的现有答案。我可以轻松地作为具有8个值的单列导入,但似乎无法将内部数组分为单独的列。

I'm trying to import a JSON file with the following format into Excel:

[
    [1,2,3,4],
    [5,6,7,8]
]

I want to get a spreadsheet with 2 rows and 4 columns, where each row contains the contents of the inner array as separate column values, e.g.

Column AColumn BColumn CColumn D
1234
5678

Although this would seem to be an easy problem to solve, I can't seem to find the right PowerQuery syntax, or locate an existing answer that covers this scenario. I can easily import as a single column with 8 values, but can't seem to split the inner array into separate columns.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

愁以何悠 2025-02-12 11:54:17

假设JSON

[
    [1,2,3,4],
    [5,6,7,8]
]

在PowerQuery中看起来像是此代码

let Source = Json.Document(File.Contents("C:\temp\j.json")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Text.Combine(List.Transform([Column1], each Text.From(_)),",")),
ColumnTitles = List.Transform({1 .. List.Max(Table.AddColumn(#"Added Custom", "count", each List.Count(Text.PositionOfAny([Custom],{","},Occurrence.All))+1)[count])}, each "Column." & Text.From(_)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ColumnTitles),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1"})
in #"Removed Columns"

生成

”“在此处输入图像说明”

它将JSON转换为列表列表,转换为列表表,将列表扩展为使用逗号的文本,然后将其扩展到列中通过计算逗号的最大数量,在动态创建新列的列名之后

Assuming the JSON looks like

[
    [1,2,3,4],
    [5,6,7,8]
]

then this code in Powerquery

let Source = Json.Document(File.Contents("C:\temp\j.json")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Text.Combine(List.Transform([Column1], each Text.From(_)),",")),
ColumnTitles = List.Transform({1 .. List.Max(Table.AddColumn(#"Added Custom", "count", each List.Count(Text.PositionOfAny([Custom],{","},Occurrence.All))+1)[count])}, each "Column." & Text.From(_)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ColumnTitles),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1"})
in #"Removed Columns"

generates

enter image description here

It converts the JSON to a list of lists, converts to a table of lists, expands the list to be text with commas, then expands that into columns after dynamically creating column names for the new columns by counting the max number of commas

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文