Archive in SharePoint import data from excel and from file name

Question

hello,

I am working on a Sharepoint archive what I have done since now is

• saved all the files in the folders
• create the column for the data

• export in excel the list of files with path
• added some information

and now I have tried to follow different explanation in internet but the import is not working...

I would like to import some information but for some column I am trying to make automatic data from file name, it will be a problem to have import and something automatic?

thanks for the help

Answer 1

Dear Dropje_30,

Since SharePoint doesn’t have a direct option to re-import updated data from Excel, you can use these methods to automate the import process.

1. Use Power Automate

Create a Flow in Power Automate:

Go to Power Automate (https://flow.microsoft.com) and create a new flow.

Use the trigger Manually trigger a flow if you want to run it manually.

Retrieve Excel Data:

Add an action: List rows present in a table to connect to your Excel file (stored in OneDrive or SharePoint).

Select the correct Excel file and table containing the data. Make sure your Excel file has a column for the Item Name (which corresponds to a unique field in SharePoint).

Loop Through Each Row:

Add an Apply to Each action to loop through each row of the Excel data.

Inside the loop, add the action Get Items from SharePoint to match the Item Name in Excel with the corresponding SharePoint list items.

Search for Item in SharePoint Using Item Name:

In the Get Items action, filter the items in SharePoint using a Filter Query. You can use the Item Name column for matching.

The Filter Query format should be like this:

Title eq '@{items('Apply_to_each')?['ItemName']}'

Replace Title with the internal name of the Item Name column in your SharePoint list and ItemName with the name of the column in your Excel file.

Update the Item:

Inside the Apply to Each action, after retrieving the SharePoint item, add an Update Item action.

Map the necessary Excel column values to the corresponding SharePoint columns in this action.

Use the ID from the retrieved SharePoint item in the Update Item action to ensure the correct item is updated.

2. You can also use PnP PowerShell for Bulk Import.

If you’re familiar with PnP PowerShell, this is another way to update the existing SharePoint list items in bulk.

Here’s a simplified script you can use:

Connect to SharePoint

Connect-PnPOnline -Url "https://yourtenant.sharepoint.com/sites/yoursite" -UseWebLogin

Import the Excel file

$excelData = Import-Excel -Path "C:\path\to\your\file.xlsx"

Loop through each row in the Excel file

foreach ($row in $excelData) {

Get the existing item in the SharePoint list using the title column

$item = Get-PnPListItem -List "YourList" -title $row.title

Update the necessary columns with the new values from Excel

Set-PnPListItem -List "YourList" -Identity $item.title -Values @{

"Column1" = $row.Column1

"Column2" = $row.Column2

Continue for other columns

}

}

3. Manual Import with Quick Edit

If you prefer not to use Power Automate or PnP PowerShell, you can do a manual bulk update:

Open the SharePoint list in Quick Edit mode.

Copy and paste the updated column values from your Excel file directly into the SharePoint list. SharePoint Quick Edit allows you to paste values across multiple cells, similar to how you work in Excel.

Thank you for your time.

Best regards,

Answer 2

Dear Sophia,

thank you for the answer :-)

I will try Power Automate or PNP Power Shell, because I need to add data matching the files already saved in SharePoint, and I have tried with Quick edit but I think I didn't understand correctly and instead of add data to the matched file the copy paste added new rows and return a lot of errors.

Do you think I can use Power Automate to "fill" some field with the information in the file name?

eg.

H-IMP-AI-7-xx-xx.pdf

these information are data for some of the column:

H = Section

IMP = Author

etc

thanks and regards

Donatella