We’re sorry, but we no longer offer the Data Masher tool. Here are some alternatives: Our BCS Meta Man tool allows you to create BCS external content types which can then be used to create external lists. Our Data Viewer web part for SharePoint 2013+ or SharePoint Online allows you to display and style data from external data sources within a web part on a SharePoint page.
Importing data into a SharePoint List is a common request. Data lives in all kinds of different places such as text files and database tables. Getting it into SharePoint not only allows your users to interact with it in their familiar environment but take advantage of it being in SharePoint by using it as lookup data or setting up alerts and workflow against it. SharePoint 2010 does have the great new feature called External Lists. These lists look like normal SharePoint lists, but the data is actually stored in an external system and presented in SharePoint through the Business Connectivity Services. While External Lists are very cool and useful, they still miss some important features such as workflow and alerts.
Another request we have gotten a few times centres around how to mix external data and SharePoint data together. For example you may have a database table that has event information in it. How can you display that event information on a SharePoint calendar and allow your users to add their own events around it. Previously this was something you’d need to write custom code around – but now it is something you can use the Lightning Data Masher for.
In this example we are going to make use of a simple Events table:
Once the Lightning Data Masher has been deployed and activated create a standard SharePoint Calendar. Every SharePoint list has a column called ID which is an integer. This value can be set programmatically, or it will auto increment as new rows of data are added. We need to map our database tables primary key to a column in our SharePoint Calendar list. If we were to map our EventId table column to the ID SharePoint list column, we wouldn’t be able to add in SharePoint list items alongside the imported data as there is a strong chance that our SharePoint list data would get overridden when new data is added to the database table and the import job is run again. So what we need to do is add a new custom column to our SharePoint Calendar of type number, and call it something like DBEventId. We’ll then map this column to the EventId from our database table and it’ll be used for the import jobs.
Now we can setup our Import job. When viewing the calendar click the Actions button, and click the new Create an Import link
You’ll now be taken to an admin page where you can setup your import job. First thing to do is enter a connection string for your SQL database
once you’ve entered the connection string click the Connect to Data Source button. Using the drop down that now appears, select the database table from where you want to import data
You can now enter a title and description for your import job
The next option allows you to choose to create a new SharePoint list to import to, or so use an existing one. As we want to use a SharePoint calendar, which is a special type of list within SharePoint, we’ve already created it and will therefore use the existing option
Now you’ll be shown a drop down of existing SharePoint Lists that you can use. Select the calendar from the drop down. We now need to map which database columns will map to our SharePoint list. Click the button to ‘Add Column Mapping’ for each database column you want to map to the SharePoint list. Make sure when you map DBEventId to EventId you click the checkbox to let our Import Job know that this is the primary key field.
Finally you need to select how often you’d like the import job to run. The setting for this will really depend on how often the data in your external system changes.
Upon clicking the Save button your import job will be setup and run within the next 5 minutes (or whatever frequency time you selected). If we navigate to Site Actions –> Site Settings, you’ll see a new link in the Site Administration section for ‘Manage List Jobs’. If you click on this you’ll get an overview of the Import and Export jobs running against this site. You can also manage and delete the jobs from here.
If you check out the image below you’ll see our SharePoint Calendar with the List Import job run against it. The data that is displayed has been imported from our Events database table.
We can now go in and add our own events into the SharePoint Calendar as we normally would do, and because we setup the DBEventId column in our list our SharePoint List Import job will not overwrite the data…
Hope this walk through was useful and shows how quickly you can now get your database data into SharePoint lists.
<nick/>
