Aidan Garnish

Collaboration Not Competition

Easily import data from CSV and SQL to a SharePoint list

CSV and SQL importer wsp file

This SharePoint feature allows you to quickly import data from a CSV file or a SQL stored procedure to any custom SharePoint list.

Once you have added the solution to your farm and activated it on your site collection an additional menu option will be available on the Action menu of each custom list that will take you to the import CSV or SQL page.

Simply select which option you want - either import from a CSV file or import from a SQL stored procedure. Next, browse to the CSV file or enter the SQL connection and name of the stored procedure and hit the Import button.

There is a check box option to delete all items from the list before doing the import which is not selected by default.

IMPORTANT - the first line of the CSV file must contain the names of the SharePoint list columns you want to import the data to. Eg. If you want to import data into a list that has two columns called Title and Description then the first row of the CSV file will be Title, Description.

If you are using SQL then the stored procedure for the example above would need to be something like: SELECT Title, Description from [TableName]

**UPDATE 16/12/2012 - I have now updated the CSV Upload portion of this feature to work as a SharePoint 2013 Hosted Solution.

Creating a custom save function for InfoPath 2007 browser based forms

I recently had an issue where it was necessary to only save some of the fields on an InfoPath form back to the form library. To do this required a combination of custom code and submission to a form library using a data connection.

The steps to do this were as follows:

1. Add a Save button to the form

2. Right click the save button and select button properties, change Action dropdown to Submit and click Submit Options...

3. In the submit options select Send form data to a single destination and choose SharePoint document library from the dropdown

4. Click Add to add a new data connection and follow the wizard to set this up to submit your form to the required library

5. Now that the data connection has been set up, select Perform custom action using Code and click the Edit Code button - this will create a submit method in the code behind file

6. In the submit method place the code to do the custom "stuff" and then submit the form to the data connection. The code will look something like:

public void FormEvents_Submit(object sender, SubmitEventArgs e)
{

           //remove values from the form that you don't want to be saved
           XPathNavigator xPnName = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:Name", NamespaceManager);
           xPnName.SetValue("");

           //submit the form using the data connection

           DataConnections["Main submit"].Execute();

           //set e.CancalableArgs to be false once form has successfully saved

           e.CancelableArgs.Cancel = false;           
}

To give the form a unique filename and allow updates to saved forms do the following:

1. Create a new xml node called dtNow, give it a default value of now() and uncheck the Update this value when the result of the formula is recalculated box.

2. Go to Data and Data Connections and Modify the submit data connection created above.

3. In the Filename field add concat(userName(), dtNow) and check the Allow overwrite if the file exists checkbox.

Finally, convert the data connection used to submit the form to be centrally managed as described here

Generic CSV or SQL data importer for SharePoint list

UPDATE (09/10/09) - NEW AND IMPROVED VERSION HERE

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

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.

SharePoint Governance Tools

A link to some tools to help with governance of your SharePoint deployments -

http://www.codeplex.com/governance

WSS 3.0/MOSS 2007 Tools

  •  
    • Microsoft IT Site Delete Capture 1.0 - Simply captures sites that are deleted by end users and backs them up to disk using the event model. You have a recycle bin, but this is basically a site recycle bin (available only to administrators). (Works with WSS 3.0 and Office SharePoint Server 2007)
    • MS IT Site Life Cycle Management 1.0 - Notify, Backup then Delete Unused Unneeded sites (Works with WSS 3.0 and Office SharePoint Server 2007)

SQL 2005 XQuery

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