Friday, March 11, 2011

Export Umbraco members as a CSV

Last week I had to write a tool to get Umbraco members as a CSV file.

I started looking into the database tables and decided very quickly that raw SQL was going to be a pain. So I used Linq to SQL :)

This is a quick example of what was required.
There are a few things you have to be careful about. Look at what order your member properties are in. For instance, in my example I know that data[0].dataNvarchar is the first name. I used the dataNvarchar property because I know it's a string. For data[3] I used the dataInt property because it was a bool.

Here's the code.

public string GetMemberCSV()
MyDataContext db = new MyDataContext();
var members = (from m in db.cmsMembers select m);
StringBuilder sb = new StringBuilder();
sb.Append("Firstname, Lastname, Email, Opt-In, Verification Id, Verified, Created\n");

List emails = new List();

foreach (var m in members)
// get the properties
var data = m.umbracoNode.cmsPropertyDatas;
var firstName = data[0].dataNvarchar;
var lastName = data[1].dataNvarchar;
var email = data[2].dataNvarchar;
var optIn = data[3].dataInt ?? 0;
var verificationGuid = data[4].dataNvarchar;
var verified = data[5].dataInt ?? 1;
var created = m.umbracoNode.createDate.ToString();

if (!string.IsNullOrEmpty(email) && !emails.Contains(email))
sb.Append(string.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}\n", firstName, lastName, email, optIn, verificationGuid, verified, created));

return sb.ToString();

I hope that was helpful.