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.
Then under List Tools / List I chose Open with Access
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.
After about 20 seconds I my file was created.
I opened the file in Microsoft Access 2010 to verify that everything worked.
And just to be sure I examined the attachments for the first row
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
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.
Sunday, November 27, 2011 at 12:21PM 
