« Visio – Controlling Character Spacing (Screencast) | Main | Visio– Adding Shadows to Text »
Saturday
Nov052011

Batch Exporting SharePoint 2010 List item Attachments using Access 2010 and C#

Despite having one of the least exciting titles, in this blog post I’ll show you how I solved a problem we were having at work. I know this is a common problem you encounter, so I think you’ll find this solution helpful.

THE SITUATION

As you know our product (Dynamics AX) has a large set of SSRS reports - 856 to be exact. During the development of AX we asked developers to save PDFs of their reports into a SharePoint list.  We already had a SharePoint list that tracking various attributes of our reports (name, which team built it, what milestone it was due, etc.) so we simply used SharePoints ability to add attachment to list items.

Everything worked great until this week. My team needed to share all the PDFs on a fileshare so people could easily browse and examine all the reports. Of course, I went looking for a feature that doesn’t exist: “Export all attachments in list” is not a feature SharePoint has.

After researching a few days I found no features or tools that would help me. So it was clear I’d have to make my own solution.

OVERVIEW

SharePoint can export a list AND its attachments into an Microsoft Access database. That’s a start. At least he data is now out of SharePoint. And then next step was to use the DAO (not ADO) API to go through the resulting database and save each attachment. It turned out to be quite simple actually.

STEP 1: EXPORTING THE LIST WITH ATTACHMENTS

First, I created a view of our Reports list called “Attachments” that contained only the fields I needed. I added a filter to this list so that it only included those list items that had at least 1 attachment. The attachment field, the Design field and the Name field. Why the Design and Name fields? These extra fields help disambiguate the attachment names – which are often simply called “report.PDF”. The portion of the actual list is shown below.

image

Then under List Tools / List I chose Open with Access

image

image

 

The Open in Microsoft Access dialog appeared. I gave it the name I wanted and chose Export a copy of the data and clicked OK.

image

After about 20 seconds I my file was created.

image

I opened the file in Microsoft Access 2010 to verify that everything worked.

image

And just to be sure I examined the attachments for the first row

 

image

image

At this point everything looked great.

Now let’s get these attachments out.

STEP 2: GETTING THE ATTACHMENT OUT OF THE LIST

I couldn’t find a tool to do this for me, so I built my own using Visual Studio 2010.

The basic steps are simple.

  • Create a new VS2010 C# (.NET 4.0) project
  • Add a reference to Microsoft.Office.Interop.Access
  • Add a reference to Microsoft.Office.Interop.Access.DAO

 

image

 

And then use the code below. Instead of covering this line-by-line I’ll give a brief overview of the code.

The basic input consists of the filename of the Access DB, the name of the table in the DB, the name of the attachment field in the table, what folder to store the attachments in, and any fields to use as a prefix to the attachment filename.

All the code does is query the table for a recordset. For reach row, it queries the “attachment recordset” for that row. The attachement recordset contains the filename of the attachment and the binary data. All that is left to do is attach a prefix onto the the filename.

 

---

using System;
using System.Linq;
using MSACCESS = Microsoft.Office.Interop.Access;

namespace ExportAccessAttachments3
{
    class Program
    {
        static void Main(string[] args)
        {
            const string fieldname_filename = "FileName";
            const string fieldname_filedata = "FileData";


            string outputfolder = @"D:\attachments";
            string dbfilename = @"D:\\AX6Reports.accdb";
            string tablename = "AX6Reports";
            var prefix_fieldnames = new[] { "Name", "Design" };
            string attachment_fieldname = "Attachments";

            var dbe = new MSACCESS.Dao.DBEngine();
            var db = dbe.OpenDatabase(dbfilename, false, false, "");
            var rstype = MSACCESS.Dao.RecordsetTypeEnum.dbOpenDynaset;
            var locktype = MSACCESS.Dao.LockTypeEnum.dbOptimistic;
            string selectclause = string.Format("SELECT * FROM {0}", tablename);
            var rs = db.OpenRecordset(selectclause, rstype, 0, locktype);
            rs.MoveFirst();
            int row_count = 0;

            while (!rs.EOF)
            {
                var prefix_values = prefix_fieldnames.Select(s => rs.Fields[s].Value).ToArray();
                var attachment_rs = (MSACCESS.Dao.Recordset2)rs.Fields[attachment_fieldname].Value;
                int attachment_count = 0;
                while (!attachment_rs.EOF)
                {

                    var field_filename = attachment_rs.Fields[fieldname_filename].Value;

                    var field_attachment = (MSACCESS.Dao.Field2)attachment_rs.Fields[fieldname_filedata];
                    if (field_attachment != null)
                    {
                        if (field_attachment.Value != null)
                        {

                            string prefix = "";
                            if (prefix_fieldnames.Length > 0)
                            {
                                prefix = string.Format("{0}__", string.Join("__", prefix_values));
                                prefix = prefix.Replace(" ", "_");
                                prefix = prefix.Replace(":", "_");
                                prefix = prefix.Replace("/", "_");
                            }

                            var dest_fname = System.IO.Path.Combine(outputfolder, prefix + field_filename);

                            if (System.IO.File.Exists(dest_fname))
                            {
                                System.IO.File.Delete(dest_fname);
                            }

                            field_attachment.SaveToFile(dest_fname);
                        }
                    }

                    attachment_rs.MoveNext();
                    attachment_count++;
                }
                attachment_rs.Close();
                Console.WriteLine(row_count);
                row_count++;
                rs.MoveNext();
            }

            rs.Close();
        }
    }
}

---

PARTING THOUGHTS

This was a fun small coding project – it took maybe all of 1 hour to research the information and build the project. Still, It would be nice if this was a built-in feature of SharePoint.

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments (4)

Hello - this is great - my team has this same dilemma and thanks for posting. Can this be run as a VBScript directly in Access or is Visual Studio the only way?

March 12, 2014 | Unregistered CommenterJeff

I'm sure this can be done using VSTA.

May 28, 2014 | Unregistered CommenterEduardo

Why didn't you try using the Open with Explorer function on a library first?
Simple steps I used without going through a lot of hoops:
1. Click on a library and Open with Explorer
2. In the Address line – double click the Site name to map the site libraries and lists
3. Double click on Lists
4. Double click on the specific list name
5. Double click on the Attachments
6. Inside you will find folders corresponding to the Record ID within the list and double click on these folders

You can then set up a view with the ID sorted from 1 - end of ID # set to correspond them to each record.

Hope this helps.

July 23, 2014 | Unregistered CommenterMark

Thank you SAVEENR, it really helped me!
Best wishes :)

December 14, 2014 | Unregistered CommenterSedigheh

PostPost a New Comment

Enter your information below to add a new comment.
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>