Hi Guys,
Does anyone know any way of importing data from Excel spreadsheet to an existing Sharepoint List?
Cheers.
It’s always tricky to import from Excel to an EXISTING SharePoint list. The way you did to start from scratch (import to a NEW list) is the way to go. In both cases, you need to prepare both Excel and SharePoint lists so they match in terms of fields and format. But that’s easily said than done. I have come across these type of requests. One case was for a very ACTIVE SP list and has to be available to users 24/7, we added a “process” and created new columns to support it. To import “new data”, we created a View which is a Datasheet View to help with quality checking. Since new items are assigned unique IDs, no issue with overwriting current items. The challenge was for “modified items” — so for a growing list of “>10K” items, we got tired of copy/paste and used MS Access instead using data connection.
Best way I found to archive what I needed.
As I was not able to copy from Excel and past on sharepoint (data view) what I’ve done.
There is an app on Sharepoint Online that allow you to import an excel file to a new list.
Use this tool to create a new list with the information I wanted, go to Data view on both lists and copy the information from one to the other.
I am now moving more information.
Thanks for all your suggestions and help.
If the data isnt complex, just setup a datasheet view where the columns are in the same order as the excel sheet and then copy and paste in… Simplest way I can think of…
Well, having trouble doing it.
I am getting a message
“You cannot call a method on a null-valued expression.
At C:\Users\Andre\Desktop\Additems.ps1:32 char:4
+ $spItem = $spData.AddItem()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull”
On every single record I try to add.
My code:
# Fieds to pass (Tender Enquiry No , Date, Contact Name, Contact Title, Company Name, Site Address, Status)
# Setup the correct modules for SharePoint Manipulation
if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{
Add-PsSnapin Microsoft.SharePoint.PowerShell
}
$host.Runspace.ThreadOptions = “ReuseThread”
#Open SharePoint List
$SPServer=”https://xxxxxxx.sharepoint.com/Forms”
$SPAppList=”/Lists/Tender Enquiry form/”
$spWeb = Get-SPWeb $SPServer
$spData = $spWeb.GetList($SPAppList)
$InvFile=”SharepointEnquiries.csv”
# Get Data from Inventory CSV File
$FileExists = (Test-Path $InvFile -PathType Leaf)
if ($FileExists) {
“Loading $InvFile for processing…”
$tblData = import-csv $InvFile
} else {
“$InvFile not found – stopping import!”
exit
}
# Loop through Applications add each one to SharePoint
“Uploading data to SharePoint….”
foreach ($row in $tblData) {
“Adding entry for “+$row.”Tender Enquiry No”.ToString()
$spItem = $spData.AddItem()
$spItem[“Tender Enquiry No”] = $row.”Tender Enquiry No”.ToString()
$spItem[“Date”] = $row.”Date”.ToString()
$spItem[“Contact Name”] = $row.”Contact Name”.ToString()
$spItem[“Contact Title”] = $row.”Contact Title”.ToString()
$spItem[“Company Name”] = $row.”Company Name”.ToString()
$spItem[“Site Address”] = $row.”Site Address”.ToString()
$spItem[“Status”] = $row.”Status”.ToString()
$spItem.Update()
}
“—–“
“Upload completed”
$spWeb.Dispose()
Hi Guys, thanks for your answers.
I didn’t had enough time to test over the weekend but I will do it today.
Thanks
This doesn’t work, as it won’t let you paste a range of cells into a list. Only text one cell at a time. On SP server 2010, you could do this.