Ever had to merge SharePoint lists? Typically you can open them in Excel, copy & paste from one sheet to another and bob’s your uncle. Unfortunately this doesn’t work in all cases, especially when attachments are involved or the data uses lookup columns.

I’ve been playing about with MS Access 2007. It has a handy feature in that you can open SharePoint lists directly, as shown below:

You can then get to do pretty cool things with the list as it’s pretty much a DB table within access, albeit still linked to SharePoint.

It’s simply a case of using the ‘External Data | SharePoint List’ option to open the lists you need merged and copying/pasting from one table to another. There’s a couple of caveats here to be careful of though, namely:

• Your content types have to match across lists, otherwise it doesn’t work. You’ll have to switch into the query designer and map the columns manually.
• Beware of lookup columns. They’ll cause a ‘data integrity error’ if your data doesn’t match correctly. What I tend to do is paste a row in at a time and manually select the lookup value from the relevant column. This seems to sort it.

The plus side of using MS Access is that you can build up queries and reports from your SharePoint list data. Pretty powerful and not often mentioned!