Answered

PS Command for import from Excel to SharePoint

(0) Share

Report

Posted on by Christian866

Hello everybody,

I have a list in SharePoint with 10 columns and I always get excel files sent with the exact same 10 columns. Now I would like to transfer the Excel content to the SharePoint list. Is there a Powershell command that makes an import possible?

P.S. There is an export button at excel, but he keeps putting me on a new list in SharePoint. I would like to use the same list.

Best regards Christian

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

Sort by

panand99 641 on at

Like
a
(0)

Report
Copy link

Link copied!

To import using powershell, it will works only with CSV file.

you need to first import CSV file and then in ForEach loop needs to create items in sharepoint

$ImportSharedCSV=Import-Csv P:\temp\1.csv
$ImportSharedCSV.Values
foreach ($line in $ImportSharedCSV)
{
$fieldvalue=$line.values
$MaterialCode= $fieldvalue.Split("~")[0];
// you can sets your varialbe name as field name above array columns

$item=@{
"Title"=$MaterialCode
}
Add-PnPListItem -List listname -Values $item -ContentType Item -ErrorAction Stop
}

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Christian866 ,

Do you import data from your Excel file into a SP list?

I agree with @panand99 's thought almost. Currently, there is no direct PowerShell command to import data from a Excel file into a SP list.

An an alternative solution, you could consider convert your Excel file into a CSV file firstly. Then use PowerShell command to import data from the CSV file into your SP List.

Please check and see if the following blog would help in your scenario:

https://social.technet.microsoft.com/wiki/contents/articles/18830.sharepoint-2010-import-data-from-excel-into-a-list-using-powershell.aspx

https://practical365.com/sharepoint-online/csv-file-to-sharepoint/

Also please check and see if the following video would help to solve your problem:

https://www.youtube.com/watch?v=Q9_9zC62OVA

Best regards,

Was this reply helpful? Yes No
Christian866 14 on at

Like
a
(0)

Report
Copy link

Link copied!
Hello thank you for the quick feedback. I'm just trying to go through the tutorial (https://practical365.com/sharepoint-online/csv-file-to-sharepoint/). The First steps was fine.

i Connect to the SharePoint site and i import my csv to view all rows in Powershell. it works

I do not understand how he knows which list he should take on the URL? I can have multiple lists on the SharePoint site?
Best regards Christian

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Christian866 ,

When you execute the Foreach action within your PowerShell window, you need to specify the List Name you want to import your CSV data to using following cmdlet:

Add-PnPListItem -List "Customer Database" -Values @{...}

In addition, for your second question, the answer is YES. You could have multiple lists on your SP Site.

Best regards,

Was this reply helpful? Yes No
Christian866 14 on at

Like
a
(0)

Report
Copy link

Link copied!
I have now managed to import the data, but only empty lines in the Sharepoint list (see the gray lines in the picture). How can something like that happen?

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Christian866 ,

Do you rename the Title column into "Personalnummber" column in your TestCSV SP List?

Please do not rename the Title column within your TestCSV SP List when you import CSV Data from your CSV file into your SP list using PowerShell cmdlet.

On your side, please consider change your "Personalnummber" column back to Title column in your TestCSV SP List, or re-create a new SP List, then try the Foreach function again, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Christian866 14 on at

Like
a
(0)

Report
Copy link

Link copied!
I have now deleted the list and created one again with the same name. The title I left, only the first name and staff number I have created. Is this correct?

Maybe you wanna see my csv file?

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Christian866 ,

Yeah, you are right.

Please take a try with your new created SP List, try the Foreach function in your PowerShell window again, check if the issue is fixed.

Best regards,

Was this reply helpful? Yes No
Christian866 14 on at

Like
a
(0)

Report
Copy link

Link copied!
Here is the new list ... unfortunately empty again with the same foreach loop. You can now see the contents of the csv file as well.

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Christian866 ,

Have you specified the Title column within the Foreach function in your PowerShell window?

Please consider modify your Foreach function as below:

foreach ($Record in $CustomerData){ Add-PnPListItem -List "TestCSV" -Values @{ "Title"= $Record.'Personalnummer'; "Personalnummer"= $Record.'Personalnummer'; "Vorname"= $Record.'Vorname' }
}

please consider take a try with above PowerShell cmdlet, then check if the issue is solved.

Best regards,

Was this reply helpful? Yes No