Toad Data Point Automation Series: Blog #7 – Exporting and Importing to SharePoint

SharePoint is becoming more and more popular for content and document management. I know we use here quite a bit for storing our project schedules, architecture docs, and so on. Starting with Toad Data Analysis 3.1 we offer exporting and importing to SharePoint lists. SharePoint lists can easily be treated as a dataset. I happen to use this every month by exporting a report that shows all of the connection types that were used by Toad Data Point. Let me show you how to do this.

First of all we only support version 2010. You will need to set up the URL to your SharePoint server by going to your options. Under Environment | SharePoint enter your Server URL and press test. If the server is confirmed you will get an acknowledgment. If not the error message is rather generic and is usually an incorrect path or privilege error.

So let’s see how I export my SQL Results to SharePoint. First let’s go to the Export Wizard and enter a SQL Query. From the export type list choose “SharePoint List”, which is the last one in the list.

Next you will see the existing lists. You can choose to append to an existing list or create a new one. Here I choose to drop and create the list of exists and create a new one.

Click on through to export. To export this list again or to use in Automation you will want to save this Export template. This is a great way to export small reports.

To Import data from a SharePoint list, open up the Import Wizard. This will import to the current connection. If you don’t have privileges to a sand box database, you can import to the Toad Sample Database or Local Storage Database. Here you choose the SharePoint Import type.

On the left you will see all of the available lists and a preview of the data.

Select your list and columns. Again, make sure and save a template to use in automation.

Now to automate the exporting and importing from SharePoint, just go to the Automation designer and add an Export Wizard activity. Browse and select your template. For import choose the Import Wizard and select your import file. Press run to execute, and WaLa, you have automated your data to and from SharePoint.