Move list items with attachments between SharePoint farms: Access to the Rescue!

Update 8/27/2017: I updated the title of this post to avoid confusion about which versions of SharePoint and Access can be used. I also added a screenshot to this post showing where to find the option to import data from a SharePoint list using Access 2016 (it’s buried a little deeper in the UI than it was in Access 2013). I also verified that all the other steps in this article remain the same for Access 2016. One user posted an issue in the comments with the exported list displaying a “something went wrong” error in either classic or modern view, but I was unable to reproduce this with the list I was using to test. There are some documented issues (see section towards bottom of article) that will force a list to render using the classic experience, usually due to the data types of one or more fields in the list.

Original post appears below.

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:

blankdesktopdb

I gave the database a name and pressed Create:

newdb1

Time 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:

importsp1

Importing a SharePoint list using Access 2013.

In Access 2016, select New Data Source from the Import section of the ribbon, then choose From Online Services and then SharePoint List:

Importing a SharePoint list using Access 2016.

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

importsp2

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

importsp3

Access 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.

importsp4

When 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!

attachments

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

attachments2

Time 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:

exportsp1

Much 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.

exportsp2

I 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.

finalexport

Mission accomplished! Thanks, Access 2013!

  • irving palma

    AWESOME!! i had never imagined that was possible in Access. You save my live from learning and developing a new application in Microsoft .NET. Thank you so much broh.

  • Michael Claypoole

    I have some slight different conditions but think I may be able to use your approach. Frist off I’m only concerned with one of the 3 db (lists) on my 2007 site. We did a preliminary migration to get acquainted with SP 2013. We found the ID # did not migrate to 2013 and is out of sync because some items on the list were deleated. We also found that if the person no longer was on the site users list, it would substitute the name of the person doing the migration in fields like Created By, Updated By, etc.(we’re using the HP SharePoint Migration tool and both the 2007 and 2013 sites were created using the free version of SharePoint).
    My question is do you think this Access process would solve the name change issue (maintain the original Created By / Modified By names ever though they weren’t still with the Company)? I think there’s a better chance of that working than populating the ID# SP auto-generates from 2007 to 2013 and have the 2013 assign the next available #.

    • The built-in Created By/Modified By fields will be populated with the individual doing the export from Access (see the last screenshot in my post). You may want to look into other strategies (maybe something with custom code and/or calculated columns) to try to capture this information and bring it over in 2013.

      • Michael Claypoole

        Thanks for your quick response to my question Danny. It helped me move forward with our migration plan. I didn’t wait until today to read it but this seemed like the first I’ve had time to Thank You for the reply and information.

      • Hector Delgado

        When you meant new Blank Desktop Database is it in 2013 SharePoint? I’m using 365 version can you show me where can I view this.

  • Matej Križnjak

    I have a problem, when i migrate custom list from 2007 on 2013 sharepoint it creates me a tasks list, not custom list. How to solve that?

    • Sorry, I haven’t seen that problem before. Have you tried several different lists? They all end up going into Tasks lists?

  • My access button isn’t available, it is grayed out. Suggestions?

  • M.Solcia

    I have the exact same scenario and till now I haven’t solution due to the attachments! I’m going to follow the procedure in some weeks. It seems really promising! I will come back to give my feedback. Thanks in advance for sharing

  • MS

    thanks! however I cannot open attachment. nothing happened when I click on the paperclip.

    • In Access? Did you try double clicking it?

      • MS

        hi danny, I managed to change the view by editing the webpart properties and could access the attachment by opening the item. I still cannot open using the paper clip but it is ok. initially, none of the fields could be clicked.

  • AD

    I’ve been able to use this solution for everything except an Announcements list on a site page. The other custom lists on the page imported perfectly. Would greatly appreciate any suggestions.

    • Hmmm, I had only tried this with custom lists. What kind of problem is the Announcements list giving you? Everything moving over except the attachments, or something else?

      • AD

        It’s telling me it can’t find the file. It works perfectly for a similar Announcements list on a subsite.

        • That’s really strange. You may need to check the ULS logs to see if there is any amplifying information about the “File not found” error.

          • Joris D

            I also got a ‘can’t find the file’ error when importing a custom list in Access. Came down to 1 list item with an attachment that got a ‘error 500 – not found’ referral. When deleting this 1 attachment from the list (with + 1700 entries), everything imported fine.

  • carlton flintoff

    Great resource !
    Thank you for sharing this with us.
    Though I will add one more informative article which I was reading few days ago – https://www.lepide.com/blog/move-a-list-from-sharepoint-2010-to-sharepoint-2013/