none
Import all files and new files from a folder RRS feed

  • Question

  • Hello. If I have files in a folder how can I import all present files, do the SAME things on each one (perhaps add a column, promote headers etc) merge all files together (but just have one set of headers) and then add new files whenever they appear? Thanks. 
    Saturday, April 22, 2017 9:55 AM

Answers

  • An alternative to using the Combine.Binaries (which I never use) is to create a custom function that will return the content in an additional column: http://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

    @Andrea: If you still get errors, then it might be because your files are actually different and the function that works for opening the 1st file is different for the second. You can check that by clicking on the "Binary word" as you've already done and compare the autogenerated codes from both actions: If they are different, you can create 2 functions and combine them with an error-handler. try .... otherwise


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Saturday, April 29, 2017 6:10 AM
    Moderator

All replies

  • This is basic Power Query functionality (at least in the current versions).

    If you choose "Files from folder", you get a navigation list with the files in the folder.
    You may want to apply some filtering to limit the list to the files you want to import and combine.
    Next press the button in the column header of the "Content" column (this is the "Combine Binaries" button).
    This will create 4 new query objects for you and 4 additional steps in your main query.

    One of the 4 queries is a "Transform Example...."   query in which the transformations are specified for 1 file.
    These transformations are automatically promoted to the function (one of the other query objects that was created) and consequently to all the (selected) files from the folder.

    Depending on your file types, headers may already have been created in the "Transform Example..."  query, so when the nested tables are expanded in the main query, you will have 1 set of headers.

    Monday, April 24, 2017 9:35 AM
  • I'm trying to do the same import but using the "from Sharepoint Folder".

    I'm able to get the list of the files and to filter against the sub-folder I want to target (in my case I have 6 excel in xslb format).

    But when I click on the binary it generates two steps:

    1)"Combined Binaries"
    2)"Imported Excel"
    The second one fails with the following "DataFormat.Error: The input couldn't be recognized as a valid Excel document. Details: Binary".

    Now, if I delete these two steps and click on the "Binary" word of one of the records it opens the excel and let me work on it. So apparently the excels are not the issue.

    Any idea?

    Tuesday, April 25, 2017 9:52 AM
  • An alternative to using the Combine.Binaries (which I never use) is to create a custom function that will return the content in an additional column: http://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

    @Andrea: If you still get errors, then it might be because your files are actually different and the function that works for opening the 1st file is different for the second. You can check that by clicking on the "Binary word" as you've already done and compare the autogenerated codes from both actions: If they are different, you can create 2 functions and combine them with an error-handler. try .... otherwise


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Saturday, April 29, 2017 6:10 AM
    Moderator
  • Andrea, did the above suggestions solve your issue?

    Ehren

    Friday, May 5, 2017 7:27 PM
    Owner