AX 2012 - Import data from .csv file

Today we will learn on how to import data from a .csv file into AX 2012 through a Job

1)     Create a table TEC_Import

Emp_Age        int

Emp_Dob        date

Emp_Id           str 20

Emp_Name     str 20

2)     Write a job

static void Import(Args _args)

{

    #File

    IO              iO;

    TEC_Import      import;

    FilenameOpen    filename = "c:\\import.csv";//To assign file name

    Container       record;

    boolean         first = true;

    boolean         ifexist = false;

    TEC_ID          empid;

    ;

    iO = new CommaTextIo(filename,#IO_Read);

    if (! iO || iO.status() != IO_Status::Ok)

    {

        throw error("@SYS19358");

    }

     ttsBegin;

    while (iO.status() == IO_Status::Ok)

    {

        record = iO.read();// To read file

        if (record)

        {

            if (first)  //To skip header

            {

                first = false;

            }

            else

            {

                empid = conPeek(record, 1);

                select Emp_Id from import where import.Emp_Id == empid;

               /*if(import.Emp_Id)

                {

                first = false; //skip those records whose id's are same

                info("Record already exist");

                }

                else

                {*/

                    //  read container to insert record in your table…….

                import.Emp_Id = conPeek(record, 1);

                import.Emp_Name = conPeek(record, 2);

                import.Emp_Age = str2int(conPeek(record, 3));

                import.Emp_Dob = str2Date(conPeek(record, 4),123);

                    //if(import.validateWrite())

                    //{

                         import.insert();

                      //   info("Inserted successfully");

                    //}

                    //else

                    //{

                      //  info("Insertion Failed due to exception");

                   // }

                //}

            }

        }

       }

               ttsCommit;

}

AX 2012 - Import data from excel (.xslx) file

Today we will learn on how to import .xslx file into AX 2012 using a job.

1)     Create a table TEC_Import:

Emp_Age        int

Emp_Dob       date

Emp_Id           str 20

Emp_Name     str 20

2)     Write a AX 2012 job to import data into the above created table:

static void ImportFromExcel(Args _args)

{

#AviFiles

SysOperationProgress progress = new SysOperationProgress();

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

COMVariantType type;

FileIoPermission perm;

Name name;

FileName filename;

TEC_Import importTable;   //your table name

int row;

boolean first = true;

TEC_AGE _EmpAge;     //EDT

TEC_ID _EmpID;      //EDT

TEC_NAME _EmpName;  //EDT

TEC_DOB _DOB;       //EDT

 #define.Filename('c:\\Book2.xlsx')     //specify the file path that you want to read

 #define.FileMode('R')

;

 perm = new FileIOPermission(#FileName, #FileMode);

 perm.assert();

 application = SysExcelApplication::construct();

 workbooks = application.workbooks();

   

try

{

workbooks.open(#Filename);

}

catch (Exception::Error)

{

throw error("File cannot be opened.");

}

workbook = workbooks.item(1);

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(1); //Here 1 is the worksheet Number

cells = worksheet.cells();

progress.setCaption("Sales target data import...");

progress.setAnimation(#AviTransfer);

ttsBegin;

do

{

row++;

    if(row==1)

    {

       first = false; //to skip the header

    }

    else

    {

_EmpID   = cells.item(row, 1).value().bStr();

_EmpName = cells.item(row, 2).value().bStr();

_DOB     = cells.item(row, 3).value().date();

_EmpAge  = any2int(cells.item(row, 3).value().toString());

importTable.Emp_Id          = _EmpID;

importTable.Emp_Name        = _EmpName;

importTable.Emp_Dob         = _DOB;

importTable.Emp_Age         = _EmpAge;

importTable.insert();

         }

type = cells.item(row+1, 1).value().variantType(); 

}

while (type != COMVariantType::VT_EMPTY);

ttsCommit;

application.quit();

}