Forum Discussion

powerbiuser9971's avatar
powerbiuser9971
New Member
4 years ago
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...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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's avatar
ronrsnfld
Icon for Super User rankSuper User
4 years ago

The convention, for a csv file, is that if certain characters are included within a section, that section should be surrounded by text qualification characters -- usually a double quote.  Those certain characters include the comma and the carriage return.  Your csv file does not have that, so one solution would be to have the csv file better formed.