Forum Discussion
Solved
Removing nonbreaking space and carriage return from pipe delimited text file
Hello! I have a pipe-delimited text file that has non-breaking spaces followed by carriage returns within some of the address fields. When I try to import this via Power Query, it reads those li...
I think something like this will work (though you may need to adjust the exact new line and space characters):
let Source = Lines.FromBinary(File.Contents("C:\Users\aolson\Downloads\TestCSV.csv")), ReplaceText = Text.Replace(Lines.ToText(Source)," #(cr)#(lf)", " "), TextToList = List.RemoveItems(Text.Split(ReplaceText, "#(cr)#(lf)"), {""}), ToTable = Table.FromList(TextToList, Splitter.SplitTextByDelimiter("|")) in ToTable
AlexisOlson
Super User
Super UserI think something like this will work (though you may need to adjust the exact new line and space characters):
let
Source = Lines.FromBinary(File.Contents("C:\Users\aolson\Downloads\TestCSV.csv")),
ReplaceText = Text.Replace(Lines.ToText(Source)," #(cr)#(lf)", " "),
TextToList = List.RemoveItems(Text.Split(ReplaceText, "#(cr)#(lf)"), {""}),
ToTable = Table.FromList(TextToList, Splitter.SplitTextByDelimiter("|"))
in
ToTable
Holy moly, it worked perfectly. I was trying a long loop to read in line-by-line and brute force a find and replace, but this did it in one step even with 49K rows, thanks so much for your help!