Monday, November 28, 2011

Import From Excel in Batch Job - Dynamics AX

First i will quickly go through all the different approaches that i tried unsuccessfully and then i will discuss my final solution that worked.  i had three unsuccesfull attempts:
1. Standard procedure to import data from using AX's system classes for Excel(SysExcelApplication, SysExcelWorkbooks .. etc) . All these base classes are configured to run only on client (verify the RunOn property). So my program was running fine on client but failed to run on Batch Job on Server.  FYI:  i changed the RunOn property to "Called from" but that didn't help.
My next approach was to do all the excel stuff in C#.net and then consume the .net assembly in AX.

2. Using .Net's System.Data.Oledb :  i chose this namespace instead of Microsoft.Office.InterOp.Excel because this doesn't need the Office Excel installed on the Server. I faced the same issue as in approach 1,  my AX program was able to create an instance for .Net class in AX client, but was failing to create the instance in Batch Job.
3. Using .Net's Microsoft.Office.InterOp.Excel: i was left with this choice and i thought this would work without any issue but the same result. AX client is able to consume my dll but Batch Job was failing.
Final solution:
Thanks to my friend Dusan Chalic for recommending me to use Excel Reader from codeplex, it worked perfectly, here is the C# solution:
a) add reference to Excel.dll (download it from above link in codeplex) in your Visual Studio Project
b) create a method to read the Excel contents into a DataSet instance
c) create a method that will take a row number and return the corresponding row (AX will call this method)
d) Here is C# class that will read data from Excel file :


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel;
using System.IO;
using System.Data;

namespace ExcelReader
{
    public class ReadDataFromXL
    {
        DataTable xlDataTable;

        public string RetrieveFromXL(string fileName)
        {
            //pass the filename with path
            IExcelDataReader xlReader = null;
            FileStream xlStream = null;
            DataSet xlDataSet = null;
            string empId, fullName, accPriority, routNum, accNum, accType;
            xlDataTable = new DataTable();
            xlDataTable.Columns.Add("EmpId", typeof(string));
            xlDataTable.Columns.Add("FullName", typeof(string));
            xlDataTable.Columns.Add("AccPriority", typeof(string));
            xlDataTable.Columns.Add("RoutNumber", typeof(string));
            xlDataTable.Columns.Add("AccNum", typeof(string));
            xlDataTable.Columns.Add("AccType", typeof(string));

            try
            {
                xlStream = File.Open(fileName, FileMode.Open, FileAccess.Read);

                //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                xlReader = ExcelReaderFactory.CreateBinaryReader(xlStream);

                //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                //xlReader = ExcelReaderFactory.CreateOpenXmlReader(xlStream);

                xlReader.IsFirstRowAsColumnNames = false;
                xlDataSet = xlReader.AsDataSet();

                int rowNumber = 0;
                while (xlReader.Read())
                {
                    rowNumber++;
                    if (rowNumber < 5)
                        continue;
                    
                    empId = SanTryParse(xlReader.GetString(0));
                    fullName = SanTryParse(xlReader.GetString(1));
                    accPriority = SanTryParse(xlReader.GetString(2));
                    routNum = SanTryParse(xlReader.GetString(3));
                    accNum = SanTryParse(xlReader.GetString(4));
                    accType = SanTryParse(xlReader.GetString(5));

                    if (empId == "" && fullName == "" && accPriority == "")
                        break;
                    //Console.WriteLine(string.Format("{0} {1} {2} {3} {4} {5}", empId, fullName, accPriority, routNum, accNum, accType));                    
                    // fill the datatable
                    xlDataTable.Rows.Add(empId, fullName, accPriority, routNum, accNum, accType);
                }
                //Console.WriteLine("Row Count: " + xlDataTable.Rows.Count);
                xlReader.Close();               
            }
            catch (Exception ex)
            {
                if(xlReader != null)
                     xlReader.Close();
            }           
            return "Done";
        }

        public int GetRowCount()
        {
            return xlDataTable.Rows.Count;
        }        

