none
Importing to Excel from TXT file with custom record (row) delimiters RRS feed

  • Question

  • Hi all - this should be so easy I'm sure I'm missing something obvious.

    I have to import files into Excel that were created as dumps from a mainframe process into a Word file.  With earlier versions of Excel, I could just declare a custom delimiter and Bob's your uncle.  Now, if I don't accept Excel's default I'm pushed into Power Query editor.  It will let me split columns by delimiter but I can't for the life of me find anything to set a record (row) delimiter.

    I saw another post where it mentioned that one has to edit the default Excel text file reader every time but that reply was from someone at Microsoft who said they were working to improve PQE, dated 2013.  Six years ago.

    Does anybody know where I'm missing this?

    Thursday, February 21, 2019 1:44 AM

Answers

  • It is possible to parse the string/file in just 2 steps in Power BI:

    1. In the Ribbon Transform click on Split Text and select your row delimiter => you get a list of rows
    2. In the Ribbon Transform click on To Table to convert the list to a table and select your column delimiter.
    3. The last step is just automatical transforming of column types, if needed.

    The generated PQ code:

    let
        Source = "1,2,3;4,5,6;7,8,9",
        #"Split Text" = Text.Split(Source, ";"),
        #"Converted to Table" = Table.FromList(#"Split Text", Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}})
    in
        #"Changed Type"
    
    Saturday, February 23, 2019 3:18 PM

All replies

  • Hi Mike

    This is screenshot from the Power BI, but I am pretty sure that in Excel the "Get Data" -> "from text/csv file" is the same


    Maxim Zelensky Excel Inside

    Thursday, February 21, 2019 11:29 AM
  • Assuming a file with strange delimiters such as this:

    1,2,3,4,5,6,7,8:5,4,3,2,1,4,6,3:6,5,3,2,2,5,7,4:6,8,7,5,3,5,7,8:

    Read the file with Colon as delimiter. Make sure your type isn't changed to number, keep it as text (to keep commas intact)

    Transpose it so that you only have one column.

    Split the column by comma into more columns.

    Then you get this:


    Thursday, February 21, 2019 4:34 PM
  • It is possible to parse the string/file in just 2 steps in Power BI:

    1. In the Ribbon Transform click on Split Text and select your row delimiter => you get a list of rows
    2. In the Ribbon Transform click on To Table to convert the list to a table and select your column delimiter.
    3. The last step is just automatical transforming of column types, if needed.

    The generated PQ code:

    let
        Source = "1,2,3;4,5,6;7,8,9",
        #"Split Text" = Text.Split(Source, ";"),
        #"Converted to Table" = Table.FromList(#"Split Text", Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}})
    in
        #"Changed Type"
    
    Saturday, February 23, 2019 3:18 PM