locked
Import Text Files with Line Breaks - 1 Into each Cell RRS feed

  • Question

  • Hello Folks,

    i hope this is not too much of an obvious question.

    I am new to Power queries

    so  I have a folder with a few text files.

    how do I import each file into a row

    The content  has line breaks and spaces

    Sample file

    ==================
    Video provides a powerful way to help you prove your point. When you click Online Video,
    you can paste in the embed code for the video you want to add.

    You can also type a keyword to search online for the video that best fits your document.

    To make your document look professionally produced, Word provides header, footer,
    ==================

    Can I import the above file into a Cell  Row?

     

    Cheers Dan

    Monday, July 11, 2016 4:24 PM

Answers

  • That's not too obvious, but doable:

    let
        Source = Folder.Files("YourFolder"),
        RetrieveContent = Table.AddColumn(Source, "Custom", each Csv.Document([Content], [Delimiter="~", Encoding=1252, QuoteStyle=QuoteStyle.None])),
        CombineRows = Table.AddColumn(RetrieveContent, "Result", each Text.Combine([Custom][Column1], "#(lf)"))
    in
        CombineRows

    You go via "From Folder" and add a column where you access the file contents via Csv.Combine. This will try to split your docs into columns according to the Delimiter sign. If you manage to identify a sign that doesn't occur in your texts, the text wont be split into multiple columns. (I've chosen "~" in my example).

    You might need to adjust the encoding to your local settings: Just click on the content as you've probably done already and check out what the default-settings on your machine delivers.

    The trick lies in the last step "CombineRows" where the multiple rows will be combined using a linefeed "#(lf)"


    Imke Feldmann TheBIccountant.com


    • Edited by Imke FeldmannMVP Monday, July 11, 2016 6:59 PM
    • Marked as answer by Dan_CS Monday, July 11, 2016 7:36 PM
    Monday, July 11, 2016 6:58 PM

All replies

  • So this would be 1 column as well with line breaks and spaces maintained or multiple columns or without linebreaks?

    Imke Feldmann TheBIccountant.com

    Monday, July 11, 2016 5:06 PM
  • Hi Imke,

    Can I import a whole text file into one cell  example A1.

    I wanted all the content of the text file to be in its own cell - at the moment its spread over multiple rows.


    Cheers Dan

    Monday, July 11, 2016 5:45 PM
  • That's not too obvious, but doable:

    let
        Source = Folder.Files("YourFolder"),
        RetrieveContent = Table.AddColumn(Source, "Custom", each Csv.Document([Content], [Delimiter="~", Encoding=1252, QuoteStyle=QuoteStyle.None])),
        CombineRows = Table.AddColumn(RetrieveContent, "Result", each Text.Combine([Custom][Column1], "#(lf)"))
    in
        CombineRows

    You go via "From Folder" and add a column where you access the file contents via Csv.Combine. This will try to split your docs into columns according to the Delimiter sign. If you manage to identify a sign that doesn't occur in your texts, the text wont be split into multiple columns. (I've chosen "~" in my example).

    You might need to adjust the encoding to your local settings: Just click on the content as you've probably done already and check out what the default-settings on your machine delivers.

    The trick lies in the last step "CombineRows" where the multiple rows will be combined using a linefeed "#(lf)"


    Imke Feldmann TheBIccountant.com


    • Edited by Imke FeldmannMVP Monday, July 11, 2016 6:59 PM
    • Marked as answer by Dan_CS Monday, July 11, 2016 7:36 PM
    Monday, July 11, 2016 6:58 PM
  • Hi Imke,

    let me play with this, I am trying to figure out where to put this formula, I'll report back in a bit

    I am a total noob, and I heard really great things about power query so i have to be learning this tool.

    thank you :)


    Cheers Dan

    Monday, July 11, 2016 7:25 PM
  • If you're new to Power Query and M, you must check out this webinar: https://info.microsoft.com/CO-PowerBI-WBNR-FY16-06Jun-21-Amazing-Things-with-Power-BI-Registration.html?Is=Website

    The title is a bit misleading but it is the best intro into M - an hour worth spending before doing anything else :-)


    Imke Feldmann TheBIccountant.com

    Monday, July 11, 2016 7:30 PM
  • Hi Imke,

    just a quick one is there a limit on how many characters it can import into each row?

    I managed to press the advanced editor and insert the formula


    Cheers Dan

    Monday, July 11, 2016 7:36 PM
  • As far as I'm aware it's the 32k -limit of Excel. But you should check that and try out.

    Imke Feldmann TheBIccountant.com

    Monday, July 11, 2016 7:42 PM
  • Thank you Imke,

    I learned something new and it worked - I loaded the files into excel from the query wow

    Never used a PQ formula before  - at least I know where it goes now

    Have a good evening :)


    Cheers Dan

    Monday, July 11, 2016 7:45 PM