cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

PS Command for import from Excel to SharePoint

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

1 ACCEPTED SOLUTION

Accepted Solutions

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
Solution Supplier
Solution Supplier

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

}

 

Community Support
Community Support

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-e...

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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 @{...}

 5.JPG

 

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

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

PS.PNGemptylines.PNG
 

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

CSVlist.PNG

Maybe you wanna see my csv file?

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.
Bild1.PNGBild2.PNG
 

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (64,765)