2

I have a excel file which has 500 records and I like to migrate those records with the same column name into SharePoint 2013 custom list?

What would be the best option? I have a very limited knowledge in Powershell.

Regards

3 Answers 3

2

I would suggest you use the quick edit mode to copy the data from excel to SharePoint. Note: You need to use IE browser to copy/paste the data.

enter image description here

You could refer to this video about copying content from spreadsheet into a list SP 2013:

https://www.youtube.com/watch?v=WsHjvL8jPMo

1

4 ways we can bring excel data into SharePoint list.

Approach 1: By importing the excel table into the SharePoint list

We can do by importing the excel table into the SharePoint list, please follow the below steps to do that :

  1. Click on the settings gear and select 'Add an app'.
  2. Search for Excel and select the Import Spreadsheet app.
  3. Give the app a name and browse to the Excelfile you want to import.
  4. Enter the URL of the SharePoint site to where you want to import the Excel table.

For details steps, please refer the below :

Import Excel Table or Range to SharePoint List

Approach 2: Import using Quick edit view (copy paste)

  • Create all Excel column in the custom list.

  • Open the custom list in the quick edit data sheet view mode

  • Copy your all excel data into the Datasheet view.

Approach 3: Directly import Excel data from Excel file itself to SharePoint list

  • In Excel spreadsheet, select the data range and Format it as a Table

  • While still in Excel, highlight the table, then choose Export from the top ribbon, then Export Table to SharePoint list

  • From the pop-up that appears, specify the URL of the site you are importing to, then give the list a name, then hit Next. You can also (optionally) check the box next to Create a read-only connection to the new SharePoint list. What it does is make your Excel physically linked to SharePoint list. Any changes in the SharePoint list will propagate down to Excel when you refresh the data there.

  • On the next screen, you will see all the columns with corresponding recognized column types (Currency, Date, etc.). Just click Finish

  • You should be getting a success message/pop-up

  • You should now see your list created in SharePoint. It will default to Datasheet view, just click Stop to view it as a normal list

For details steps please refer the below article :

3 ways to import Excel to SharePoint

Approach 4: using PowerShell coding

  • Convert the excel file into. CSV file.

  • Using the import-csv command read the file.

  • loop through this import-csv collection

  • Connect to the SharePoint list object

  • Add each item from the foreach loop of import-csv to SharePoint list.

2
  • thanks for your prompt answer, the issue is there is a custom list with some data and I need to import the excel file data into the custom list. the column names are exactly similar though. your solution works if there was no data in the list.
    – Harris
    Commented Mar 3, 2020 at 1:15
  • Ohh ok,got it :-) answer updated
    – SP 2022
    Commented Mar 3, 2020 at 1:39
0
    if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null) {
Add-PSSnapin Microsoft.SharePoint.Powershell }$web = Get-SPWeb $list = $web.lists["test"] $cnt = 0 foreach($i in Import-Csv clients.csv) { $new = $list.Items.Add() $new["Title"] = $i.Title $new["State"] = $i.State $new["Client Name"] = $i.ClientName $new["Office Code"] = $i.OfficeCode $new["Client ID"] = i.ClientID $new["Current Timesheet Format"] = $i.CurrentTimesheetFormat #$new["Approx Fieldstaff"] = $i.Approx Fieldstaff $new.Update() $cnt++ } "Added" + $cnt.ToString() + "records." $null = host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

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.