Microsoft Dynamics AX 2012 & D365 F&O Blog For Microsoft ERP Business Solution : Import From Excel in Batch Job

Import From Excel in Batch Job - Dynamics AX2012

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 :

1.Open Visual Studio .

2.Create project S3 Excel Reader Class library.

3. S3 Excel Reader project Solution Add Excel Reference By clicking Right

5.Browse File add Excel .Dll File.

Click Ok Button .

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

namespace ExcelReadClass
{
    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;
            string accountnum, Name, custgroup, city, street, sreetnumber, CountryRegionId, Locator, Email, PaymIdType, TaxGroup, Currency, ItemId, SalesQty, SalesPrice, LineDisc, inventLocationId, inventSerialId;

            xlDataTable = new DataTable();
            xlDataTable.Columns.Add("accountnum", typeof(string));
            xlDataTable.Columns.Add("Name", typeof(string));
            xlDataTable.Columns.Add("custgroup", typeof(string));
            xlDataTable.Columns.Add("city", typeof(string));
            xlDataTable.Columns.Add("street", typeof(string));
            xlDataTable.Columns.Add("sreetnumber", typeof(string));
            xlDataTable.Columns.Add("CountryRegionId", typeof(string));
            xlDataTable.Columns.Add("Locator ", typeof(string));
            xlDataTable.Columns.Add("Email ", typeof(string));
            xlDataTable.Columns.Add("PaymIdType", typeof(string));
            xlDataTable.Columns.Add("TaxGroup", typeof(string));
            xlDataTable.Columns.Add("Currency", typeof(string));
            xlDataTable.Columns.Add("ItemId ", typeof(string));
            xlDataTable.Columns.Add("SalesQty ", typeof(string));
            xlDataTable.Columns.Add("SalesPrice ", typeof(string));
            xlDataTable.Columns.Add("LineDisc ", typeof(string));
            xlDataTable.Columns.Add("inventLocationId ", typeof(string));
            xlDataTable.Columns.Add("inventSerialId ", 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 < 0)
                        continue;

                    accountnum = SanTryParse(xlReader.GetString(0));
                    Name = SanTryParse(xlReader.GetString(1));
                    custgroup = SanTryParse(xlReader.GetString(2));
                    city = SanTryParse(xlReader.GetString(3));
                    street = SanTryParse(xlReader.GetString(4));
                    sreetnumber = SanTryParse(xlReader.GetString(5));
                    CountryRegionId = SanTryParse(xlReader.GetString(6));
                    Locator = SanTryParse(xlReader.GetString(7));
                    Email = SanTryParse(xlReader.GetString(8));
                    PaymIdType = SanTryParse(xlReader.GetString(9));
                    TaxGroup = SanTryParse(xlReader.GetString(10));
                    Currency = SanTryParse(xlReader.GetString(11));
                    ItemId = SanTryParse(xlReader.GetString(12));
                    SalesQty = SanTryParse(xlReader.GetString(13));
                    SalesPrice = SanTryParse(xlReader.GetString(14));
                    LineDisc = SanTryParse(xlReader.GetString(15));
                    inventLocationId = SanTryParse(xlReader.GetString(16));
                    inventSerialId = SanTryParse(xlReader.GetString(17));

                    // 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);
                    xlDataTable.Rows.Add(accountnum, Name, custgroup, city, street, sreetnumber, CountryRegionId, Locator, Email, PaymIdType, TaxGroup, Currency, ItemId, SalesQty, SalesPrice, LineDisc, inventLocationId, inventSerialId);
                }
                //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 accountnum, Name, custgroup, city, street, sreetnumber, CountryRegionId, Locator, Email,
          PaymIdType, TaxGroup, Currency, ItemId, SalesQty, SalesPrice, LineDisc, inventLocationId, inventSerialId;
            //string empId, fullName, accPriority, routNum, accNum, accType;
            DataRow currRow = xlDataTable.Rows[index];
            int endCol = 18;
            string result;

            accountnum = SanTryParse(currRow[0]);
            Name = SanTryParse(currRow[1]);
            custgroup = SanTryParse(currRow[2]);
            city = SanTryParse(currRow[3]);
            street = SanTryParse(currRow[4]);
            sreetnumber = SanTryParse(currRow[5]);
            CountryRegionId = SanTryParse(currRow[6]);
            Locator = SanTryParse(currRow[7]);
            Email = SanTryParse(currRow[8]);
            PaymIdType = SanTryParse(currRow[9]);
            TaxGroup = SanTryParse(currRow[10]);
            Currency = SanTryParse(currRow[11]);
            ItemId = SanTryParse(currRow[12]);
            SalesQty = SanTryParse(currRow[13]);
            SalesPrice = SanTryParse(currRow[14]);
            LineDisc = SanTryParse(currRow[15]);
            inventLocationId = SanTryParse(currRow[16]);
            inventSerialId = SanTryParse(currRow[17]);
            // sreetnumber = SanTryParse(currRow[5]);

            result = accountnum + "!" + Name + "!" + custgroup + "!" + city + "!" + street + "!" + sreetnumber + "!" + CountryRegionId + "!" + Locator + "!" +
             Email + "!" + PaymIdType + "!" + TaxGroup + "!" + Currency + "!" + ItemId + "!" + SalesQty + "!" + SalesPrice + "!" + LineDisc + "!" +
             inventLocationId + "!" + inventSerialId;
            return result;
        }

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

}

