Forum Discussion
Removing nonbreaking space and carriage return from pipe delimited text file
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
ronrsnfld I wish I had a choice in the matter, but the exports come from a legacy SaaS platform that has apparently never heard of quoted text or escape characters. The CRs are easy enough to get rid of manually (open text reader, do a RegEx-type search for carriage returns and replace them with spaces, hyphens, etc. and save changes), but there are hundreds of files to process in each dump, kind of defeats the purpose of running batch conversions in the first place.
Super UserYou might need a different tool to do this optimally. But in PQ you could certainly detect the <nbsp><cr> combinations and replace them with something (or use that to combine with the next line, depending on the rest of your code, and your data). If you want, upload a sample text file that has the problem, and I'll take a look.