Vasanth Arivali's Blog

Import Excel Data into Dynamics AX 2009

Posted on: August 19, 2010

Here i post code for Import Excel Data into Dynamics using X++ code.

Here the code written in Command Button clicked event, and also i  added the Excel format below of this post which i used.

void clicked()
SysExcelApplication             application;
SysExcelWorkbooks               workbooks;
SysExcelWorkbook                workbook;
SysExcelWorksheets              worksheets;
SysExcelWorksheet               worksheet;
SysExcelCells                   cells;
COMVariantType                  type;
System.DateTime                 ShlefDate;

FilenameOpen                    filename;
dialogField                     dialogFilename;

Dialog                          dialog;

//Table Declarations Starts

InventSize                      _InventSize;
InventBatch                     _InventBatch;
InventSerial                    _InventSerial;
InventTable                     _InventTable;
VendParameters                  _vendParameters;

//Table Declartions Ends

InventBatchId                   batchNumber;
InventBatchExpDate              expdate;
itemId                          itemid;
TransDate                       poddate;
CertificatesofSterilization  Certs;
CertificatesofAnalysis       CertiAnalysis;
InventSizeId                    InventSize;
ConfigId                        _ConfigId;
InventColorId                   _InventColorId;
InventSiteId                    _InventSiteId;
WMSLocationId                   _WMSLocationId;
InventLocationId                _InventLocationId;
WMSPalletId                     _WMSPalletId;
NoYesId                         ClosedTransactions;
NoYesId                         ClosedTransQty;
str                             pONo;
str                             srNo;
real                            quantity;
int                             row;
InventBatchExpDate              ShelfLifeDate;


// convert into str from excel cell value

str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
switch (_cv.variantType())
case (COMVariantType::VT_BSTR):
return _cv.bStr();

case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_DATE):
return date2str(,123,2,1,2,1,4);

case (COMVariantType::VT_EMPTY):
return “”;

throw error(strfmt(“@SYS26908”, _cv.variantType()));
return “”;


dialog              =   new Dialog(“Excel Upoad”);
dialogFilename      =   dialog.addField(typeId(FilenameOpen));

dialog.filenameLookupTitle(“Upload from Excel”);
dialog.caption(“Excel Upload”);



filename            =   dialogFilename.value();

application         =   SysExcelApplication::construct();
workbooks           =   application.workbooks();

catch (Exception::Error)
throw error(“File cannot be opened.”);

workbook            =   workbooks.item(1);
worksheets          =   workbook.worksheets();
worksheet           =   worksheets.itemFromNum(1);
cells               =   worksheet.cells();



pONo                    =   COMVariant2Str(cells.item(row, 1).value());
itemid                  =   COMVariant2Str(cells.item(row,2).value());
InventSize         =   COMVariant2Str(cells.item(row, 3).value());
batchNumber             =   COMVariant2Str(cells.item(row, 4).value());
poddate                 =   cells.item(row, 5).value().date();
expdate                 =   cells.item(row, 6).value().date();
srNo                    =   COMVariant2Str(cells.item(row, 7).value());
Certs                =   str2enum(Certs,cells.item(row, 8).value().bStr());
CertiAnalysis        =   str2enum(CertiAnalysis,cells.item(row, 9).value().bStr());
quantity                =   cells.item(row, 10).value().double();
_ConfigId               =   COMVariant2Str(cells.item(row, 12).value());
_InventColorId          =   COMVariant2Str(cells.item(row, 13).value());
_InventSiteId           =   COMVariant2Str(cells.item(row, 14).value());
_WMSLocationId          =   COMVariant2Str(cells.item(row, 15).value());
_InventLocationId       =   COMVariant2Str(cells.item(row, 16).value());
_WMSPalletId            =   COMVariant2Str(cells.item(row, 17).value());
ClosedTransactions      =   str2enum(ClosedTransactions,cells.item(row, 18).value().bStr());
ClosedTransQty          =   str2enum(ClosedTransQty,cells.item(row, 19).value().bStr());

if(row > 1)

//Insert into InventSize Table

select firstonly _InventSize where _InventSize.ItemId == itemid && _InventSize.InventSizeId == InventSize;

_InventSize.InventSizeId     =      InventSize;
_InventSize.ItemId           =      itemid;
warning(strfmt(“Item Id and InventSize (%1   –   %2) already exists”,itemid,InventSize));

// Insert into InventBatch Table

_InventBatch.inventBatchId      =       batchNumber;
_InventBatch.itemId             =       itemid;
_InventBatch.prodDate           =       poddate;
_InventBatch.expDate            =       expdate;

// Insert into InventSerial Table

_InventSerial.InventSerialId    = srNo;
_InventSerial.ItemId            = itemid;
_InventSerial.ProdDate          = poddate;

info(strfmt(“Item(%1) uploaded successfully”,itemid));


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

}while (type != COMVariantType::VT_EMPTY);


Error(“Upload Failed”);



Excel Format Image for My Import

My Excel Format

These are the details i used to import from Excel.


6 Responses to "Import Excel Data into Dynamics AX 2009"

Hi Vasanth!

I only want to make clear, for you and the AX community, that your code is another way to do something that you currently can do through Definition groups.

Administration–>Periodic–>Data export/import–>Definition groups

For more specifications:

Keep on posting!



Thanks for your comment.

This is not for normal format of Excel import.
Someone give the input as these kind of files, in that they are not belongs to one table, and also it simultaneously affect multiply tables.
At that time we cant use the Data Export method.
Here in my code i used to import one excel data in to five different table.
For this kind of situation, using this kind of code is the best way.

Thanks & Regards
Vasanth Arivali

What does this line do? Im confused where it is placed.

while (type != COMVariantType::VT_EMPTY);

This line check whether the cell is Empty or Not.

Hi, I’m trying to use your code but am stucked, would u be able to guide me on this ?

tell me, how could i help you on this post, where you stucked?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


What’s happening?

Error: Twitter did not respond. Please wait a few minutes and refresh this page.


Join 12 other followers

%d bloggers like this: