locked
WhatsApp & Replace Values #(lf)#(cr) RRS feed

  • Question

  • WhatsApp facilitates the export of a chat via email. The _chat.txt file contains both CRLF (#(cr)#(lf)) and LF #(lf). I know that with the aid of “Replace Values” I can replace all the LF’s with nothing or something else.

    The number of rows however remain the same, due to the fact that the Table is already created.

    With LF removed via Notepad++ the number of rows is corect and every row has a date and a time.

    My question is can this be done without the help of Notepad++.

    Thx in advance.

    Robert

    Wednesday, May 11, 2016 10:02 AM

Answers

  • Hi Robert,

    For the start of your query, try something like this:

    let
        Source = Text.FromBinary(File.Contents("C:\Users\rstam_000\Downloads\WhatsApp Chat - Noud en Olle fans (2)\_cha2t.txt")),
        #"Replaced Linefeeds" = Text.Replace(Source, "#(lf)", ""),
        #"Converted to Lines" = Lines.FromText(#"Replaced Linefeeds"),
        #"Converted to Table" = Table.FromList(#"Converted to Lines", Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        #"Converted to Table"

    It replaces the linefeed characters before calling Lines.FromText.

    Ehren

    Thursday, May 12, 2016 5:10 PM

All replies

  • You should be able to run a replace on the source file, prior to converting it to a table. Can you click the Advanced Editor button and paste the M code for your query here?

    Also, how big is the exported _chat.txt file? Are we talking a few MB, or something potentially much bigger?

    Thanks,

    Ehren

    Wednesday, May 11, 2016 10:37 PM
  • The files are small, just text.let
        Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\rstam_000\Downloads\WhatsApp Chat - Noud en Olle fans (2)\_cha2t.txt"), null, null, 65001)}),
        #"Replaced Value" = Table.ReplaceValue(Source,"#(lf)","",Replacer.ReplaceValue,{"Column1"}),
        #"Split Column by Position" = Table.SplitColumn(#"Replaced Value","Column1",Splitter.SplitTextByPositions({0, 8}, false),{"Column1.1", "Column1.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", type date}, {"Column1.2", type text}}),
        #"Split Column by Position1" = Table.SplitColumn(#"Changed Type","Column1.2",Splitter.SplitTextByPositions({0, 9}, false),{"Column1.2.1", "Column1.2.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Column1.2.1", type time}, {"Column1.2.2", type text}}),
        #"Split Column by Position2" = Table.SplitColumn(#"Changed Type1","Column1.2.2",Splitter.SplitTextByPositions({0, 2}, false),{"Column1.2.2.1", "Column1.2.2.2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position2",{{"Column1.2.2.1", type text}, {"Column1.2.2.2", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.2.2.1"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns","Column1.2.2.2",Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false),{"Column1.2.2.2.1", "Column1.2.2.2.2"}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.1", "Datum"}, {"Column1.2.1", "Tijd"}, {"Column1.2.2.2.1", "Deelnemr"}, {"Column1.2.2.2.2", "Tekst"}}),
        #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Tekst2", each if [Tekst] = null then [Deelnemr] else [Tekst] ),
        #"Renamed Columns2" = Table.RenameColumns(#"Added Conditional Column",{{"Deelnemr", "Deelnemer"}}),
        #"Filtered Rows" = Table.SelectRows(#"Renamed Columns2", each true),
        #"Added Conditional Column1" = Table.AddColumn(#"Filtered Rows", "Deelnemer1", each if [Tekst] = null then "Beheerder" else [Deelnemer] ),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column1",{"Deelnemer"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Datum", "Tijd", "Deelnemer1", "Tekst", "Tekst2"}),
        #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns",{"Tekst"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Tekst2", "tekst"}, {"Deelnemer1", "Deelnemer"}}),
        #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each Text.Length([tekst])),
        #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}, {"tekst", type text}, {"Deelnemer", type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Changed Type4",{{"tekst", Text.Trim}}),
        #"Filtered Rows1" = Table.SelectRows(#"Trimmed Text", each true),
        #"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Datum", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
        #"Reordered Columns1" = Table.ReorderColumns(#"Added Index",{"Index", "Datum", "Tijd", "Deelnemer", "tekst", "Custom"}),
        #"Added Index1" = Table.AddIndexColumn(#"Reordered Columns1", "Index.1", 0, 1),
        #"Reordered Columns2" = Table.ReorderColumns(#"Added Index1",{"Index", "Index.1", "Datum", "Tijd", "Deelnemer", "tekst", "Custom"})
    in
        #"Reordered Columns2"
    Thursday, May 12, 2016 3:45 PM
  • Hi Robert,

    For the start of your query, try something like this:

    let
        Source = Text.FromBinary(File.Contents("C:\Users\rstam_000\Downloads\WhatsApp Chat - Noud en Olle fans (2)\_cha2t.txt")),
        #"Replaced Linefeeds" = Text.Replace(Source, "#(lf)", ""),
        #"Converted to Lines" = Lines.FromText(#"Replaced Linefeeds"),
        #"Converted to Table" = Table.FromList(#"Converted to Lines", Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        #"Converted to Table"

    It replaces the linefeed characters before calling Lines.FromText.

    Ehren

    Thursday, May 12, 2016 5:10 PM