数据清洗

Office · 发表于 2024-9-11 11:24

本帖最后由 Office 于 2024-9-11 11:25 编辑

附件中的PDF可以清洗整理成如表格所示吗？

cnmlgb9998 · 发表于 2024-9-11 16:09

let
源 = Pdf.Tables(
File.Contents("C:\Users\飞飞\Desktop\dzfp_20240702132347.pdf"),
[Implementation = "1.3"]
),
Page001 = 源{[Id = "Page001"]}[Data],
Num = List.Select(
Record.ToList(Table.First(Page001)),
each _
<> null
and List.AllTrue(
List.Transform(Text.ToList(_), (x) => List.Contains({"0" .. "9"}, x))
)
){0},
Date = Text.AfterDelimiter(
List.Select(
List.Select(
Table.ToRows(Page001),
each List.AnyTrue(List.Transform(_, (x) => Text.Contains(x, "开票日期")))
){0},
(x) => x <> null
){0},
"："
),
Tab = Table.Skip(Page001, each [Column2] <> "项目名称"),
提升的标题 = Table.PromoteHeaders(Tab, [PromoteAllScalars = true]),
合并的列 = Table.CombineColumns(
提升的标题,
{"Column1", "项目名称", "Column3", "Column4", "Column5", "Column6"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"项目名称.1"
),
合并的列1 = Table.CombineColumns(
合并的列,
{"单", "位"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"单位"
),
合并的列2 = Table.CombineColumns(
Table.TransformColumnTypes(合并的列1, {{"量", type text}}, "zh-CN"),
{"数", "量"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"数量"
),
合并的列3 = Table.CombineColumns(
Table.TransformColumnTypes(合并的列2, {{"Column14", type text}, {"价", type text}}, "zh-CN"),
{"Column12", "单_1", "Column14", "价"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"单价"
),
合并的列4 = Table.CombineColumns(
合并的列3,
{"金", "额"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"金额"
),
合并的列5 = Table.CombineColumns(
Table.TransformColumnTypes(
合并的列4,
{{"Column18", type text}, {"Column19", type text}, {"税率/征收率", type text}},
"zh-CN"
),
{"Column18", "Column19", "税率/征收率"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"税率"
),
合并的列6 = Table.CombineColumns(
Table.TransformColumnTypes(合并的列5, {{"税", type text}}, "zh-CN"),
{"Column21", "税", "额_2"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"税额"
),
自定义2 = Table.FirstN(合并的列6, each [项目名称.1] <> "合计"),
自定义3 = Table.Group(
自定义2,
"项目名称.1",
{
"Data",
each [
a = Table.ToColumns(_),
b = {Num} & {Date} & List.Transform(a, (x) => Text.Combine(x, "")),
c = {"发票号码"} & {"开票日期"} & Table.ColumnNames(_),
d = Table.FromRows({b}, c)
][d]
},
0,
(x, y) => Number.From(Text.Start(y, 1) = "*")
),
自定义4 = Table.Combine(自定义3[Data])
in
自定义4

复制代码

cnmlgb9998 · 发表于 2024-9-11 16:13

let
   源 = Pdf.Tables(
         File.Contents("C:\Users\飞飞\Desktop\dzfp_20240702132347.pdf"),
         [Implementation = "1.3"]
   ),
   Page001 = 源{[Id = "Page001"]}[Data],
   Num = List.Select(
         Record.ToList(Table.First(Page001)),
         each _
               <> null
               and List.AllTrue(
                     List.Transform(Text.ToList(_), (x) => List.Contains({"0" .. "9"}, x))
               )
   ){0},
   Date = Text.AfterDelimiter(
         List.Select(
               List.Select(
                     Table.ToRows(Page001),
                     each List.AnyTrue(List.Transform(_, (x) => Text.Contains(x, "开票日期")))
               ){0},
               (x) => x <> null
         ){0},
         "："
   ),
   Tab = Table.Skip(Page001, each [Column2] <> "项目名称"),
   提升的标题 = Table.PromoteHeaders(Tab, [PromoteAllScalars = true]),
   合并的列 = Table.CombineColumns(
         提升的标题,
         {"Column1", "项目名称", "Column3", "Column4", "Column5", "Column6"},
         Combiner.CombineTextByDelimiter("", QuoteStyle.None),
         "项目名称.1"
   ),
   合并的列1 = Table.CombineColumns(
         合并的列,
         {"单", "位"},
         Combiner.CombineTextByDelimiter("", QuoteStyle.None),
         "单位"
   ),
   合并的列2 = Table.CombineColumns(
         Table.TransformColumnTypes(合并的列1, {{"量", type text}}, "zh-CN"),
         {"数", "量"},
         Combiner.CombineTextByDelimiter("", QuoteStyle.None),
         "数量"
   ),
   合并的列3 = Table.CombineColumns(
         Table.TransformColumnTypes(合并的列2, {{"Column14", type text}, {"价", type text}}, "zh-CN"),
         {"Column12", "单_1", "Column14", "价"},
         Combiner.CombineTextByDelimiter("", QuoteStyle.None),
         "单价"
   ),
   合并的列4 = Table.CombineColumns(
         合并的列3,
         {"金", "额"},
         Combiner.CombineTextByDelimiter("", QuoteStyle.None),
         "金额"
   ),
   合并的列5 = Table.CombineColumns(
         Table.TransformColumnTypes(
               合并的列4,
               {{"Column18", type text}, {"Column19", type text}, {"税率/征收率", type text}},
               "zh-CN"
         ),
         {"Column18", "Column19", "税率/征收率"},
         Combiner.CombineTextByDelimiter("", QuoteStyle.None),
         "税率"
   ),
   合并的列6 = Table.CombineColumns(
         Table.TransformColumnTypes(合并的列5, {{"税", type text}}, "zh-CN"),
         {"Column21", "税", "额_2"},
         Combiner.CombineTextByDelimiter("", QuoteStyle.None),
         "税额"
   ),
   自定义2 = Table.FirstN(合并的列6, each [项目名称.1] <> "合计"),
   自定义3 = Table.Group(
         自定义2,
         "项目名称.1",
         {
               "Data",
               each [
                     a = Table.ToColumns(_),
                     b = {Num} & {Date} & List.Transform(a, (x) => Text.Combine(x, "")),
                     c = {"发票号码"} & {"开票日期"} & Table.ColumnNames(_),
                     d = Table.FromRows({b}, c)
               ][d]
         },
         0,
         (x, y) => Number.From(Text.Start(y, 1) = "*")
   ),
   自定义4 = Table.Combine(自定义3[Data])
in
   自定义4

cnmlgb9998 · 发表于 2024-9-11 16:17

代码要审核。。。。。。。。。。。。。。。

jiffy92010 · 发表于 2024-9-11 23:07

这个解读出来好多坑,有些规格型号的末尾跨行的,跑到项目名称去了.最后一行的项目名称第三行,却把合计两字也给并进去了.变成和合计数值那些同一行.

jiffy92010 · 发表于 2024-9-12 00:10

00JTR00 · 发表于 2024-9-12 10:37

jiffy92010 发表于 2024-9-12 00:10

能否发文件学习一下

jiffy92010 · 发表于 2024-9-12 10:59

00JTR00 发表于 2024-9-12 10:37
能否发文件学习一下

觉得这个文件不具备代表性哈，不规范，坑点多，每个坑点就要一段代码洗掉，换个新的PDF文件，又要重写，比手工录还麻烦。

00JTR00 · 发表于 2024-9-12 11:04

jiffy92010 发表于 2024-9-12 10:59
觉得这个文件不具备代表性哈，不规范，坑点多，每个坑点就要一段代码洗掉，换个新的PDF文件，又要重写， ...

没关系，我对比下调整的代码

Office · 发表于 2024-9-12 11:27

果然有大神现身。@jiffy92010 可否分享一下你的方法？

		自动登录	找回密码
密码			免费注册

[求助] 数据清洗

评分