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
powerbiuser9971's avatar
powerbiuser9971
New Member
4 years ago

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.

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    4 years ago

    You 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.

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    4 years ago

    And here is PQ code that can handle those included carriage returns.  It works on your limited one row sample.

     

    Read the code comments and explore the applied steps to better understand the algorithm.

     

    If this data will be going to an Excel worksheet, I suggest doing the Text.ReplaceRange using linefeed rather than carriage return as that seems to be standard in Excel multiline cells (eg replace "#(cr)" with "#(lf)" in the Text.ReplaceRange function)

     

     

     

    let

//Read in file as a Text File
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("full_pathname"), null, null, 1252)}),

//create grouping column
//    add Index column
//    add custom column the = Index value if line does NOT end with nbsp (else null)
//    fill up
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "grouper", each if Text.EndsWith([Column1],"#(00A0)") then null else [Index]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Filled Up" = Table.FillUp(#"Removed Columns",{"grouper"}),

//Group by the "grouper" column
//Concatentate the rows after replacing any terminal nbsp with cr
    #"Grouped Rows" = Table.Group(#"Filled Up", {"grouper"}, {
        {"textStrings", (t)=>
            Text.Combine(
                List.Transform(t[Column1], 
                    each if Text.EndsWith(_,"#(00A0)") 
                        then Text.ReplaceRange(_,Text.Length(_),1,"#(cr)") 
                        else _),
            "")}
         
         }),

//Remove the grouper column
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"grouper"}),

//Split by the Pipe
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "textStrings", 
        Splitter.SplitTextByDelimiter("|", QuoteStyle.None))
in
    #"Split Column by Delimiter"