Export Umbraco members as CSV/table

A while ago I blogged about how to list umbraco document types as an Excel table which has come in handy a few times. Today I thought I’d blog about how to list Umbraco members in a CSV format with their properties as column headings.

If you’ve ever needed to export your member data from Umbraco this is a handy little script:

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);

SET @cols = STUFF((
            SELECT ',' + QUOTENAME(pt.Name)
            FROM
                (SELECT [Id] FROM dbo.umbracoNode WHERE nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43') AS mt
                LEFT OUTER JOIN (SELECT NodeID, contentType FROM dbo.cmsContent) AS ml ON ml.contentType = mt.id
                LEFT JOIN dbo.cmsPropertyType AS pt ON pt.contentTypeId = ml.contentType
            GROUP BY pt.sortOrder, ',' + QUOTENAME(pt.Name)
            ORDER BY pt.sortOrder ASC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = '
SELECT
    LoginName,
    Email,
    createDate,
    MemberGroup,
    ' + @cols + '
FROM
(
    SELECT
    pt.Name AS MemberFieldName
    ,    ISNULL(CASE
            WHEN dt.DBTYPE = ''Ntext'' THEN CAST(d.[dataNtext] AS NVARCHAR(MAX))
            WHEN dt.DBTYPE = ''Nvarchar'' THEN d.dataNvarchar
            WHEN dt.DBTYPE = ''Date'' THEN CONVERT(nvarchar, d.[dataDate])
            WHEN dt.DBTYPE = ''Integer'' THEN CONVERT(nvarchar, d.[dataInt])
            ELSE NULL
        END, NULL)
        AS MemberData
    ,    m.LoginName
    ,    m.Email
    ,    n.createDate
    ,   g.[Text] AS MemberGroup
    FROM
        (SELECT [Id] FROM dbo.umbracoNode WHERE nodeObjectType = ''9b5416fb-e72f-45a9-a07b-5a9a2709ce43'') AS mt
            LEFT OUTER JOIN (SELECT NodeID, contentType FROM dbo.cmsContent) AS ml ON ml.contentType = mt.id
            LEFT JOIN dbo.cmsPropertyType AS pt ON pt.contentTypeId = ml.contentType
            LEFT JOIN [dbo].[cmsDataType] AS dt ON pt.datatypeID = dt.NodeId
            LEFT JOIN dbo.cmsPropertyData AS d ON d.contentNodeId = ml.NodeID AND d.propertytypeid = pt.id
            LEFT JOIN dbo.cmsMember AS m ON m.NodeID = ml.NodeID
            LEFT JOIN dbo.cmsMember2MemberGroup AS xmg ON xmg.Member = m.NodeID
            LEFT JOIN dbo.umbracoNode AS g ON g.id = xmg.MemberGroup
            LEFT JOIN dbo.umbracoNode AS n ON n.id = m.nodeId
) As src
PIVOT (
    MAX(MemberData)
    FOR MemberFieldName in (' + @cols + ')
) aS pvt
ORDER BY LoginName'

print(@query)
execute(@query);