2

I have a excel sheet whose columns and my sharepoint list columns are same. But when i try to export from excel this doesn't works. I have some specific column types which cant be altered because i have a form. Any other way to populate the list with excel data? PS- I don't want to create a new list !

2
  1. Go to your list
  2. Create a view with same columns than your Excel
  3. In this view, go to Ribbon / List / Quick Edit

enter image description here

  1. Copy your data from Excel
  2. Paste your data in SharePoint using Quick Edit
  • Can you please specify where is this - Ribbon / List / Quick Edit? – OM-ॐ Jun 19 '17 at 11:50
  • I added the image – Juan Pablo Pussacq Laborde Jun 19 '17 at 11:54
1

I cobbled together a CSOM script a while ago that does this and works a lot better than copying and pasting data in in Quick View. Note to run this you'll need to import the Microsoft.Sharepoint.Client namespace, I always run it in the Sharepoint Online Management Shell.

//Set filename

$file = "local path to your Excel file"

// Set the sheet name to use

$sheetName = "Sheet1"

// Create Excel COM object

$objExcel = New-Object -ComObject Excel.Application

// Open the file

$workbook = $objExcel.Workbooks.Open($file)

// Open the sheet

$sheet = $workbook.Worksheets.Item($sheetName)

// Make sure the window is hidden (remove this if you need to make sure Excel is opening with the right file)

$objExcel.Visible=$false

// Get the number of rows

$rowMax = ($sheet.UsedRange.Rows).count

// For each column you want to get, set an X and Y start position (i.e. row and column). Create these two variables for every piece of data being loaded in (in the example below my cross reference is row 2, column A)

$rowDataSet1=2
$colDataSet1=1

// Set Sharepoint variables for your site

$Site = "site collection url"
$DocLib = "list name"
$name = "username@tenant or domain\username"
$pwd = "password"

// Open site context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($Site)
$Credentials = New-Object System.Net.NetworkCredential($name, $pwd)
$Context.Credentials = $Credentials

// Load the list

$List = $Context.Web.Lists.GetByTitle($DocLib)
$Context.Load($List)
$Context.ExecuteQuery()

// Loop thru each row - this is where you put in your cross reference variables you created before, using them to assign the cell contents to a new variable

for ($i=1; $i -le $rowMax; $i++)
{
$valofDataSet1 = $sheet.Cells.Item($rowDataSet1+$i,$colDataSet1).text

// Create and populate list item - remember you need to use the STATIC NAME of the field, not the DISPLAY NAME (edit the column and inspect the URL to get this)

$ListItemCreationInformation = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$NewListItem = $List.AddItem($ListItemCreationInformation)
$NewListItem["ColumnStaticName"] = $valofDataSet1

$NewListItem.Update()
$context.load($newlistitem)
$Context.ExecuteQuery()
}

// Quit Excel

$objExcel.quit()
  • 1
    If you do use this, make sure to strongly type your data variables as sometimes CSOM can throw errors if there is a mismatch between what the shell thinks the data type is and what SP expects in the column. – Thomas Gass Jun 19 '17 at 12:09

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.