        public string GetRow(int index)
        {
            string empId, fullName, accPriority, routNum, accNum, accType;
            DataRow currRow = xlDataTable.Rows[index];
            int endCol = 5;
            string result;

            empId = SanTryParse(currRow[0]);
            fullName = SanTryParse(currRow[1]);
            accPriority = SanTryParse(currRow[2]);
            routNum = SanTryParse(currRow[3]);
            accNum = SanTryParse(currRow[4]);
            accType = SanTryParse(currRow[5]);

            result = empId + "!" + fullName + "!" + accPriority + "!" + routNum + "!" + accNum + "!" + accType;
            return result;
        }

        private string SanTryParse(object input)
        {
            if (input == null)
                return "";
            return Convert.ToString(input);
        }
    }
}

e) Sign the above VS project, compile and deploy the assembly to GAC.
f) Now the AX part - open AOT -> References node -> right click and Add reference to the above assembly in GAC
g) create a a new batch job Class in AX (Extending RunBaseBatch)
h) create a method , here is the code to call the methods in C# class and read the data from Excel:


void importDataFromXlReader()
{
    Set   permissionSet;
    System.Exception e;
    str result,  currRowStr;
    int totalRows, i, j;
    List values;
    ListIterator iter;
    str valuesArr[6];
    SanTempTable buffer;  // Temporary table to hold the data from Excel
  ;
    try
    {


        permissionSet =  new Set(Types::Class);
        permissionSet.add(new InteropPermission(InteropKind::ClrInterop));
        permissionSet.add(new InteropPermission(InteropKind::ComInterop));
        permissionSet.add(new InteropPermission(InteropKind::DllInterop));
        CodeAccessPermission::assertMultiple(permissionSet);


        xlReader = new ExcelReader.ReadDataFromXL();
        result = xlReader.RetrieveFromXL(fileName);
        //info(result);


        if(result == "Done")
        {
          totalRows = xlReader.GetRowCount();
          if(totalRows <= 0)
             {
               errMessage = "Zero Rows read from XL, there is an issue";
               throw error(errMessage);
             }


          lastRow = totalRows; //lastRow is class vraiable used for ProgressBar
          info(strFmt("Total Rows:  %1", totalRows));


           for(i=0; i<totalRows ; i++)
           {
             currRowStr = xlReader.GetRow(i);
             //info(strFmt("Current Row: %1", currRowStr));
             values = Global::strSplit(currRowStr, '!');
             iter = new ListIterator(values);
             j = 0;
             while(iter.more())
             {
               j++;
               //info(iter.value());
               if(j<=6)
                 valuesArr[j] = iter.value();
               iter.next();
             }
             //info(strFmt("Individual Values: %1 %2 %3 %4 %5 %6 ", valuesArr[1], valuesArr[2], valuesArr[3], valuesArr[4], valuesArr[5], valuesArr[6] ));
             // fill the buffer
             buffer.EmplId = valuesArr[1];
             buffer.EmpName = valuesArr[2];             
             buffer.AccountPriority = str2Int(valuesArr[3]);             
             buffer.RoutingNumber = valuesArr[4];
             buffer.AccountNumber = valuesArr[5];
             buffer.AccountType = valuesArr[6];
             buffer.insert();
           }// end for
        }// end if


        CodeAccessPermission::revertAssert();
    }
    catch(Exception::CLRError)
    {
        info(CLRInterop::getLastException().ToString());
        e = CLRInterop::getLastException();
        errMessage = e.get_Message() + "\n";
        while( e )
        {
           info( e.get_Message() );
           e = e.get_InnerException();
           errMessage = errMessage +  "\n" + e.get_Message();
        }
        throw error(errMessage);
    }
}


with these 2 code snippets we should be able to import data from Excel in AX 2009 Batch Jobs.

Happy Daxing and Cyber Monday shopping
San.

