- Posted by Bojan Jovičić on May 29, 2011
Recently I have been asked to help with solving an issue that involved reading data from Microsoft Excel (xls format) into Microsoft Dynamics AX.
Original approach was used with default set of classes for Microsoft Excel (SysExcelApplication, SysExcelWorksheets, SysExcelWorksheet, etc.). The excel file in question was very small but with a rather large number of columns (~150). About 200 reads were made per row. What made us investigate this further was the fact that import was quite slow. After doing some basic profiling (we used Microsoft .NET's System.Diagnostics.Stopwatch so we can cross-compare the results) we found out that the time required to process one row increases almost exponentially.
After that we developed a small application in Microsoft.NET/C# (with Microsoft Visual Studio) that was reading data from Excel using OleDbConnection class and saving it in Microsoft Dynamics AX using .NET Business Connector. The increase in speed was several orders of magnitude but what is also important is that near exponential growth of time per row was missing.
We then applied the same principle with Microsoft Dynamics in X++ using its set of classes for ADO access (CCADOConnection, CCADOCommand, CCADORecordset). The speed was still around 100 times greater than with default approach and ~2.5 times slower than .NET. With this set of classes we were lacking the ability to do arbitrary (e.g. accessing the previous row values) row access like in .NET, so some caching was needed. Still the speed increase was incredible. Below is the graph of time needed (in milliseconds) to process one row with all three approaches.
If you haven't tried this apporach, I suggest it. For it will surely outweight the time needed for implementation, especially with bigger or files with many columns. Connection string and row selection query (you can remove absolute row range) for .NET and X++ should look something like this:
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=Excel 8.0;";
rowQuery = strfmt("SELECT * FROM [Excel 8.0;HDR=NO;IMEX=1;Database=%1;].[%2$%3]", filename, "OrderWorkSheet", "A:EJ");
Update: Zubair got in contact with me regarding this and here you can find full X++ samples for XLS and XLSX.