英文:
How to use Power Query to find a word in a PDF document and pull a table out
问题
我尝试将多个PDF表格导入到PowerQuery中，但这些表格出现在不同的页面上。在一个PDF中，表格可以出现在第5页，而在另一个PDF中，表格可以出现在第10页。在这种情况下，表格的标题是相同的。我如何使用PowerQuery找到这些表格标题并将这些表格导入到Excel中？
感谢您预期的帮助。
英文:
I am trying to import multiple PDF tables into PowerQuery but the table appear on different pages. In one pdf, the table can appear on Page 5 while the other the table can appear on page 10. In this case the table headers are the same. How can I use PowerQuery to find this table header and pull these tables into excel?
Thanks for the anticipated help.
答案1
得分: 1
这似乎对你的三个示例文件起作用
let PullPDF = (variable) => let
Source = Pdf.Tables(File.Contents(variable)),
List = List.Union(List.Transform(Source[Data], each Table.ColumnNames(_))),
# "Expanded Data" = Table.ExpandTableColumn(Source, "Data", List,List) ,
// 假设所有包含单词“Volume”的行以上的数据都是垃圾
Row=Table.RemoveFirstN(# "Expanded Data",List.PositionOf(# "Expanded Data"[Column2],"Volume")),
# "Removed Other Columns" = Table.SelectColumns(Row,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
# "Promoted Headers" = Table.PromoteHeaders(# "Removed Other Columns", [PromoteAllScalars=true]),
// 根据实际列名称和格式更改或删除下一行
# "Changed Type" = Table.TransformColumnTypes(# "Promoted Headers",{{"A3 Unit", type text}, {"Volume", Int64.Type}, {"∆V", Int64.Type}, {"Method", type text}, {"Volume_1", Int64.Type}, {"∆V_2", Int64.Type}, {"Delta#(lf)Volume", Int64.Type}})
in # "Changed Type",
Source2 = Folder.Files("c:\temp\"),
# "Filtered Rows" = Table.SelectRows(Source2, each ([Extension] = ".pdf")),
# "Added Custom" = Table.AddColumn(# "Filtered Rows", "Data", each PullPDF([Folder Path]&[Name])),
List = List.Union(List.Transform(# "Added Custom"[Data], each Table.ColumnNames(_))),
# "Expanded Data" = Table.ExpandTableColumn(# "Added Custom", "Data", List,List)
in # "Expanded Data"
我在另一个网站上查看了一下，因为我感到无聊，但实际上，要求人们登录到另一个网站来帮助你在这个网站上不是最佳选择。
英文:
This seems to work on your three sample files
let PullPDF = (variable)=> let
Source = Pdf.Tables(File.Contents(variable)),
List = List.Union(List.Transform(Source[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", List,List) ,
// assume all data above row containing word Volume is garbage
Row=Table.RemoveFirstN(#"Expanded Data",List.PositionOf(#"Expanded Data"[Column2],"Volume")),
#"Removed Other Columns" = Table.SelectColumns(Row,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
// change or remove next row as needed based on real column names and formats
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"A3 Unit", type text}, {"Volume", Int64.Type}, {"∆V", Int64.Type}, {"Method", type text}, {"Volume_1", Int64.Type}, {"∆V_2", Int64.Type}, {"Delta#(lf)Volume", Int64.Type}})
in #"Changed Type",
Source2 = Folder.Files("c:\temp\"),
#"Filtered Rows" = Table.SelectRows(Source2, each ([Extension] = ".pdf")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Data", each PullPDF([Folder Path]&[Name])),
List = List.Union(List.Transform(#"Added Custom"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", List,List)
in #"Expanded Data"
I looked on the other site since I was bored, but really, requiring people to log into another site to help you on this one is not optimal
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论