Import excel sheet in AX 2009 using RunbaseBatch class.

Requirement: Import excel sheet in AX 2009 using RunbaseBatch class.

Brief Steps:

1.       Create a table or you can use existing if possible.

2.       Create a class which performs all operations.

3.       .xls file pattern.

Elaborated Steps:

1.       Create a table with 5 fields:

Create a table in AOT with name “TestImportTable” having required field which you want to print in your report.

Field	Type(EDT, if available)
EmplId	String (EmplId)
Name	String(Name)
WorkingDate	Date(TransDate)
WorkingHrs	Real
ProjectNo	String

2.       Create a Class which performs all operations.

class testImport extends RunbaseBatch {         DialogField                 dialogFileName;         FileNameOpen            fileName;         FileIOPermission          permission;                 TestImportTable            importTable;                 #File         #avifiles                 #define.CurrentVersion(1)        #define.Version1(1)        #localmacro.CurrentList             fileName        #endmacro }

public Object dialog() {         DialogRunbase       dialog = super();         ;         dialogFileName = dialog.addField(typeid(FileNameOpen));         return dialog; }

public Object dialog() {         DialogRunbase       dialog = super();         ;         dialogFileName = dialog.addField(typeid(FileNameOpen));         return dialog; }

public boolean getFromDialog() {         boolean ret;                 ret = super();         fileName =  dialogFileName.value();         return ret; }

// BP Deviation documented void importFromXSLFile() {         Sysexcelapplication   excelapp=sysexcelapplication::construct();         Sysexcelworksheet         excelworksheet;         Sysexcelrange                 excelrange;         Sysexcelcells                  excelcells;                SysOperationProgress    simpleProgress;         int                                    j =1;         ;         excelapp.workbooks().open(fileName);         excelworksheet = excelapp.worksheets().itemFromNum(1);         excelcells =excelworksheet.cells();                simpleProgress  = SysOperationProgress::newGeneral(#AviUpdate,"Import is in progress",100);         startLengthyOperation();         do        {                importTable .EmplId =  (excelcells.item(j,1).value().bStr());                importTable .Name =  (excelcells.item(j,1).value().bStr());                importTable .WorkingDate =  (excelcells.item(j,1).value().Date());                importTable .WorkingHrs =  (excelcells.item(j,1).value().Double());                importTable .ProjectNo =  (excelcells.item(j,1).value().bStr());              }        // exclude first line of excel         while(excelcells.item(j+1 ,1).value().bStr());         endLengthyOperation(); }

public container pack() {     return [#CurrentVersion,#CurrentList];  }

public boolean unpack(container packedClass) {         Version version = RunBase::getVersion(packedClass);         ;         switch (version)     {                 case #CurrentVersion:         [version,#CurrentList] = packedClass;         break;         default:         return false;    }    return true; }

public void run() {     #OCCRetryCount     ;     if (! this.validate())           throw error("Import has been cancel");         try        {                 this.importFromXLSFile(); //Import records of xls file                 }         catch(Exception::Deadlock)         {             retry;         }         catch(Exception::Error)         {                         info ('Import cancelled');         }         catch (Exception::UpdateConflict)         {                         if (appl.ttsLevel() == 0)             {                              if (xSession::currentRetryCount() >= #RetryNum)                  {                         throw Exception::UpdateConflictNotRecovered;                  }                  else                  {                         retry;                  }              }              else              {                     throw Exception::UpdateConflict;              }         }        }

public boolean validate() {     boolean     ret = true;     Container   checkType;     if (! filename)     {                 ret = checkFailed("Please specify file");     }     checkType =  Docu::splitFilename(filename);     if(Conpeek(checkType,2) != 'xls' || Conpeek(checkType,2) != 'XLS')     {           ret = checkFailed("file format is incorrect");     }      return ret; }

static void main(Args args) {     TestImport             importClass;     ;         importClass = new TestImport ();     if(TestImport.prompt());             TestImport.run(); }

     

     

3.       .xls file pattern.
You can make desire pattern or for this code you can use below one.

Import from .csv or txt file in AX 2009 through X++ code

Requirement:  Import .cvs or .txt file in AX 2009 using RunbaseBatch class.

Brief Steps:
1.       Create a table or you can use existing if possible.
2.       Create a class which performs all operations.
3.       Csv or txt file pattern.

Elaborated Steps:
1.       Create a table with 4 fields:

Create a table in AOT with name “TestImportTable” having required field which you want to print in your report.

Field	Type(EDT, if available)
EmplId	String (EmplId)
Name	String(Name)
EffectiveDate	Date(TransDate)
Salary	Real (Amount)

2.       Create a Class which performs all operations.

class testImport extends RunbaseBatch {         DialogField                 dialogFileName;         FileNameOpen            fileName;         TextIO                      textIO;         FileIOPermission          permission;         Container                  c;         TestImportTable            importTable;                 #File         #avifiles                 #define.CurrentVersion(1)        #define.Version1(1)        #localmacro.CurrentList             fileName        #endmacro }

public Object dialog() {         DialogRunbase       dialog = super();         ;         dialogFileName = dialog.addField(typeid(FileNameOpen));         return dialog; }

public Object dialog() {         DialogRunbase       dialog = super();         ;         dialogFileName = dialog.addField(typeid(FileNameOpen));         return dialog; }

public boolean getFromDialog() {         boolean ret;                         ret = super();         fileName =  dialogFileName.value();         return ret; }

// BP Deviation documented void importFromTextFile() {         SysOperationProgress    simpleProgress;         ;         permission      = new fileIOpermission(filename,#io_read);         permission.assert();         textIO          = new TextIO(filename,#io_read);         textIO.inFieldDelimiter("|");         textIO.inRecordDelimiter('\n');         simpleProgress  = SysOperationProgress::newGeneral(#AviUpdate,"Import is in progress",100);         startLengthyOperation();         while(textIO.status() == IO_Status::Ok)         {                         c  = textIO.read();                         if(conLen(c) > 1)                         {                                 setPrefix(this.caption());                         importTable.clear();                                 importTable. EmplId                = conpeek(c,1);                                 importTable. Name                  = conpeek(c,2);                                 importTable.EffectiveDate     = conpeek(c,3);                                 importTable.Salary                   = conpeek(c,4);             }                 }         endLengthyOperation(); }

public container pack() {     return [#CurrentVersion,#CurrentList];  }

public boolean unpack(container packedClass) {         Version version = RunBase::getVersion(packedClass);         ;         switch (version)     {                 case #CurrentVersion:         [version,#CurrentList] = packedClass;         break;         default:         return false;    }    return true; }

public void run() {     #OCCRetryCount     ;     if (! this.validate())           throw error("Import has been cancel");         try     {                 this.importFromTextFile(); // Import records of txt file                 }         catch(Exception::Deadlock)         {             retry;         }         catch(Exception::Error)         {                         info ('Import cancelled');         }         catch (Exception::UpdateConflict)         {                         if (appl.ttsLevel() == 0)             {                              if (xSession::currentRetryCount() >= #RetryNum)                  {                         throw Exception::UpdateConflictNotRecovered;                  }                  else                  {                         retry;                  }              }              else              {                     throw Exception::UpdateConflict;              }         }         }

public boolean validate() {         boolean     ret = true;     Container   checkType;     TextIO     errorIo;     if (! filename)     {                 ret = checkFailed("Please specify file");         }         checkType =  Docu::splitFilename(filename);     if(Conpeek(checkType,2) != 'txt' || Conpeek(checkType,2) != 'csv')     {           ret = checkFailed("file format is incorrect");     }     errorIo = new TextIO(Filename, 'r');     if (! errorIo)     {                 return checkFailed(strfmt("@SYS18678", filename));     } return ret; }

static void main(Args args) {     TestImport             importClass;     ;         importClass = new TestImport ();     if(TestImport.prompt());             TestImport.run(); }

     

     

3.       Csv or txt file format

You can create a file and for csv you save as .csv file.