21 comments:

  1. Hi Santosh,

    I am getting the following error while compiling job in AX.

    Variable xlReader has not been declared.

    Can you please tell me what type of variable it should be ?

    ReplyDelete
  2. xlReader is an instance of the C# class ExcelReader whose dll need to signed and deployed to GAC. Then from AOT->Refernces , add the reference to the above dll.
    Once this is done, then ExcelReader would be available for you to use within AX class, job or any other code places.
    Thanks,
    Santosh.

    ReplyDelete
    Replies
    1. can describe step by step process of this.

      Delete
  3. Hi, i have correctly imported my dll into AX references node (After GAC registration).
    Now when i try to call load method, when it create an instance of Excel (from Excel.dll library) it crash with the following CLR error message :

    System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Excel, Version=2.0.1.0, Culture=neutral, PublicKeyToken=93517dbe6a4012fa' or one of its dependencies. The system cannot find the file specified.
    File name: 'Excel, Version=2.0.1.0, Culture=neutral, PublicKeyToken=93517dbe6a4012fa'
    at ExcelReaderAX.GeneralLedgerExcelReader.RetrieveFromXL(String fileName)

    I've also try to copy (and register into GAC) Excel.dll.

    I've store dll in client\bin\ folder but it fail.
    So after a web research i moved my dll (and Excel.dll) to ..\server\\bin but problem isn't solved.

    Have you any idea ?

    Thanks

    AM

    ReplyDelete
  4. Hi AM, make sure to set the Platform target to "Any CPU" in your Visual Studio Project properties window.
    Hope that helps,
    San

    ReplyDelete
    Replies
    1. Hi San,
      Thanks for reply.
      Platform target was already set to "Any CPU" but it doesn't work.
      When i try to execute my program, i catch this message error :

      Assembly containing type Excel.ExcelReaderFactory is not referenced.

      Excel.dll is signed and deployed to GAC. It is referenced to AOT->References too.

      I'm so confused about this error..

      AM

      Delete
    2. Hi San,
      finally i'm solved by restarting AOS service.
      I do this operation yesterday without success.

      At the moment i've try my code not in batch.

      Today i'll try that.

      Thanks a lot for the help.

      Delete
  5. Great Post.
    Stuck with exact the same problem. Solution works very well.

    ReplyDelete
    Replies
    1. same error im getting plz reply

      System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Excel, Version=2.0.1.0, Culture=neutral, PublicKeyToken=93517dbe6a4012fa' or one of its dependencies. The system cannot find the file specified.

      at ExcelReader.ReadDataFromXL.RetrieveFromXL(String fileName)

      --- End of inner exception stack trace ---

      at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)

      at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

      at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

      at Microsoft.Dynamics.AX.ManagedInterop.ClrBridgeImpl.InvokeClrInstanceMethod(ClrBridgeImpl* , ObjectWrapper* objectWrapper, Char* pszMethodName, Int32 argsLength, ObjectWrapper** arguments, Boolean* argsAreByRef, Boolean* isException)

      Delete
  6. good to know that you are able to use it. Thanks for the post.
    Santosh.

    ReplyDelete
  7. hi san,
    I am getting the following error while compiling job in AX.

    Variable xlReader has not been declared.

    when i try to solve the issue using the above it doesnt work for me

    ReplyDelete
  8. I might have declared the variable 'xlReader' at the class level. Anyhow it is an instance of type - ReadDataFromXL.
    - San

    ReplyDelete
  9. Hi San,
    clearly tell me in which dll ReadDataFromXL.is present

    ReplyDelete
  10. HI santhosh,
    While trying to compile my job in ax i am getting "zero rows read from xl,there is an error".How to resolve it?

    ReplyDelete
  11. I am getting "Zero Rows Read From Xl, there is an error", how to resolve it?

    ReplyDelete
  12. I am getting "Zero Rows Read From Xl, there is an error", how to resolve it?

    could you tell me

    ReplyDelete
  13. I am getting "Zero Rows Read From Xl, there is an error", how to resolve it?

    ReplyDelete
    Replies
    1. Pankaj, i would try these things:
      1. try Saving excel file in 2 formats - XLS and XLSX , hopefully one of them should work
      2. to make your debugging simple Write a simple consumer application in C# Console program
      Good Luck.

      Delete
  14. San Thank You Great Post.
    Stuck with exact the same problem.it is best Solution.

    ReplyDelete
  15. Very nice blog.It will be a great help for me regarding excel file conversion
    excel file conversion

    ReplyDelete