Tag Archives: import

Move List Items with Attachments between SharePoint 2007 and 2013: Access 2013 to the Rescue!

I was recently faced with the unenviable task of moving the full contents of a custom SharePoint list with about 60 columns (including attachments totaling about 400 MB) between a hosted instance of SharePoint 2007 and SharePoint Online. While there is no shortage of options for getting data from a list in one environment into another, I was constrained by the fact that this was a cloud-hosted instance of SharePoint 2007 where I had no server or administrator-level access. I went through a mental checklist of my options:

  • Save the list as a template with contents – Nope. Way too big with those 400 MB worth of attachments.
  • Export the list to Excel – Nope. Only includes the fields defined in the list view and does not include attachments.
  • Create an empty version of the list in the destination environment, then use datasheet/quick view to copy data between the two lists – Nope. Again, the attachment problem (not to mention how tedious it would be to create identical views that would support datasheet/quick view in both environments).
  • Write code to programmatically handle the export from SharePoint 2007 – Eh. With no server-level access in the cloud-hosted SharePoint 2007 environment, writing full trust code with the server object model was off the table. Theoretically I could have written code that leveraged SharePoint 2007’s .asmx web services, but I only wanted to go there as a last resort.

Then, as if by providence, I accidentally clicked to open Access 2013 when I actually meant to open Excel. I knew SharePoint 2013 had the hooks to import and export data using Access, but could I somehow use Access to pull the data out of SharePoint 2007? I clicked to create a new Blank Desktop Database and started my journey: blankdesktopdbI gave the database a name and pressed Create:

newdb1Time to start poking around. I went to the External Data tab, and clicked More to reveal SharePoint List in the “Import & Link” section of the ribbon:

importsp1I entered the URL to my SharePoint 2007 site and chose to Import the source data into a new table in the current database.

importsp2I selected the list I wanted to export and clicked OK.

importsp3Access then proceeded to lock up my computer for several minutes as it imported the data, but it was worth the wait. Keep in mind that although logically we are exporting the data from SharePoint 2007, from Access’ perspective we are importing this data into Access. From here, we will export this data to a list in our SharePoint Online site.

importsp4When the import completed, I clicked Close and noticed in the “All Access Objects” view in the left pane, my database now contained a table named after the SharePoint list I just brought over. I double-clicked it and sure enough, all the data was there including the list item attachments!

attachmentsStunned by my good fortune, I double-clicked some of these cells to confirm the attachments were actually there. They were!

attachments2Time to get this data into SharePoint 2013. I went back to the External Data tab, and clicked More to reveal SharePoint List in the “Export” section of the ribbon:

exportsp1Much like the import wizard earlier, the export wizard allows me to connect to a SharePoint site (I chose my SharePoint Online site this time) and specify a name for the new list.

exportsp2I pressed OK and allowed Access to do its thing (it took about 15 minutes). When it was done, my browser opened to the newly imported list in SharePoint Online. Not only was the list schema perfect (if you’ve ever imported a spreadsheet to create a new list, you know how frustrating it can be to have all of your Choice columns converted to Single line of text), but all the attachments to the list items were there as well!

You will notice a few extra columns are created as part of the process:

  • Encoded Absolute URL
  • Item Type
  • Path
  • URL Path
  • Workflow Instance ID
  • Comments
  • File Type
  • _OldID

You can safely remove these from your new list if you do not want or need them.finalexportMission accomplished! Thanks, Access 2013!