Tuesday, November 09, 2010

Download any Umbraco document type’s values as crosstab/Excel table

In a follow up to my post yesterday -How to download Umbraco content properties into a crosstab table this is the follow up SQL Script that makes it even easier to download any Umbraco document type into Excel.

This SQL Script is fairly simple, basically what it does is it gets the properties associated with the specified document type and then pivots the values so you end up with a table of data that looks like this:

How to use the script

All you need to do is set the parameter "@ContentTypeId" to the document type you want (as in my previous post you can get this by checking out the link on the document type).

Once you set the id, just run the script and voila there's the data.

If you run the code and get "Command(s) completed successfully" then you've not set the id right so double check and try again.

The Script

DECLARE @cols NVARCHAR(max), @ContentTypeId int
SET @ContentTypeId = 1074

SELECT  @cols = STUFF(( 
	SELECT DISTINCT TOP 100 PERCENT
        '],[' 
        + CONVERT(varchar, Name + ' (' + CONVERT(varchar, id) + ')', 255)
    FROM
		dbo.cmsPropertyType
	WHERE
		contentTypeId = @ContentTypeId
    ORDER BY
        '],[' 
        + CONVERT(varchar, Name + ' (' + CONVERT(varchar, id) + ')', 255)
    FOR XML PATH('')
), 1, 2, '') + ']'
--SELECT  @cols

DECLARE @query NVARCHAR(max)
SET @query = N'SELECT Id, ' + @cols + '
FROM
  (
		SELECT
			CONVERT(varchar, t.Name + '' ('' + CONVERT(varchar, t.id) + '')'', 255) As [PropId],
			contentNodeId As [Id],
			ISNULL(dataNvarchar, ISNULL(CONVERT(varchar, dataDate), ISNULL(CONVERT(varchar, dataInt), dataNtext))) As [Value]
		FROM
			dbo.cmsPropertyType t LEFT JOIN dbo.cmsPropertyData d ON t.id = d.propertytypeid
		WHERE
			contentTypeId = ' + CONVERT(varchar, @ContentTypeId) + ' 
) p
PIVOT
(
	MAX(Value) 
	FOR PropId IN ( '+ @cols +' )
) AS pvt
ORDER BY Id ASC'

--PRINT(@query)
EXECUTE(@query)

Download any Umbraco document type’s values as crosstab/Excel table

Useful Links:            Comments [0]  Trackback

Tags: SQL | SQL Server | The Site Doctor | Umbraco | Web Development

 Monday, November 08, 2010

Download Umbraco content properties into a crosstab table

Have you ever needed to get a download of your Umbraco content nodes in a cross-tab query e.g. download contact form data from Doc2Form? Using Umbraco's Contour product makes this a breeze but what about older systems? Thankfully, it's not actually that difficult.

We have a contact us form on one of our sites which uses an old version of Doc2Form which emails the customer details of the enquiry. One benefit is it also saves it to the recycle bin as a document with the name: "RE: SYSTEM DATA: umbraco master root". With that in mind, we can use SQL Server's PIVOT functionality to pull the data out in a nicely formatted manner.

Firstly you'll need to know the id's of the document type's properties, there are numerous ways to do this:

1. Just look at the cmsPropertyData table for a couple of content nodes (I could spot the ones I was after fairly easily)

2. Query the cmsPropertyType table:

Find the contentTypeId of the document type -you can do this by hovering your mouse over the document type in the tree and checking out the status bar (you can see the contentTypeId in brackets in the bottom left -mine here is 1074):

Once you have the contentTypeId of the document type, you can then get the ids of all the properties you're after by replacing "xxx" with your property id in the following script:

SELECT * FROM dbo.cmsPropertyType WHERE contentTypeId = xxx

3. Get it from the source of the document type editor

An alternative way is to examine the HTML of the Document Type editor. If you view the source on the "Generic Properties" tab and scroll to the section you're interested in (there'll be a h2 with the same name) you will find a ul that has the class of "genericPropertyList".

Each li of that ul will have the relevant id as part of it's id e.g. for a section called "Enquiry Form" the id will be: "EnquiryFormContents_49" where "49" is the id we're interested in. You can see mine (49, 50, 51 and 52 below):

Once you have these ids to hand (mine were 49, 50, 51 and 52) you just need to update the code below and run against your Umbraco database:

SELECT
	contentNodeId As [Id], 
	[49] As [Name], 
	[50] As [Telephone], 
	[51] As [Email Address], 
	[52] As [Notes]
FROM
(
	SELECT contentNodeId, propertytypeid, ISNULL(dataNvarchar, dataNtext) As [Value]
	FROM dbo.cmsPropertyData
) As src
PIVOT (
	MAX(Value) 
	FOR propertytypeid in ([49], [50], [51], [52])
) aS pvt
WHERE [50] IS NOT NULL OR [51] IS NOT NULL
ORDER BY contentNodeId

That will then produce some lovely formatted data for you, my example above produced:

It's also possible to automate this entire script so you don't need to find out the property ids, I'll post that separately.

Download Umbraco content properties into a crosstab table

Useful Links:            Comments [0]  Trackback

Tags: Development | SQL | SQL Server | Umbraco