Extract metadata from excel and add to document library in folders

Hello

I have been given 30,000 construction project files. Lucky me?

They have come to me in folders in the following folder structure:

ContractorName\DocumentType

and the document title.

For example:

JimTheBuilder\Drawings\Drawing1.dwg

JimTheBuilder\Drawings\Drawing2.dwg

JimTheBuilder\Drawings\Drawing3.dwg

JimTheBuilder\Specifications\Spec1.doc

JaneTheContractor\Drawings\Elevation1.dwg

JaneTheContractor\Reports\Report.doc

etc etc

The client has placed them complete with folder structure in a Document Library called AllVersionsArchive

The client has also given me an Excel file with Metadata for each file.

One row for each file, all on one sheet.

She wants me to find a way to add the metadata to every file in the document library.

There are three "fields" that are the same in the folder structure and the metadata.

Folders: ContractorName\DocumentType\fileName

Excel:     ContractorName\DocumentType\fileName

The file names are unique and can be used as a Primary Key

the Excel file has extra metadata: description, status, shortName, ReceivedDate, IssueNumber, revisionLetter

Its the metadata in blue that I have to add to each file in each folder.

As I see it I need to:

• Go through the Excel data row by row,
• for each file, find the matching file name in the SharePoint document library
• attach the metadata.

I have some basic PowerAutomate experience and I am OK with PoweShell. But Im not a programmer and need some help and pointers as to where to start. I have Googled around of course but its the requirement to go recursively through the folders to find a match that defeats me.

I was also considering converting the files and folders into just one flat Document Library containg the fileName, ContractorName and Document type. I could then perhaps mathch the metadata more easily?

Any ideas?

Adam.