6.Right click and Build the project.

7. Right Click Select Add S3 Excel Reader to IDE AOT.

8. Add this file to GAC Folder.

9 After copying this file past it.

10. Create Table A table S3exceltable add fields.

10. Add a Reference in aot.

Add S3 Excel Reader Dll File.

11. Create A class S3ExcelTableClass and extends RunBaseBatch.

class S3ExcelTableClass extends RunBaseBatch

{

//#Excel

SysExcelApplication xlsApplication;

SysExcelWorkBooks xlsWorkBookCollection;

SysExcelWorkBook xlsWorkBook;

SysExcelWorksheets xlsWorkSheetCollection;

SysExcelWorksheet xlsWorkSheet;

SysExcelRange xlsRange;

SysExcelCells Cells;

SysExcelCell RCell;

CommaIO inFile;

int nRow,i;

DialogField dialogPath;

DialogRunbase dialog;

FileNameOpen filename;

//FilenameOpen filenameOpen;

DirPerson dirPerson;

DirPersonName dirPersonName;

LogisticsLocation logisticsLocation;

LogisticsPostalAddress logisticsPostalAddress,logisticsPostalAddressloc;

LogisticsElectronicAddress logisticsElectronicAddress;

CustTable custTable;

Salestable salestable;

SalesLine salesLine;

InventDim inventDim;

InventSite inventsite;

WMSLocation wmslocation;

CustAccount custAccount;

DirPartyName name;

CustGroupId custGroupId;

LogisticsAddressing address;

LogisticsElectronicAddressLocator telephone;

Email email;

BankCustPaymIdType paymentType;

TaxGroup taxGroup;

CustCurrencyCode currency;

ItemIdSmall itemId;

SalesOrderedQty Qty;

SalesPrice salesPrice;

InventSiteId Site;

InventLocationId Warehouse;

InventSerialId inventSerialId;

DirPartyTable dirpartytable,dirpartytableloc;

Amount qty1,qty2;

AmountCur amountcur;

SalesFormLetter salesFormLetter;

///added for address

DirParty dirParty,dirPartyloc;

DirPartyPostalAddressView dirPartyPostalAddressView;

LogisticsAddressCity city;

LogisticsAddressStreet street;

LogisticsAddressStreetNumber streetnumber;

LogisticsAddressCountryRegionId countryregionid;

DirPartyContactInfoView ContactInfoView;

SalesLineDisc discount;

SalesLinePercent Percentage;

DialogField dialogFilename;

COMVariantType type;

int rowNo;

int row;

int column;

S3exceltable exceltable,buffer,exceltableloc;

// #define.CurrentVersion(1)

//////////////////

NoYes displayMessage;

Description message;

DialogField fieldDisplayMessage;

DialogField fieldMessage;

#define.CurrentVersion(1)

#localmacro.CurrentList

displayMessage,

message

#endmacro

}

public void ActivateCOMOnAOS()

