I finally got bored of rewriting the same console application to move data from a csv or a sql table into a SharePoint list! The result is a generic console application that is capable of importing either a CSV file or some SQL data from a stored procedure. The app uses the object model so it needs to be run on the SharePoint server farm. I may get round to rewriting it using web services at some point. The CSV import parses the file so that things like line breaks and commas within fields are handled nicely. Any blank fields in the CSV do need to be filled using find and replace on blank space otherwise data ends up in the wrong columns.

To import a CSV the first row of the of the CSV file is used to define which columns the data will import to. Eg. A CSV that has one column with a header of Title will import the data into the Title column of the specified list. Modify the App.Config file to include the URL of your site collection, the list name, the path to the CSV and whether or not you want to delete all items in the list before importing.

To import SQL data create a stored procedure that returns the data as the names of the site columns

Eg. Select Name AS Title from tblPerson - will return one column of data that will import into the Title column of a list. Modify the App.Config to include the name of the stored procedure and the SQL connection string and run the app.

You can download the project from the following link:

AG.GenericSharePointListImporter.zip (41.72 kb)

This application is supplied as is and offers no guarantees feel free to use as you wish all I ask is that if you make any improvements then you share them in the comments or by email.

I have recently been looking at using the SQL 2005 XML column to store data that will eventually be used to create web pages in MOSS 2007. The first issue I faced was how to get the XML out of the column!

The first attempt involved reading it out as a string and using the System.Xml namespace in C# to manipulate the string, get nodes etc. I felt like there must be a better way and eventually came across the following article on XQuery:

http://www.15seconds.com/Issue/050803.htm

For those that don't want to trawl the whole article the syntax for selecting an XML data point is as follows:

Select

[XML Column Name].query(‘data([path to data point])')

From [Table Name]

So in my specific case this would be:

Select

Attributes.query('data(//product/volumesolid)') as volumesolid,

Attributes.query('data(//product/dft)') as dft,

From Product

Another useful article to understand when to use the XML column in SQL is ‘XML best practices with SQL 2005’ - http://msdn2.microsoft.com/en-us/library/ms345115.aspx