PS Command for import from Excel to SharePoint

[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

[panand99]

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

}

[v-xida-msft]

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,

[Christian866]

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

[v-xida-msft]

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,

[Christian866]

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?

[v-xida-msft]

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,

[Christian866]

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?

[v-xida-msft]

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,

[Christian866]

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.