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

[tksnota]

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

[Anonymous]

Hi @tksnota 

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!

[PhilipTreacy]

@tksnota 

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

[tksnota]

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]

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