Thursday, November 22, 2012

XML transformation from Excel spreadsheet

Being inspired from sample code in Inside Microsoft Dynamics AX 2012 I have made a small sample of how you can use an Excel spreadsheet as input for an AIF XML transformation.

To emphasize on the important bits and pieces, I have left out all the sugar and bells and whistles.
Also the XML code is not exactly formatted as AIF expects it, but you can follow this article to figure out how to do that.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.OleDb;
using Microsoft.Dynamics.IntegrationFramework.Transform;
namespace TestExcelTransform
    public class TransformExcel : ITransform
        public void Transform(System.IO.Stream input, System.IO.Stream output, string config)
            string excelFilename = @"C:\TEST\output.xlsx";
            // Save the received XML in a location available from the AOS
            TransformExcel.saveExcelFile(input, excelFilename);
            TransformExcel.saveAsXML(excelFilename, output);
        private static void saveExcelFile(System.IO.Stream input, string filename)
            System.IO.Stream excelOutput = new System.IO.FileStream(filename, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);
            // The CopyTo method requires .NET 4.0
            // In older versions you need to save the stream with a bit more code:
            // public static void CopyStream(Stream input, Stream output)
            // {
            //     byte[] buffer = new byte[8 * 1024];
            //     int len;
            //     while ( (len = input.Read(buffer, 0, buffer.Length)) > 0)
            //     {
            //         output.Write(buffer, 0, len);
            //     }    
            // }
        static private DataSet ReadDataFromExcel(string filename)
            string connectionString;
            OleDbDataAdapter adapter;
            // The connection string required to access the spreadsheet as a datasource
            connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;"
                + "Data Source=" + filename + ";"
                + "Extended Properties='Excel 12.0 Xml;"
                + "HDR=YES'";  // Sheet has row header with column titles
            adapter = new OleDbDataAdapter(
                "SELECT * FROM [sheet1$]",
            DataSet ds = new DataSet();
            // Get the data from the spreadsheet
            adapter.Fill(ds, "Customers");
            // Return the dataset
            return ds;
        private static void saveAsXML(string filename, System.IO.Stream output)
            XmlTextWriter xwriter = new XmlTextWriter(output, Encoding.UTF8);
            string rootName = "Customers";
            string rowName = "Customer";
            // Start writing the XML file.
            xwriter.Formatting = Formatting.Indented;
            xwriter.WriteComment("customers.xml file");
            // Get the Customers table from the data source
            DataTable table = TransformExcel.ReadDataFromExcel(filename).Tables["Customers"];
            foreach (DataRow row in table.Rows)
                string accountNum = row["Account"] as string;
                string name = row["Name"] as string;
                // Loop through each line of data in the file.
                // Write field elements
                xwriter.WriteElementString("Account", accountNum);
                xwriter.WriteElementString("Name", name);
                // Write the </Customer> end element.
            // Write the </Customers> end element.

And here's the contents of the spreadsheet (account numbers are formatted as text):
And the result: