Import data from excel to sharepoint

Question

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

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. — Niranjan Kulkarni
– Niranjan Kulkarni, Commented Dec 24, 2019 at 15:23

Karthik Jaganathan · Accepted Answer · 2019-12-26 05:34:26Z

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/

Lee · Accepted Answer · 2019-12-25 01:40:00Z

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

answered Dec 25, 2019 at 1:40

Lee

4,2791 gold badge9 silver badges7 bronze badges

Add a comment |

Karthikeyan Ganesan · Accepted Answer · 2019-12-26 04:26:34Z

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/

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

Denis Molodtsov · Accepted Answer · 2019-12-26 05:56:15Z

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

Make sure your CSV file has column names that match internal names of columns of the destination SharePoint List
Make sure destination SharePoint list exists with all coluns from the CSV file
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 
}

SP 2022 · Accepted Answer · 2019-12-30 17:40:55Z

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:

Gear Icon > Add an App
Scroll down to Import Spreadsheet App. Click on it.

On the next screen, give your new app/list a name, then choose an Excel file. Click Import
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.

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

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?

Stack Exchange Network

Import data from excel to sharepoint

5 Answers 5

Your Answer

Hot Network Questions

Import data from excel to sharepoint

5 Answers 5

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions