how to import excel fileto VendTable and add more fields.....

Hello everyone

i need to import data from excel to Ax VendTable
for that i have added new fields namely IFSC, MICR & Bene_Bank_Acc in Vend Table.
now if VendorAccount in Excel Match with VendorAccount in Vend Table then above 3 fields data will import into Table for respective Vendor Account.

if not matches then throw error like Vendor Account not Exist.

i have attached the VendTable word Document & Import Excel sheet for reference.
please find it & reply soon.

thanks in advance!!!

waiting for early response…

Manali

Hi Manali,

below code may help you.

static void ReadExcelFile (Args _args)
{
SysExcelApplication excel;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
vendaccount account;

vendtable vendtable;
str IFSC, MICR,Bene_Bank_Acc;
#define.filename(@‘file path’)
excel = SysExcelApplication::construct();
workbooks = excel.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);
cells = worksheet.cells();
type = cells.item(row+1, 1).value().variantType();
while (type != COMVariantType::VT_EMPTY)
{
row++;
account = cells.item(row, 1).value().bStr();
IFSC= cells.item(row, 2).value().bStr();

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

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

account = ventable::find(account);

if(!account)

{

throw error(" Vendor not present");

}

else

{

ttsbegin;

vendtable.selectforupdate(true);

vendtable.IFSC= IFSC;

vendtable.MICR = MICR;

vendtable.Bene_Bank_Acc = Bene_Bank_Acc;

vendtable.update;

ttscommit;

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

Hi, below is a simple job to insert record using excel sheet

static void toInsertVendor(Args _args)

{

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

COMVariantType type;

int row=1;

vendAccount vendAccount;

IFSC _Ifsc;

MICR _micr;

Bene_Bank_Acc _Bene_Bank_Acc;

FileName filename;

Vendtable objVendtable; Table in which record is to be inserted

;

application = SysExcelApplication::construct();

workbooks = application.workbooks();

//specify the file path that you want to read

filename = “C:\New folder\VendRecords.xlsx”;

try

{

workbooks.open(filename);

}

catch (Exception::Error)

{

throw error(“File cannot be opened.”);

}

workbook = workbooks.item(1);

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(1);

cells = worksheet.cells();

do

{

row++;

vendAccount= cells.item(row, 1).value().bStr(); //I assumed that column 1 is for VendAccount

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

_micr = cells.item(row, 3).value().bStr();

_Bene_Bank_Acc = cells.item(row, 4).value().bStr();

select objVendtable

where objVendtable.VendAccount== vendAccount;

if(objVendtable.RecId != 0)

{

ttsBegin;

select forupdate objVendtable

where objVendtable.VendAccount== vendAccount;

objVendtable.IFSC= _Ifsc ;

objVendtable.MICR = _micr ;

objVendtable.Bene_Bank_Acc = _Bene_Bank_Acc ;

objVendtable.update();

ttsCommit;

}

else

{

warning(strfmt("%1 does not exist in Vendtable",objVendtable.VendAccount));

}

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

}

while (type != COMVariantType::VT_EMPTY);

application.quit();

}

hi saddaf &Jagannath Tripathy,

Thanks for the code,its running,but didnt get correct output…

want to show total rows in excel ,vender account matches how much and mismatch accounts…

i want this as an result then what to do,plz suggest solution…

Thanks.

Manali.

Hi Manali,

I have added few more code which may help u.

static void ReadExcelFile (Args _args)
{
SysExcelApplication excel;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
vendaccount account;

int i,j,k;

vendtable vendtable;
str IFSC, MICR,Bene_Bank_Acc;
#define.filename(@‘file path’)
excel = SysExcelApplication::construct();
workbooks = excel.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);
cells = worksheet.cells();
type = cells.item(row+1, 1).value().variantType();
while (type != COMVariantType::VT_EMPTY)
{
row++;
account = cells.item(row, 1).value().bStr();
IFSC= cells.item(row, 2).value().bStr();

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

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

account = ventable::find(account);

i = 0;

j= 0;

k = 0;

j++;

if(!account)

{

i++;

throw error(" Vendor not present");

}

else

{

k++;

ttsbegin;

vendtable.selectforupdate(true);

vendtable.IFSC= IFSC;

vendtable.MICR = MICR;

vendtable.Bene_Bank_Acc = Bene_Bank_Acc;

vendtable.update;

ttscommit;

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

info("%1 rows mismatch account",i);

info("%1 rows total rows",j);

info("%1 rows match account",k);

excel.quit();
}