none
Power Query - Selected Import From Text Files RRS feed

  • Question

  • Hi friends,

    is there a way for me to extract only selected data from my  text files in power query

    Example below extract everything between START and END

    Sample File
    ================
    Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.
    START
    Fruit 1kg
    Bananas 500g
    Crisps
    Cereal
    END
    Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum
    ===============

    or once I Import my files do I do a query to say only extract between Start and End in another column

    any ideas welcome

    Cheers Dan

    Tuesday, July 12, 2016 1:58 PM

Answers

  • Dan,

    At face value, it appears that you have text separated by lines breaks. If this is the case, you can do something like:

    let
        Source = Lines.FromBinary(File.Contents(<FullFilePath>)),
        ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        TrimmedText = Table.TransformColumns(ConvertedToTable,{},Text.Trim),
        PositionOfStart = List.PositionOf(TrimmedText[Column1], "START"),
        RemovedRowsFromTop = Table.RemoveFirstN(TrimmedText, PositionOfStart + 1),
        PositionOfEnd = List.PositionOf(RemovedRowsFromTop[Column1], "END"),
        RemovedRowsFromBottom = Table.RemoveRows(RemovedRowsFromTop, PositionOfEnd, Table.RowCount(RemovedRowsFromTop) - PositionOfEnd)
    in
        RemovedRowsFromBottom

    Tuesday, July 12, 2016 3:03 PM

All replies

  • Dan,

    At face value, it appears that you have text separated by lines breaks. If this is the case, you can do something like:

    let
        Source = Lines.FromBinary(File.Contents(<FullFilePath>)),
        ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        TrimmedText = Table.TransformColumns(ConvertedToTable,{},Text.Trim),
        PositionOfStart = List.PositionOf(TrimmedText[Column1], "START"),
        RemovedRowsFromTop = Table.RemoveFirstN(TrimmedText, PositionOfStart + 1),
        PositionOfEnd = List.PositionOf(RemovedRowsFromTop[Column1], "END"),
        RemovedRowsFromBottom = Table.RemoveRows(RemovedRowsFromTop, PositionOfEnd, Table.RowCount(RemovedRowsFromTop) - PositionOfEnd)
    in
        RemovedRowsFromBottom

    Tuesday, July 12, 2016 3:03 PM
  • If you import using the From.Folder-method, the query won't fold back to the source so you import and then select what's relevant:

    let
        Source = Folder.Files("YourFolderPath"),
        RetrieveContent = Table.AddColumn(Source, "Custom", each Csv.Document([Content], [Delimiter="~", Encoding=1252, QuoteStyle=QuoteStyle.None])),
        PositionsOfStartAndEnd = Table.AddColumn(RetrieveContent, "Positions", each Table.PositionOfAny(Table.TransformColumns([Custom], {{"Column1", Text.Trim}}), {[Column1="START"], [Column1="END"]}, Occurrence.All)),
        SelectRange = Table.AddColumn(PositionsOfStartAndEnd, "Custom.2", each Table.Range([Custom], [Positions]{0}+1, [Positions]{1}-[Positions]{0}-1))
    in
        SelectRange
    ... haven't seen Colins answer - now you have 2 options :-)


    Imke Feldmann TheBIccountant.com


    Tuesday, July 12, 2016 3:21 PM
    Moderator
  • thank you Imke,

    you are a genuis :)

    This will be sooooooo useful to me -

    I'm not very good  at VBA and well I always need to extract something into excel and never can :(

    Please can you advise is there any way to get it into one cell again.

    I will be so grateful -

    thanking you


    Cheers Dan

    Tuesday, July 12, 2016 3:35 PM
  • Basically like yesterday, just that you reference the new column "Custom.2":

        CombineRows = Table.AddColumn(SelectRange, "Result", each Text.Combine([Custom.2][Column1], "#(lf)"))
    in
        CombineRows


    Imke Feldmann TheBIccountant.com

    Tuesday, July 12, 2016 6:33 PM
    Moderator
  • Thank you Colin and Imke,

    this will help me get started on my power queries and i wont have to deal with all that mess  from the other data.

    Good evening to all :)


    Cheers Dan

    Tuesday, July 12, 2016 8:36 PM