Skip to main content

How to bulk copy from Excel to SharePoint List

There will be scenarios where you would want to copy thousands of rows from Excel to existing SharePoint List in a site. There are a couple of ways this can be achieved without writing a code. This post explains both these approaches and also explain on which is the better option for huge number of data.

Approach 1: Using DataSheet View (Quick Edit in SP2013)
In this approach, you have to switch to Quick Edit view of List. Then, you can copy data from Excel to Quick Edit view (provided the order of columns are same).


This approach is most straight-forward and best suited for few hundreds of rows in Excel. If number of rows increase, this approach becomes more time consuming and also sometimes browser becomes non-responsive.

Approach 2: Using MS Access
In this approach, you will make use of MS Access to copy data from Excel to List. What I have observed is, this approach is pretty fast compared to Quick Edit and MS Access handles thousands of rows.
Navigate to List View. Under List tab, click on "Open with Access" link as shown below.



In the dialog box, select the physical path where you want to save Access DB file and ensure that "Link to data on the SharePoint site" option is chosen and click OK.

Now, MS Access opens and in the left side, you can see an Access table with the same name as List. Double-click on that table to open in the right-pane.

Now, select the new row in Access (in screen shot above, cell just below "Row 5"). Here, ensure that you have selected the entire cell (It should not be in type or edit mode).

Now, copy the data from Excel and just do Ctrl+V (paste) into Access. When you get prompt for confirmation, click Yes. Wait for some time depending upon volume of your data and it'll display all the rows copied.

Now, go back to your SharePoint List and refresh the browser. Your data is reflected here!
Another beauty of this approach is, if there is any error for specific rows, Access prompts it and it continues with loading of other rows. It stores problematic rows into a separate table in Access for analysis.
Labels:

Comments

  1. AnonymousOctober 31, 2014 at 3:42 AM

    How did you cut and paste from excel to a SharePoint list in option 1.?

    ReplyDelete
  2. SureshOctober 31, 2014 at 12:36 PM

    Select the rows in Excel (not entire row but specific rows and columns), Copy, switch to portal which has datasheet view, point the mouse icon on first column on the blank row, do Ctrl+V.

    ReplyDelete

Post a Comment

Popular posts from this blog

Document ID Service - New Feature in SharePoint

When I explored this new feature of SharePoint in Online (this feature is available in SharePoint 2013 also), I found it interesting because it solved one of the common problems people faced in SharePoint. I will first explain about this feature and I'll cover the problem and the solution in another post. This feature allows Site Collection Admin assign a unique ID to every document in the site collection out of the box. If you recall, there was no out of the box way to assign a unique number to the documents across libraries in a site collection. Yes ID column is unique but only within a library. Document ID column contains a number which is unique across libraries. To start with, let's have a look at a typical document library. This is a familiar view for SP guys. By default, you don't get Document ID column in a library. All Items View To get Document ID column, you need to activate a site collection feature "Document ID Service" as shown below. S...
Post a Comment
Read more

How to update Person field with multiple values using REST API

Person or Group field in SharePoint is similar to a Lookup field. When you are updating this field using REST API, you need to append "Id" to the name of the column in the body construct. For example, the body construct looks like this: data: { "__metadata": { "type": "SP.Data. ListName ListItem" }, "Title": "First Item", " PeopleField Id": "4" }; The highlighted portions should be replaced by the actual List Name and Column Name. In the above example, the REST call is updating a List item with Title and People columns. How to get the value for user ID ("4" in the above example) needs a separate explanation and that will be my next post! The above example works fine if Person field is configured to accept only one value. If we change the Person field to accept multiple values, how do we pass more than one value in the REST call? Since we normally separate user names with semicolon in peop...
12 comments
Read more

Restrict Future or Past Date in List

Many a times you will come across a requirement wherein you need to restrict user from entering future or past date in a List or Document Library Date column. Developers are inclined towards writing either event handler or javascript to achieve this. But, hold on! You can achieve this without writing a single line of code. I have a list which stores Employee details like Employee Number, Name and Date of Joining. Here I need to enure that Date of Joining will not accept future date. Navigate to List Settings -> Validation Settings. Here you can enter the formula to restrict future date as shown below. If you change the logical operator, it will restrict past date. Also, you can put message which will be displayed to user. Now, when you try to enter future date and try Save, you will see the validation message. So, next time you have similar requirement, you know where to try first before jumping into coding!
14 comments
Read more