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

How to get SharePoint List or Library GUID via REST

Sometimes, you would need List or Library GUID to use that in some operation. In such cases, how do you get hold of GUID using REST API? There is a straight-forward end point which you can use: /_api/web/lists/getByTitle('ListTitle')/Id This will return the GUID of a List or Library. There is also another approach. However this approach works only if a List or Library contains at least one item. /_api/web/lists/getByTitle('ListTitle')/items This is a familiar end point which we use to fetch items of a List or Library. To get the GUID from the response, you would do: var listIDTemp = response.data.d.results[0].__metadata.id; var listID = listIDTemp.substring(listIDTemp.lastIndexOf("guid") + 5).split('\'')[0]; As you can see, we are doing string operations to fetch GUID from metadata.id. This contains information only if the response has at least one item.
Post a Comment
Read more

All about SharePoint List View Styles

Sometimes, there are out of the box features which we tend to ignore and later when we do apply, we are more than happy about the feature which is readily available in SharePoint. One such feature is List View Style. I never thought I would write a post on this. However, whenever I spoke about this with users, people were excited to see the result. That prompted me to write this post. Instead of getting into only theory part, I will basically take use cases where these styles can be applied and also touch up on on some minor limitations with certain style. When you are creating/modifying a List view, you will get an option to select View Style. As shown below, there are 8 options available and Default is always set if you ignore this style. List of View Styles I will take typical Contact List and Announcement List to explian about these styles. Let us go one by one. Default: This view, as name suggest, is the default style in a view. This is one of the widely seen style
1 comment
Read more

Get User Id using REST or JavaScript Object Model

Sometimes you would need to fetch the User Id based on either Login name or Email id. You would need User Id if you need to assign a user object to a people picker control or People/Group field. How do we get the Id based on Email or Login Name in client side development? We can achieve that using JavaScript Object Model or REST API. Let me share the first example using JavaScript Object Model (JSOM). var context = new SP.ClientContext.get_current(); this.user = context.get_web().ensureUser(loginName or Email); var o = { d: d, user: this.user }; context.load(this.user); context.executeQueryAsync(     Function.createDelegate(o, ensureUserSuccess),     Function.createDelegate(o, Fail) ); The above code fetches User Id for a given Login Name or Email Id. Interestingly, there is no equivalent endpoint available in REST! The Microsoft documentation talk about a endpoint but I could not get it working. So what is the way to get User Id using REST? You have to use the hidd
Post a Comment
Read more