Exporting Views as Text Files

CRM has a great piece of functionality that allows you to export views into Excel worksheets, but sometimes a simple delimited text file may be all you need. I created a quick page that you can drop into your ISV subdirectory (this assumes you're using CRM 4.0, but you can convert this to 3.0 code without too much hassle) that provides this piece of functionality.

This is a pretty lengthy post, so we'll get to the good stuff after the jump...

Requirements

Since this is a mini application, we need to define our requirements. Here's what we want to acheive:

1. Create a custom web page that accepts a view's id as a parameter and spits out the records in that view in a delimited text file.
2. Allow for the delimiter to be specified at run-time.
3. Allow for the column headings to be included or excluded in the file.
4. Allow for the record id to be included in the file (this would not be shown in the view)

Input Parameters

So, knowing the above, here are the parameters that I want to be able to specify when accessing the export page:

• ViewId: pass the guid of the current view.
• ViewType: pass the object type code of the view's records.
• Delimiter: any character, but I usually use a pipe - |
• HideHeader: specify if we want to hide the columns names on a header row. This parameter is optional and defaults to false (which means the columns names are present if not specified).
• ShowRowId: specify if we want the record id of the row to be output (ie. for accounts, include the accountid column). This parameter is optional and defaults to false.

I wrote a previous entry on how to use ISV.config to create a menu that passes the id of the current view (for the view's object type code, see here). You can use this technique to pass these parameters to the export page. I'll leave it up to you to build the javascript for your ISV.config piece, I've presented all the information, but you may elect to use a button on an entity's grid or the global menu bar or whatever you wish.

Building the Page

Now let's get our page built. Since there is no interaction (just pass the variables and the page will give a single response - the text file), we don't really have any event driven stuff, other than the Page_Load event. What we do have are a few methods that are called during the Page_Load event.

Page_Load()

What we want to accomplish is this:

1. Grab and verify our parameters.
2. Create a temporary text file to contain our data.
3. Populate the text file.
4. Send the file to the user's browser.

With that in mind, here's the Page_Load() method:

//  check for valid parameters
string sViewId = Request.QueryString["ViewId"];
int iViewType = Convert.ToInt32(Request.QueryString["ViewType"]);
string sDelimiter = Request.QueryString["Delimiter"];
bool bHideHeader = Convert.ToBoolean(Request.QueryString["Header"]);
bool bShowRowId = Convert.ToBoolean(Request.QueryString["ShowRowId"]);
if (sViewId == null || iViewType ==0 || sDelimiter == null)
{
    // no viewid is given
    Response.Write("Invalid parameters.");
    return;
}

//  create service object
_oService = _oHelper.oCrmService;

//  generate temp text file
string sTempFileName = generateTempFile();
_fsTempFile = File.Create(sTempFileName);

//  populate data
populateFile(sViewId, iViewType, sDelimiter, bHideHeader, bShowRowId);

//  close file
_fsTempFile.Close();

//  output file to browser
Response.Clear();
FileInfo oFile = new FileInfo(sTempFileName);
Response.AddHeader("Content-Disposition", "attachment; filename=" + oFile.Name);
Response.AddHeader("Content-Length", oFile.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(sTempFileName);

First, we check for our input parameters. Notice that we have our optional parameters (Header and ShowRowId) structured so that if they are not specified, they default to false (null or empty QueryString items convert to false - that's important to know). Next, we check for the essential parameters, if any are missing, we just simply write an error message to the page and stop.

Once we've dispensed with the parameter validation, we create our CrmService object that will be used throughout the example. I have a helper class that I like to use to make things easy. For a good lesson on doing your own helpers, check out the SDK - there's tons of goodies in there.

generateTempFile()

Our next order of business is to create a temporary text file on the server. I do this by creating a string function that returns a unique temporary file name (it's a standard function of the Path class):

private string generateTempFile()
{
    //  generate temp file and return its name
    return Path.GetTempFileName();
}

I then create the actual file with the name we generated using the generateTempFile() function.

populateFile()

Now that we have our temp file created, we need to populate it with data. This is where the fun begins:

private void populateFile(string sViewId, int iViewType, string sDelimiter, bool bHideHeader, bool bShowRowId)
{
    //  get view definition
    string sFetchXml = "";
    string sLayoutXml = "";
    string sRowId = "";
    getViewDefinition(sViewId, iViewType, ref sFetchXml, ref sLayoutXml);
    //  create column list (and write header record)
    ArrayList alColumns = new ArrayList();
    getColumnList(ref alColumns, sLayoutXml, bHideHeader, sDelimiter, bShowRowId, ref sRowId);

    //  fetch records in view & write results
    XmlDocument oResults = new XmlDocument();
    getAllRecords(ref oResults, sFetchXml);

    writeResults(alColumns, sDelimiter, oResults);

}

We first need to get the definition of our view: we want the Fetch XML and the Layout XML. So we're going to call the getViewDefinition method to populate our xml variables. This is just a standard CRM Fetch:

private void getViewDefinition(string sViewId, int iViewType, ref string sFetchXml, ref string sLayoutXml)
{
    //  fetch view definition from crm
    string sEntityName = "";
    switch (iViewType)
    {
        case 1039:
            sEntityName = "savedquery";
            break;
        case 4230:
            sEntityName = "userquery";
            break;
    }
    //  build fetch xml
    StringBuilder sbGetFetchXml = new StringBuilder();
    sbGetFetchXml.AppendLine("<fetch version='1.0' output-format='xml-platform' mapping='logical'>");
    sbGetFetchXml.AppendLine("<entity name='" + sEntityName + "'>");
    sbGetFetchXml.AppendLine("<attribute name='" + sEntityName + "id'/>");
    sbGetFetchXml.AppendLine("<attribute name='fetchxml'/>");
    sbGetFetchXml.AppendLine("<attribute name='layoutxml'/>");
    sbGetFetchXml.AppendLine("<attribute name='name'/>");
    sbGetFetchXml.AppendLine("<attribute name='returnedtypecode'/>");
    sbGetFetchXml.AppendLine("<filter type='and'>");
    sbGetFetchXml.AppendLine("<condition attribute='" + sEntityName + "id' operator='eq' value='" + sViewId + "'/>");
    sbGetFetchXml.AppendLine("</filter>");
    sbGetFetchXml.AppendLine("</entity>");
    sbGetFetchXml.AppendLine("</fetch>");

    //  load view definition into xml object & get view's fetch xml
    string sViewXml = _oService.Fetch(sbGetFetchXml.ToString());
    XmlDocument oViewXml = new XmlDocument();
    oViewXml.LoadXml(sViewXml);

    XmlNode oViewFetchXml = oViewXml.SelectSingleNode("/resultset/result/fetchxml");
    XmlNode oViewLayoutXml = oViewXml.SelectSingleNode("/resultset/result/layoutxml");
    sFetchXml = HttpUtility.HtmlDecode(oViewFetchXml.InnerXml);
    sLayoutXml = HttpUtility.HtmlDecode(oViewLayoutXml.InnerXml);

}

Next, we want to create an ArrayList object to store the columns in our view. We use the getColumnList() method to parse our Layout XML and populate the ArrayList (and we also generate our header record if specified):

private void getColumnList(ref ArrayList alColumns, string sLayoutXml, bool bHideHeader, string sDelimiter, bool bShowRowId, ref string sRowId)
{
    //  parse layout xml to create column list
    XmlDocument oLayoutXml = new XmlDocument();
    oLayoutXml.LoadXml(sLayoutXml);
    XmlNode oRowNode = oLayoutXml.SelectSingleNode("/grid/row");
    foreach (XmlNode oNode in oRowNode.ChildNodes)
    {
        string sColName = oNode.Attributes["name"].Value;
        alColumns.Add(sColName);
    }

    //  write header record
    if (!bHideHeader)
    {
        StringBuilder sbHeader = new StringBuilder();
        if (bShowRowId)
        {
            //  output row id column
            sRowId = oRowNode.Attributes["id"].Value;
            sbHeader.Append(sRowId + sDelimiter);
        }
        foreach (XmlNode oNode in oRowNode.ChildNodes)
        {
            //  output columns
            string sColName = oNode.Attributes["name"].Value;
            if (oNode != oRowNode.LastChild)
            {
                sColName += sDelimiter;
            }
            sbHeader.Append(sColName);
        }

        writeLine(sbHeader.ToString());
    }
}

Notice we are grabbing the name of the primary key - sRowId - we'll be using that later when we write the data. Also, see the writeLine() method above? That's a simple little helper method that writes a line of text to a stream:

private void writeLine(string value)
{
    //  write line of text to file stream
    byte[] info = new UTF8Encoding(true).GetBytes(value + "\r\n");
    _fsTempFile.Write(info, 0, info.Length);
}

Okay, we've got our column layout (and written our header record, if applicable). Next, we create an XmlDocument object and populate it by fetching all the records in the view:

private void getAllRecords(ref XmlDocument oResults, string sFetchXml)
{
    //  get ALL fetch records (CRM will throttle at 5000 records)
    bool bComplete = false;
    int iPage = 1;
    while (!bComplete)
    {
        //  add/revise page attribute to fetch
        XmlDocument oFetchXml = new XmlDocument();
        oFetchXml.LoadXml(sFetchXml);
        XmlNode oFetchNode = oFetchXml.SelectSingleNode("/fetch");
        if (oFetchNode.Attributes["page"] == null)
        {
            XmlAttribute oPageAttribute = oFetchXml.CreateAttribute("page");
            oPageAttribute.Value = iPage.ToString();
            oFetchNode.Attributes.Append(oPageAttribute);
        }
        else
        {
            oFetchNode.Attributes["page"].Value = iPage.ToString();
        }
        //  execute fetch and look at results (check for morerecords)
        string sResultsXml = _oService.Fetch(oFetchXml.InnerXml);
        XmlDocument oTempXml = new XmlDocument();
        oTempXml.LoadXml(sResultsXml);
        if (iPage == 1)
        {
            oResults.LoadXml(oTempXml.InnerXml);
        }
        else
        {
            XmlNodeList oNodes = oTempXml.SelectNodes("/resultset/result");
            XmlNode oResultsetNode = oResults.SelectSingleNode("/resultset");
            foreach(XmlNode oNode in oNodes)
            {
                XmlNode oNewNode = oResults.ImportNode(oNode,true);
                oResultsetNode.AppendChild(oNewNode);
            }
        }
        //  check for more records
        XmlNode oMore = oTempXml.SelectSingleNode("/resultset");
        if (oMore.Attributes["morerecords"].Value == "0")
        {
            bComplete = true;
        }
        else
        {
            iPage++;
        }
    }
}

Notice that the above code is not a simple fetch, I used this technique from an earlier post to make sure I have ALL the records in the view. This is a good habit to form, since you won't run into problems if your data grows beyond the 5,000 record limit.

Now I've got all my records. So it's time to write out the data to the text file. This is pretty straightforward. We're just going through each node of our XML and creating a single line for each result (columns separated by our delimiter). Don't forget to output the primary key of the row if specified:

private void writeResults(ArrayList alColumns, string sDelimiter, XmlDocument oResults, bool bShowRowId, string sRowId)
{
    //  write results
    XmlNodeList oRecordNodes = oResults.SelectNodes("/resultset/result");
    foreach (XmlNode oRecordNode in oRecordNodes)
    {
        StringBuilder sbRowData = new StringBuilder();
        if (bShowRowId)
        {
            // output row id
            XmlNode oRowIdNode = oRecordNode.SelectSingleNode(sRowId);
            sbRowData.Append(oRowIdNode.InnerText);
            sbRowData.Append(sDelimiter);
        }
        for (int i = 0; i < alColumns.Count; i++)
        {
            XmlNode oColumnNode = oRecordNode.SelectSingleNode(alColumns[i].ToString());
            if (oColumnNode != null)
            {
                //  if node has "name" attribute, use that instead (picklists and lookups)
                if (oColumnNode.Attributes["name"] == null)
                {
                    sbRowData.Append(oColumnNode.InnerText);
                }
                else
                {
                    string sName = oColumnNode.Attributes["name"].Value;
                    sbRowData.Append(sName);
                }
            }
            if (i != (alColumns.Count - 1))
            {
                sbRowData.Append(sDelimiter);
            }
        }
        writeLine(sbRowData.ToString());
    }
}

You may notice that I did a little trick here with the values - I'm checking each node to see if it has the name attribute and if it does, then I'm outputting the value of that instead of the node value. This is because the database value of picklists and lookups are returned as the node data. The translated value is in the name attribute of the node. Take the following example for the customertypecode column:

<customertypecode name="Customer" formattedvalue="3">3</customertypecode>

You could certainly skip this check and just output the data value of "3", but I wrote in the logic to spit out "Customer" instead. You can take this one step further by creating a parameter (like the HideHeader parameter) that lets you specify at runtime which option to use. In fact, I've put this in place in my copy of the above code, but I'll let you figure out how to do this in your copy.

All that's left is to close our temp file and send it out to the browser, which I will repeat from above:

//  close file
_fsTempFile.Close();

//  output file to browser
Response.Clear();
FileInfo oFile = new FileInfo(sTempFileName);
Response.AddHeader("Content-Disposition", "attachment; filename=" + oFile.Name);
Response.AddHeader("Content-Length", oFile.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(sTempFileName);

What we're doing here is just setting the response headers so the user's browser know that we're sending a file instead of something to be displayed. We then output the file.

There you have it. This piece of code is pretty powerful and can give you or your users some neat functionality. Stay tuned for my take on a great way to utilize this code. Enjoy!