{

#define.excel('Excel.Application')

COM com;

InteropPermission permission = new InteropPermission(InteropKind::ComInterop);

;

permission.assert();

com = new COM(#excel);

CodeAccessPermission::revertAssert();

}

public boolean canGoBatch()

{

return true;

}

//Method For Excel Reader

public void importDataFromXlReaderNew()

{

Set permissionSet;

System.Exception e;

str result, currRowStr;

int totalRows, x, y,lastRow;

List values;

ListIterator iter;

str valuesArr[18];

ExcelReadClass.ReadDataFromXL xlReader;

str errMessage;

//FileNameOpen filename;

// Temporary table to hold the data from Excel

str a;

;

//filename = ("D:\\SalesOrderImport1.xls");

a=@"C:\SalesOrderImport1.xls";

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 ExcelReadClass.ReadDataFromXL();

result = xlReader.RetrieveFromXL(a);

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(x=1; x<totalRows ; x++)

{

currRowStr = xlReader.GetRow(x);

info(strFmt("Current Row: %1", currRowStr));

values = Global::strSplit(currRowStr, '!');

iter = new ListIterator(values);

y = 0;//modidfied

while(iter.more())

{

y++;

//info(iter.value());

if(y<=18)

valuesArr[y] = 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.AccountNum = valuesArr[1];

buffer.Name = valuesArr[2];

buffer.CustGroup = valuesArr[3]; //str2Int(valuesArr[3]);

buffer.City = valuesArr[4];

buffer.Street = valuesArr[5];

buffer.StreetNumber = valuesArr[6];

buffer.CountryRegionId = valuesArr[7];

buffer.Locator = valuesArr[8];

buffer.Email = valuesArr[9];

buffer.PaymIdType = valuesArr[10];

buffer.TaxGroup = valuesArr[11];

buffer.Currency = valuesArr[12];

buffer.ItemId = valuesArr[13];

buffer.SalesQty = str2int(valuesArr[14]);

buffer.SalesPrice =str2int(valuesArr[15]);

buffer.LineDisc =str2int(valuesArr[16]);

buffer.inventLocationId = valuesArr[17];

buffer.inventSerialId = valuesArr[18];

buffer.insert();

}// end for

}// end if

CodeAccessPermission::revertAssert();

//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);

}

info(strFmt("%1,%2",buffer.AccountNum,buffer.ItemId));

}

public container pack()

{

container ret;

ret = super();

return ret;

}

public void run()

{

int k;

;

k = 1;

this.importDataFromXlReaderNew();

while select exceltable

{

select exceltableloc where exceltableloc.RecId == exceltable.RecId;

{

//info(strFmt("%1,%2",exceltable.AccountNum,exceltable.RecId));

// Sales order creation

salestable.clear();

salestable.initValue();

salestable.SalesId = NumberSeq::newGetNum(SalesParameters::numRefSalesId()).num();

salestable.SalesType = salestype::Sales;

salestable.CustAccount = exceltableloc.AccountNum;

salestable.initFromCustTable();

salestable.CustGroup = exceltableloc.CustGroup;

salestable.CurrencyCode = exceltableloc.Currency;

//salestable.TaxGroup = exceltableloc.TaxGroup;

salestable.InclTax = noyes::Yes;

salestable.SalesName = exceltableloc.Name;//modified

salesTable.InventLocationId = exceltableloc.inventLocationId;

salesTable.InventSiteId = '1';

salestable.insert();

//Sales line creation

salesline.clear();

salesline.initValue();

salesline.ItemId = exceltableloc.ItemId;

salesline.Name = Inventtable::find(exceltableloc.ItemId).NameAlias;

salesline.initFromSalesTable(salestable);

salesline.initFromInventTable(inventtable::find(salesline.ItemId));

salesline.SalesQty = exceltableloc.SalesQty;

salesline.QtyOrdered = exceltableloc.SalesQty ;

salesLine.LineDisc = exceltableloc.LineDisc;

//salesLine.LinePercent = Percentage;

salesline.SalesPrice = exceltableloc.SalesPrice;

qty1 = exceltableloc.SalesQty;

qty2 = exceltableloc.SalesPrice;

amountcur = qty1*qty2;

//salesLine.LineDisc = discount;

//salesline.LineAmount = amountcur ;

salesline.LineAmount = amountcur - discount;

//salesLine.TaxGroup = taxGroup;

salesline.createLine();

//Sales Order Invoice

salesformletter = salesformletter::construct(documentstatus::Invoice);

salesformletter.update(salestable,salesline.ReceiptDateConfirmed,salesupdate::All,accountorder::None);

}

info(strFmt("%1,%2",salestable.SalesId,salesline.ItemId));

}

//info(strFmt("%1,%2",salestable.SalesId,salesline.ItemId));

}

/////////////////////////////////////////////////////////////////////////////

public boolean unpack(container packedClass)

{

boolean ret;

ret = super(packedClass);

return ret;

}

public static s3ExcelTableClass construct()

{

return new s3ExcelTableClass();

}

public static ClassDescription description()

{

return "Sales Order Imports";

}

public static void main(Args _args)

{

S3ExcelTableClass s3ExcelTableClass = S3ExcelTableClass::construct();

S3_Excel bufferloc;

;

if (s3ExcelTableClass.prompt())

{

s3ExcelTableClass.run();

}

13.Create a Menuitem Class type

13.Add Menus.

14. Navigate Accounts receivable>Periodic

Click Run Batch Sales Order.

Click Recurrence button.

Fill

Click ok

15. Please Keep Your .Xls File at C:\ only

Download Project .XPO

Microsoft Dynamics AX 2012 & D365 F&O Blog For Microsoft ERP Business Solution

Pages

Saturday, 6 September 2014

Import From Excel in Batch Job - Dynamics AX2012 thought AIF

Import From Excel in Batch Job - Dynamics AX2012

No comments:

Post a Comment

Labels