1

Is it possible to use REST API to import data from excel file to sharepoint list?

If No, how can I import excel to sharepoint list through the code!

I am using SharePoint online

1
  • 3
    But why you are importing the data using rest api or code instead we can create a sharepoint list by simply importing the excel spreadsheet to SharePoint. Dec 24, 2019 at 15:23

5 Answers 5

3

NO CODE APPROACH:

There is no REST API, to import data from Excel to SharePoint, instead, you can directly export Excel sheet to SharePoint, it's a default feature in Excel. This option creates a new list in SharePoint or you can create a new list in SharePoint and copy-paste the values.

CODE APPROACH:

In my previous project to read the data from excel which is placed in a library from the same site, I used ExcelPLus, it's a free JavaScript plugin, include that in your SharePoint page and read the content of your excel file very easily.

Below code, snippet shows how to open and display the content of an excel sheet.

var ep=new ExcelPlus();
// we call openRemote()
ep.openRemote("http://my.server.com/path/file.xlsx", function(passed) {
  if (!passed) alert("Error: impossible to load the remote file");
  else alert(ep.selectSheet(1).readAll()) // show the content of the first sheet
})

and if you know the range from which you need to fetch the data from the excel sheet, then you can use the read function, like below,

read(range, options)

the range can be a range (e.g. "A1:D1"), or a single cell (e.g. "A1")

For more information, refer this link http://aymkdn.github.io/ExcelPlus/

2

I'll suggest PowerShell for similar requirement.

You could check PowerShell script from this thread.

Or read excel by sheetjs and insert into SharePoint by JSOM/rest api.

Creating a List Item Using ECMAScript(JSOM)

Or c# solution by EPPlus, demo

2

Add items from csv file to SharePoint Online List using PowerShell in CSOM.

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Provide CSV location
$ImportFile ="D: \Powershell\LearnPowershell\Test.csv"
$csv = Import-CSV $ImportFile

#next pass the credentials and connect to web
$siteURL = "https://hubto.sharepoint.com/sites/learnhub"
$Listname="Employee"
$userName = "User.onmicrosoft.com"
$PlainPassword = "Password"

$password = $PlainPassword | ConvertTo-SecureString -AsPlainText -Force
# set SharePoint Online credentials
$SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)

#Creating client context object
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
$clientContext.credentials = $SPOCredentials
$web = $clientContext.Web
$clientContext.load($web)

#Get the List
$List = $clientContext.Web.Lists.GetByTitle($Listname)
$clientContext.Load($List)
$clientContext.executeQuery()

foreach($row in $csv)
{
    #Creat single list Items
    $ListItenCreationInformation =New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
    $NewListItem = $List.AddItem($ListItenCreationInformation)
    $NewListItem["Sourcesite"] = $row. Sourcesite
    $NewListItem["TargetSite"] = $row. TargetSite
    $NewListItem["WaveId"] = $row.WaveId
    $NewListItem["Status"] = $row.Status
    $NewListItem.Update()
    $ClientContext.ExecuteQuery()
}
Write-Host "Items Added to List Sucessfully"

Ref:https://www.enjoysharepoint.com/steps-to-add-items-from-csv-file-to-sharepoint-online-list-using-powershell-in-csom/

1
  • If any of the above answers satisfied from your end, Please accept it. It will be helpful other users. Dec 26, 2019 at 4:37
1

If you want to do it programmatically, the easiest approach is to use PowerShell PnP.

  1. Make sure your CSV file has column names that match internal names of columns of the destination SharePoint List
  2. Make sure destination SharePoint list exists with all coluns from the CSV file
  3. Run the following to migrate data from the CSV to a SharePoint List. Don't forget to update SOURCE_FILE.csv and TARGET_LIST_TITLE with the appropreate values:

PowerShell

Install-Module SharePointPnPPowerShellOnline
# MIGRATE CSV TO SHAREPOINT
$items = Import-CSV "SOURCE_FILE.csv" 
Write-host Creating list items. Please, wait
foreach($item in $items){
    $hashTable = @{}
    $item.psobject.properties | ForEach-Object { $hashTable[$_.Name] = $_.Value }
    $suppress = Add-PnPListItem -List "TARGET_LIST_TITLE"  -Values $hashTable 
}
1

We can import the excel with the out of the box solution, no need go with coding:

OPTION 1: IMPORT USING QUICK EDIT(quick edit view).

  • Build out all the columns exactly as they appear in Excel
  • Make sure columns in a view are in the same order as in Excel (otherwise when you do copy/paste, you will get a mismatch)
  • If you are taking advantage of metadata (drop-downs), make sure to populate all the possible choices into respective columns (otherwise, when you paste, and pasted text does not match the drop-down choice, the row will be rejected)
  • Open your list in Quick Edit (Datasheet view for those in SharePoint 2010)
  • Copy the rows you want to copy from Excel Paste into Quick Edit View
  • You will notice the rows being pasted one by one. You might need to give it some time if you have lots of rows.

OPTION 2: IMPORT SPREADSHEET WEB PART

Another option that is available to import Excel to SharePoint is to use a Web Part called “Import Spreadsheet”. These are the steps to follow:

  1. Gear Icon > Add an App enter image description here

  2. Scroll down to Import Spreadsheet App. Click on it.

enter image description here

  1. On the next screen, give your new app/list a name, then choose an Excel file. Click Import enter image description here

  2. You will now notice an Excel file open up with a pop-up window where you need to select a range of cells to import. Once you choose the range of cells in the pop-up, click Import.

enter image description here

  1. The table will now be imported to SharePoint. It will go ahead and create a custom list with proper column headers and values

enter image description here

For detailed steps, please refer the below article:

3 ways to import Excel to SharePoint

Using the third party tool - SSIS package

Download, SharePoint List adapters from below link and install this integration adapter with visual studio BI.

https://sqlsrvintegrationsrv.codeplex.com/releases/view/82369

It provides SharePoint List source and destination tool in the source section of the tool.

Just configure the source and destination with corresponding URL, then the data can be imported or exported in seconds.

Reference URL for this tool:

Import data from Excel to list in Sharepoint Online site

How to programmatically import data from excel columns into sharepoint lists?

Your Answer

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

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