]> Posts for April 2019 🌐:aligrant.com

Reading inconsistent data from Excel with ACE

Alastair Grant | Tuesday 9 April 2019

I've had the unpleasant task of reading through an Excel spreadsheet of questionable data quality using the ACE OLEDB provider.

One of the challenges with reading data from Excel is data types.  In a conventional relational database you know that column X is always going to be of type Y.  But in Excel, all bets are off.  There may be a word in the first row, and a number in the second.  Or even more confusing a number as a string, and so on.

The ACE driver will guess the data type by reading a few rows until it finds data and then works out the type.  If you have lots of blanks, you can fudge this by putting a dummy row in first with example data of default values to allow the provider driver to work out the types correctly.

Another approach that I'm using fairly successfully at the moment is to read the data as an object and then attempt to convert it (not cast it), using this handy little extension method:

/// <summary>
/// Attempts to retrieve a value as the provided type.
/// </summary>
/// <remarks>
/// Reads the value as an object and then attempts to use IConvertable to convert into another data type.
/// Nullable<> types are supported, and will return NULL if there is no value.
/// </remarks>
/// <typeparam name="T">Type to attempt conversion on, including nullable types.</typeparam>
/// <param name="row">DataRow to read from.</param>
/// <param name="colName">Column Name to read from.</param>
/// <returns>Value or default</returns>
public static T GetSafish<T>(this DataRow row, string colName)
    if (colName == null) throw new ArgumentNullException(nameof(colName));

    if (row.IsNull(colName)) return default(T);

    return (T) Convert.ChangeType(row[colName], Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T));

Instead of calling row.Field<double?>("MyCol"), you call row.GetSafish<double?>("MyCol").  If Excel is handling the data as a string, it'll get converted to a double just fine, and if the cell is blank, you'll get null.  You will get a System.FormatException when the conversion can't take place (such as those horrible blank cells that aren't blank).

Breaking from the voyeuristic norms of the Internet, any comments can be made in private by contacting me.

BizTalk: XTransformationFailureException on mutli-part XSLT map.

Alastair Grant | Friday 5 April 2019

Microsoft.XLANGs.Core.XTransformationFailureException: Error encountered while executing the transform XXX.MyMap. Error:Transformation failed.. ---> Microsoft.XLANGs.Core.XTransformationFailureException: Error encountered while executing the transform XXX.MyMap. Error:Invalid input/output document into/from the transform. Does not have a single root node..

Ya what now?

I received this error message when using a multi-message map in BizTalk.

If you need to use multiple messages on either side of your map, then you can do this with BizTalk.  You have to define your map from the Orchestration Transform shape, but selecting all the input, or output, messages that you wish to use.

Visual Studio will then update the schema used in the map dynamically with a custom schema that contains all your messages, wrapped up in a schema of type http://schemas.microsoft.com/BizTalk/2003/aggschema#Root.  Each message will be embedded in an element named InputMessagePart_X, where X is a zero-based sequential number.  The runtime takes care of this combining of messages when the map is called.

A repeating InputMessagePart node would arguably be more flexible, but hey.

In my particular situation I had multiple input messages and one output. Using the Test Map action on the map file, Visual Studio can generate you one of these aggregate messages for you (which you can then easily customise the message parts with test data).  My map worked just fine within Visual Studio, but not in BizTalk at runtime, and I received the error above.

Logically, it would seem like the problem was with the inputs, as I had multiple - I assumed that for some reason BizTalk wasn't wrapping up the inputs into the aggregate schema and creating multiple root elements.


I was incorrect.  The issue actually lay in the output - I was using Custom XSLT (although I'm not sure if it's exclusive to XSLT maps), and I needed to exclude the XML declaration (that's the <?xml version="1.0" encoding="utf-8"?> bit) in the output message.  I'm not sure why this is, as there is only a single output, my assumption is that there is some sort of "multi-message mode" in BizTalk that will expect both sides to be wrapped up - having an XML declaration midway through a message (e.g. if it was wrapped in an envelope message), would be invalid XML.

<xsl:output method="xml" omit-xml-declaration="yes"/>

Setting omit-xml-declaration excludes the XML declaration from the top of the output, and somehow everything starts working.

Remember when making changes to XSLT files, you will need to force a rebuild to your project as Visual Studio doesn't pick this up as a change, even if you have the XSLT included in the project.

Breaking from the voyeuristic norms of the Internet, any comments can be made in private by contacting me.

Entries for: April 2019

Previous Next