Pages

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
            input.CopyTo(excelOutput);
 
            // 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);
            //     }    
            // }
 
            excelOutput.Close();
        }
 
        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$]",
                connectionString);
 
            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.WriteStartDocument();
            xwriter.WriteComment("customers.xml file");
            xwriter.WriteStartElement(rootName);
 
            // 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.
                xwriter.WriteStartElement(rowName);
 
                // Write field elements
                xwriter.WriteElementString("Account", accountNum);
                xwriter.WriteElementString("Name", name);
 
                // Write the </Customer> end element.
                xwriter.WriteEndElement();
            }
 
            // Write the </Customers> end element.
            xwriter.WriteEndElement();
            xwriter.Close();
        }
    }
}

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

Thursday, November 15, 2012

Thursday, November 8, 2012

Wednesday, November 7, 2012

Update cross reference in batch

In AX 2012 R2 you can update cross reference in batch.

You just need to add the class xRefUpdateIL to a bacth job task.

It seems like a batch task for this is automatically created when doing a full compilation, but I haven't really investigated the details of that. Any comments on that are very welcome.

Monday, November 5, 2012

Two new AX books from Packt Publishing

Microsoft Dynamics AX 2012 Security How-To - Available now
I read it, but find it a bit thin. There's not really any new information that you can't already read from the available Microsoft documention. Note the low page count!

Microsoft Dynamics AX 2012 Services -  Available for pre-order
I'm one of the reviewers of this book, so I have already read it. I can really recommend this book to anyone who needs to work with services in AX.

Friday, November 2, 2012

Team Foundation Service is now live

The Team Foundation Service is now live.

If you have tried the preview version, you'll eventually have to change the URL to the service. The new URL is https://yoursitename.visualstudio.com:443/defaultcollection

You'll still need to install the hotfix mentioned here, if you are new to the service.

You can learn more about the service here: https://tfs.visualstudio.com/

The service is to some extend free to MSDN subscribers.