Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Extract Excel and PDF Files (Convert To Excel if Possible) for 100+ Individual Folders

Hi,


Is it possible to achieve the subject above via Powe Query or Power Bi or Excel?

 

If so, what's the best course of action?

 

//NT

Need Help

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    2 years ago

    Anonymous 

     

    Is it possible?  Yes.  How to do it?  Well, depends on a lot of things and you haven't provided any details.

     

    Are the folders on a PC, Sharepoint, somewhere else?  Are the file contents structured the same - obviously the PDF and Excel files are different.

     

    Regards

     

    Phil

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

       

      Hi,

       

      The files either excel or pdf are located in subfolders as shown but since I'm working on 100+ Job Nos. I need to open 100 Main Folders and then go to their respective subfolders which will be tedious if done manually.

       

      All files are stored in sharepoint. 

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        You may try Sharepoint Folder connector. Once connecting to a SharePoint site, you will see all files on that site. Then filter files by [Extension] column and [Folder Path] column to find the files that have the same file type and need to be combined. 

        Power Query SharePoint folder connector

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    You can use Power Query. Power Query is a data transformation and data preparation engine that is available in both Excel and Power BI. Power Query has the following connectors. 

    Power Query Folder connector - Power Query | Microsoft Learn

    Power Query Excel connector - Power Query | Microsoft Learn

    Power Query PDF connector - Power Query | Microsoft Learn

     

    Not sure if all of your Excel files and PDF files share the same table structure. If so, you can use the Folder connector to connect to a folder which has all the files, filter only Excel type files, then combine the files (Combine files overview - Power Query | Microsoft Learn). It will let you choose a file as Sample file, and it will detect the file type of the sample file then choose an appropriate function to extract the data. You can go to Transform Sample File query to do some transformations. These transformation steps will be applied to all files to be combined.

     

    For PDF files, perform a similar operation like above but filter only PDF type files before combining files. 

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!