10

Is it possible to import an excel list or excel data into a SharePoint List that has already been created? Not create a new list.

I know you can import a spreadsheet which will create a new list, but I don't want that because I have created columns already that do calculations and such and I don't want to recreate them....

It can be either an excel or access list, but I want to make sure the pre-existing columns in still my SharePoint list.

1
  • 1
    Hi @Matt-Ludwig I have a very thorough and tested solution for exactly what you're looking for on a related question. The key is formatting the data correctly for each respective Column Type in your list, as others have mentioned. I have covered this extensively. Answer: Import/Copy/Paste Data from Excel into Existing SharePoint List.
    – Chiramisu
    Nov 14, 2017 at 18:30

3 Answers 3

3

You can open the list in datasheet view (or create a datasheet view) and just copy & paste your excel sheet rows into the list. Just be careful that the columns are in the same order as in your sheet.

If this is not possible, you need to use script or code. The problem here is to use the right format for your fields. Example:
(src: http://social.technet.microsoft.com/wiki/contents/articles/18830.sharepoint-2010-import-data-from-excel-into-a-list-using-powershell.aspx)

#Get the CSV file and connect to the SharePoint list            
$vessellist = import-csv -Path "C:\Temp\VesselInPortReport.csv"            
#Get the list used to import the data to            
$l = (Get-Spweb "http://test").GetList("http://test/Lists/yourlist")            

#Get the lists EmployeeType field (choice)            
$employeeType = $l.Fields["EmployeeType"] -as [Microsoft.SharePoint.SPFieldChoice]            

#Loop through the items and add them to the list            
$r = 1;            
$itemCount = $vessellist.Count;            
$currentItem = 1;            
foreach($item in $vessellist)            
{            
    Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From CSV into SharePoint" -PercentComplete (($currentItem/$itemCount)*100) -Status "Adding item $currentItem or $itemCount";            
    $currentItem++;            
    $ni = $l.items.Add();            
    #Add the Title, using the rows VESSEL_NAME column            
    $ni["Title"] = $item.VESSEL_NAME;            

    #Add the "Date Recorded" field, using the csv rows "RPT_DATE" column            
    [DateTime]$rd = New-Object System.DateTime;            
    if([DateTime]::TryParse($item.RPT_DATE, [ref]$rd)){            
        $ni["Date Recorded"] = $rd;            
    }            

    #Add the csv rows "TRIP_NO" column to the new list items "Trip Id" field (SPFieldNumber)            
    [Int64]$tn = New-Object System.Int64;            
    if([Int64]::TryParse($item.TRIP_NO, [ref] $tn)){            
        $ni["Trip Id"] = $tn;            
    }            

    #Add some other text properties            
    $ni["Flag"] = $item.FLAG;            
    $ni["Agent Name"] = $item.AGENT_NAME;              
    $ni["Current Location"] = $item.CURRENT_LOCATION;                  

    #Add user information            
    $ni["employee"] = $w.EnsureUser($item.EMPLOYEE); #In this case, the $item.EMPLOYEE value from the spreadsheet is a persons name. Eg. "Matthew Yarlett"             
    $employeeType.ParseAndSetValue($ni,$item.EMPLOYEE_TYPE); #In this case, the $item.EMPLOYEE_TYPE value from the spreadsheet is valid choice present in the EmployeeType list field. Eg. "Manager"            

    #Update the item            
    $ni.Update()            
    $r++;            
}

I recommend just creating a new list from the sheet, it's less painful.

6
  • the only problem with this is that you are only allowed to do 100 rows at a time in 2013 and I have 24000 records... Is there a way to do more? Jan 14, 2015 at 13:46
  • if I create a new list then my calculated colmns would be gone, where do I enter this powershell script? Jan 14, 2015 at 15:44
  • how many calculated columns do you have? they are not so hard to create right? you enter the script in powershell console on your sharepoint server but you have to edit it for your needs.
    – Gwny
    Jan 14, 2015 at 16:30
  • I have about 20 calculated records Jan 14, 2015 at 17:03
  • 1
    I just assume that it would be easyier to copy/paste this formulas then to mess around with different Field-Types and how to fill them with powershell. If you just have some number and text fields then you can easy use powershell.
    – Gwny
    Jan 15, 2015 at 13:43
0

There is no out of the box way to achieve this. Possible work arounds

  1. Export the existing data into excel. Append the new data into the same excel file. Then edit the list in data sheet view, copy and paste the data from excel file
  2. Open the list in Access application and then do a copy and paste of missing row.
  3. Using programming
    • Powershell
    • VB Script
    • Client Side Object Model
1
  • how do I add a powershell script that will allow me to copy in more then 100 records? Jan 14, 2015 at 16:20
0

you can use this free app to import Excel CSV into sharepoint online or onprem existing lists.

https://www.paragyte.com/blog/import-or-upload-excel-csv-or-spreadsheet-data-into-sharepoint-list

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.