Compartir a través de

HOWTO: Bulk Load Data Into SharePoint Lists via Access 2007

  • Artículo

Hi all,

          Before writing something that would take some time to do in terms of loading data, I was able to successfully conduct a bulk load of data into SharePoint with MSAccess.  Here is my mock up test that I did. 

I created a SharePoint list in a sample site with the following schema:

Sample List

Title                                 Single Line of Text

Sample Column                  Single Line of Text

Sample Lookup Column       Lookup to a Contacts List

I chose these columns because they represent some non-standard column types of mapping in order to do bulk loads with.  I then created a new Access 2007 database and linked to the [Sample List] in SharePoint. 

I then created a mirror data load table in the access database with the following schema

Sample List Local Source

ID                          AutoNumber

Title                       Text

SampleColumn         Text

SampleLookupValue  Number

Note that the source data for inserting into a SharePoint list lookup column via Access is a number (the actual ID of the specified target value of the Lookup list).

I then populated the local list with 100 rows of sample data.

clip_image002

I then created a Append Query that would take the rows from the [Sample List Local Source] and put it into the linked table to the target SharePoint list.  The query for the [Sample List Load] is below:

clip_image004

Upon running the query, and refreshing in the browser, you can see all 100 rows in the list.  The query took about 10 seconds to run.  I would suggest you bulk load in batches to avoid a really long running operation.

Here are the results:

clip_image006

I would also suggest that we make a default view on the list that limits the data to rendering a max of 2000 items so you don’t’ incur big perf hits when viewing the data.

This list will help in creating the source table and its data types to map to the target SharePoint list.

https://office.microsoft.com/en-us/access/HP010477131033.aspx

Comments

  • Anonymous
    December 17, 2009
    How do you create a SharePoint list in a sample site? And why are the columns in the sample list non-standard?