Monday, November 28, 2011
Import From Excel in Batch Job – Dynamics 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 :
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.