Sunday, January 21, 2007

Adding data to Sharepoint Lists in bulk from database, Excel files

Many a times, it is required to import data in sharpoint lists from sources like SQL Server, Access db
or Excel files.
Here I will outline the steps required to import data in list from these sources. We can even link a sharepoint
list to SQL database, so that updates made to one are relfected in other and vice versa.
This is achieved through linked table feature.
  • Lets fire up Access 2007 and create a new blank access database.

  • Go to External data ribbon and we will choose ODBC to link this table to the SQL Server database.

  • Choose link to data source by creating a linked table

  • Choose a DSN for your data source or create a new one.

  • Once you get the table into Access 2007, right click the table and Choose Export -> Sharepoint List

  • Specify the address of sharepoint site and the name of the list you want to create and you are done :)

  • Sharepoint will itself create the site columns and select the data types for these columns based on the data types specified in the table.
Here we have imported the data into list from SQL server database. This can be extended to XML files, Excel files or even the Access database , paradox or even the Lotus database :)
Not to mention, just like import, you can export the data from sharepoint list to Access, SQL Server etc.....

5 comments:

Doug Whitby said...

Great. Can you create a list that links to a query on a SQL database?

Anonymous said...

"so that updates made to one are reflected in other and vice versa"

Are you sure about this? Its been my experience that when you use access to import into sharepoint from SQL that after the initial import there is no linkage between the two.

Brian said...

I keep getting the following error:
"Reserved error (-7748); there is no message for this error."

Trying to connect to a Sybase database via ODBC.

Georg Winkelhofer said...

You said: Not to mention, just like import, you can export the data from sharepoint list to Access, SQL Server etc.....

How do you export lists into a SQL Server or an Oracle database? Searching in the community I always found exporting data into Sharepoint

Christina said...

You said that "We can even link a sharepoint list to SQL database, so that updates made to one are relfected in other and vice versa."
I linked my SharePoint list to a SQL database but when I update the SQL database, it updates in Access and not in SharePoint. Is there some step that I am missing?