Power Query处理含多余#(cr)#(lf)的Oracle导出文件转表异常求助

问题背景 #

• Oracle导出的管道分隔文本中，部分日期时间字段含多余#(cr)#(lf)，导致Power Query导入失败
• 无法从数据源端解决问题，只能在Power Query内处理
• 自行编写的脚本先移除所有换行再重新添加，但点击「To Table」按钮时，Power Query无法正确识别所有换行，部分行被合并
• 若将转表前的文本复制到Notepad++保存后重新导入，转表操作则完全正常

原脚本（不含转表步骤） #

let
    Source = Lines.FromBinary(File.Contents("H:\My Documents\test.csv")),
    Listtotext = Text.Combine(Source),
    ReplacedValue2 = Text.Replace((Listtotext),"#(cr)#(lf)", ""),
    SplitText = Text.Split(ReplacedValue2 ," "),
    ListNonBlankValues = List.Select(SplitText,each _<> ""),
    TextJoinList2 = Text.Combine(ListNonBlankValues," "),
    ReplacedValue3 = Text.Replace((TextJoinList2),"|Is Final?", "|Is Final?#(cr)#(lf)"),
    ReplacedValue4 = Text.Replace((ReplacedValue3),"|Y|", "|Y|#(cr)#(lf)"),
    ReplacedValue5 = Text.Replace((ReplacedValue4), "#(cr)#(lf) ", "#(cr)#(lf)")
in
    ReplacedValue5

问题原因 #

原脚本最终返回的是单个文本值，而「To Table」按钮默认针对列表格式的行数据进行处理。直接对文本执行转表操作时，Power Query的换行识别逻辑存在偏差；而复制到外部文件再导入时，Lines.FromBinary会自动将文本按换行拆分成标准的行列表，因此转表正常。

修正后的脚本 #

let
    Source = Lines.FromBinary(File.Contents("H:\My Documents\test.csv")),
    Listtotext = Text.Combine(Source),
    ReplacedValue2 = Text.Replace(Listtotext,"#(cr)#(lf)", ""),
    SplitText = Text.Split(ReplacedValue2 ," "),
    ListNonBlankValues = List.Select(SplitText,each _ <> ""),
    TextJoinList2 = Text.Combine(ListNonBlankValues," "),
    ReplacedValue3 = Text.Replace(TextJoinList2,"|Is Final?", "|Is Final?#(cr)#(lf)"),
    ReplacedValue4 = Text.Replace(ReplacedValue3,"|Y|", "|Y|#(cr)#(lf)"),
    ReplacedValue5 = Text.Replace(ReplacedValue4, "#(cr)#(lf) ", "#(cr)#(lf)"),
    // 将处理后的文本按换行拆分为行列表
    SplitToLines = Text.Split(ReplacedValue5, "#(cr)#(lf)"),
    // 过滤空行，避免转表异常
    FilteredLines = List.Select(SplitToLines, each _ <> ""),
    // 直接转表，指定管道符为分隔符
    ToTable = Table.FromList(FilteredLines, Splitter.SplitTextByDelimiter("|"), null, null, ExtraValues.Error)
in
    ToTable

关键修改说明 #

• 新增SplitToLines步骤：手动将处理后的文本按#(cr)#(lf)拆分成列表，让Power Query明确识别每行数据
• 新增FilteredLines步骤：过滤拆分后可能出现的空行，避免转表时产生无效行
• 用Table.FromList替代手动点击「To Table」：直接指定分隔符为管道符，同时通过ExtraValues.Error配置异常值处理，便于排查数据问题

内容的提问来源于stack exchange，提问作者grasshopper