8

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
    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 '17 at 18:30
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.

  • 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? – Matt Ludwig Jan 14 '15 at 13:46
  • if I create a new list then my calculated colmns would be gone, where do I enter this powershell script? – Matt Ludwig Jan 14 '15 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 '15 at 16:30
  • I have about 20 calculated records – Matt Ludwig Jan 14 '15 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 '15 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
  • how do I add a powershell script that will allow me to copy in more then 100 records? – Matt Ludwig Jan 14 '15 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, privacy policy and cookie policy

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