现有如下所示文档,其中的每一列(总共9列)算上前置的空白字符共有14个字符,现要求使用Power Query读取该文档并且把数据准确地分为9列。
这个问题的最优解是使用Csv.Document()的等宽分列功能,代码如下:
let Source = Csv.Document( File.Contents("D:\OneDrive\PQ Fans\Basketball\Data.csv"), null, {1, 15, 29, 43, 57, 71, 85, 99, 113, 127} ), RemoveCol = Table.RemoveColumns( Source, {"Column10"} ) in RemoveCol
在没有发现以上方法之前,笔者曾向篮球大哥请教了如何使用正则表达式解答以上问题,今天将与大家分享篮球大哥的巧妙解答。在这之前,让我们先看看使用正则表达式解答的常规方法,代码如下:
let Source = Csv.Document( File.Contents( "D:\OneDrive\PQ Fans\Basketball\Data.csv" ), {"1"}, "#(lf)" ), Add_Col = Table.AddColumn( Source, "Reg_Test", each Web.Page( Text.Combine( { "˂script˃ ", "var s = '", [1], "'; ", "var p = /\d*,?\d*\.?\d+|([a-zA-Z]+\s*){1,3}|\s{14}/g; " , "var r = s.match(p).join('|');", "document.write(r); ", "˂script˃" } ) )[Data]{0}[Children]{0}[Children]{1}[Text]{0}, type text ), RemoveCol = Table.RemoveColumns( Add_Col, {"1"} ), SplitCol = Table.SplitColumn( RemoveCol, "Reg_Test", Splitter.SplitTextByDelimiter("|"), List.Transform( {"1".."9"}, each Text.Combine( { "Column", _ } ) ) ) in SplitCol
以上方法为源数据添加了一计算列,这个计算列中的每一行都调用了一次Web.Page(),每一次调用都会使用\d*,?\d*\.?\d+|([a-zA-Z]+\s*){1,3}|\s{14}这个正则表达式把当前文本的每一列(总共九列)的值匹配出来。匹配出来的9个值实际上是以数组形式存在的,所以可以使用Join()方法以|为分隔符把这些值连接起来。由于这个方法每一行都调用了一次Web.Page(),所以运行的时间会较长,如果数据拓展到10万行,运行时间接近50秒(注:\d*,?\d*\.?\d+用于匹配数字,([a-zA-Z]+\s*){1,3}匹配至少出现一次但不多于3次的英文单词或者字母,\s{14}匹配刚好出现14次的空白字符)。
篮球大哥的方法引入了数组,这样可以使Web.Page()的调用次数减少至一次,代码如下:
let Source = Csv.Document( File.Contents( "D:\OneDrive\PQ Fans\Basketball\Data.csv" ), {"1"}, "#(lf)" ), Json = Web.Page( Text.Combine( { "˂script˃ arr = ", Text.FromBinary( Json.FromValue( Table.Column( Source, "1" ) ) ), "; ", "ptn = /\d*,?\d*\.?\d+|([a-zA-Z]+\s*){1,3}|\s{14}/g; ", "rsl = []; ", "for (i in arr){rsl.push('""' + arr[i].match(ptn).join('|') + '""')}; ", "document.write('{' + rsl + '}');", "˂script˃" } ) )[Data]{0}[Children]{0}[Children]{1}[Text]{0}, Result = Table.FromList( Expression.Evaluate(Json), Splitter.SplitTextByDelimiter("|"), List.Transform( {"1".."9"}, each Text.Combine( { "Column", _ } ) ) ) in Result
以上方法最关键的一步在于使用Text.FromBinary()+Json.FromValue()的组合把源数据转换为以文本形式存在的数组。该数组会用于循环的过程,在每一次循环中都会用match()方法把九个值匹配出来。因为这九个值是以数组形式存在的,所以可以使用Join()方法以|为分隔符把这些值连接起来,之后还需要在最左边与最右边各加两个双引号。最后,连接的结果会通过push方法加入至空数组(即[])中,使用document.write()输出时需要在输出结果左右各加一个大括号,这样Expression.Evaluate()才会把输出的结果识别为Power Query的List。由于以上方法只使用了一次Web.Page(),所以运行时间大幅度减少,如果数据拓展至10万行,运行时间大约为3秒。
最优解还可以更简洁,一步到位,不需要删除列这一步,代码如下:
let
Source =Csv.Document(File.Contents("D:\OneDrive\PQ Fans\Basketball\Data.csv"),9,List.Numbers(1, 10, 14),ExtraValues.Ignore)
in
Source
好方法,谢谢分享
还可以在导入时不拆分,而是直接导入为一列,然后用拆分列功能进行拆分,配合拆分器使用,更具灵活性,能够适应各种不同文档格式。代码如下:
let
Source=Csv.Document(File.Contents("D:\OneDrive\PQ Fans\Basketball\Data.csv")),
用位置分列 = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByRepeatedLengths(14))
in
用位置分列
非常好的思路,谢谢分享
楼上好多大佬,但我主要是来学习正则表达式批量处理的