Share
ReportHi guys
I would like ask you that how i can import data from excel to dynamics ax r3
*This post is locked for comments
You’re offline. This is a read only version of the page.
Hi guys
I would like ask you that how i can import data from excel to dynamics ax r3
*This post is locked for comments
Hi,
If you use AX2012, you can use below code:
All responses (
Answers (
SalesLine _salesLine;
InventDim _inventDim;
SalesTable _salesTable;
InventTable inventTable;
SalesId salesId = salesTable.SalesId;
ItemId itemId;
EcoResItemSizeName inventSize;
EcoResItemColorName inventColor;
EcoResItemStyleName inventStyle;
InventSiteId inventSiteId;
InventLocationId inventLocationId;
EcoResItemConfigurationName configId;
SalesPrice salesPrice;
Qty qty;
InventDimId inventDimIdTmp;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
FilenameOpen filename;
dialogField dialogFilename;
Dialog dialog;
Integer row = 1;
str COMVariant2Str(COMVariant _cv)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R8):
// return _cv.toString() ;
return _cv.bStr() ;
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY):
return '';
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
}
;
dialog = new Dialog('Import Vendor Details');
dialogFilename = dialog.addField(ExtendedTypeStr("FilenameOpen"));
dialog.filenameLookupTitle('Import from excel.');
dialog.caption('Import From Excel');
dialogFilename.value(filename);
if(dialog.run())
{
filename = dialogFilename.value();
application = SysExcelApplication::construct();
//application.displayAlerts(false);
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);
cells = worksheet.cells();
try
{
// ttsbegin;
do
{
inventTable.clear();
_inventDim.clear();
_salesLine.clear();
row ;
itemId = cells.item(row, 1).value().bStr();
inventSize = strreplace(cells.item(row, 2).value().bStr(),'\'','');
inventColor = strreplace(cells.item(row, 3).value().bStr(),'\'','');
inventStyle = strreplace(cells.item(row, 4).value().bStr(),'\'','');
configId = cells.item(row, 5).value().bStr();
qty = cells.item(row, 6).value().double();
inventSiteId = cells.item(row, 7).value().bStr();
inventLocationId = cells.item(row, 8).value().bStr();
salesPrice = cells.item(row, 9).value().double();
type = cells.item(row 1, 1).value().variantType();
_salesLine.initValue();
/* Init SalesLine from SalesTable*/
_salesLine.SalesId = salesId;
_salesLine.initFromSalesTable(salesTable);
/* Set SalesLine Item*/
_salesLine.ItemId = itemId;
//Initializing the sales line from inventory
_salesLine.initFromInventTable(InventTable::find(_salesLine.ItemId));
//Setting and creating inventory dimensions
//I have given the warehouse in
if(Inventlocation::find(inventLocationId).InventLocationId != "")
{
_inventDim.inventBatchId = "General";
_inventDim.InventSiteId = InventLocation::find(inventLocationId).InventSiteId;
_inventDim.InventLocationId = Inventlocation::find(inventLocationId).InventLocationId;
_inventDim.wMSLocationId = "In_01";
_inventDim.InventSizeId = inventSize;
_inventDim.InventStyleId = inventStyle;
_inventDim.InventColorId = inventColor;
_inventDim.InventStatusId = "normal";
_inventDim.ConfigId = configId;
}
_salesLine.InventDimId = InventdIm::findOrCreate(_inventDim).inventDimId;
// _salesLine.initFromProductDimensions(inventDim);
inventDimIdTmp = _salesLine.InventDimId;
/* Set Sales line value*/
_salesLine.SalesQty = qty;
// _salesLine.SalesPrice = salesPrice;
/* End*/
/* Look for trade agreement if not found base selling price will be use*/
//_salesLine.setPriceDisc(inventDim);
/* End*/
/* Create SalesLine*/
//_salesLine.CreateLine(NoYes::Yes,NoYes::Yes,NoYes::Yes,NoYes::Yes,NoYes::Yes,NoYes::Yes,NoYes::Yes );
_salesLine.createLine(NoYes::Yes, // Validate
NoYes::Yes, // initFromSalesTable
NoYes::Yes, // initFromInventTable
NoYes::Yes, // calcInventQty
NoYes::Yes, // searchMarkup
NoYes::Yes ); //
_salesLine.SalesPrice = salesPrice;
_salesLine.LineAmount = _salesLine.calcLineAmount();
_salesLine.InventDimId = inventDimIdTmp;
_salesLine.update();
}
while (type != COMVariantType::VT_EMPTY);
workbooks.close();
application.quit();
// ttscommit;
SalesLine_ds.research();
InventDim_ds.research();
InventDim_ds.refresh();
}
catch
{
try{
workbooks.close();
application.quit();
}
catch{
info('error in importing...');
}
}
}
Can you confirm you are using ax 2012? Sounds like 365fo error to me.
Hi Musaad,
There are many methods to import data from excel into AX.
DIXF the best way to import Excel.
In general you would have to install the Micsosoft Office Plug In on the Client. Once installed you can then open your Excel and use the Dynamics AX Plug In which enables you to exchange all table data as well as some data sets. However you must be Aware of the interdependencies and Relations of the tables.
Another way is, working with the Dynamics Import Export Framework, which has some preconfigured entitites. It also supports Format Excel.
Hi Musaad,
You can import data by using Job and by using form buttons and so..
You can you this code to import data form excel to ax. Try this code by using Job or Button(Clicked Method).
Job:
static void InsertRecords(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
int row =1;
int i=0;
InventTable inventTableLoc;
ItemId itemid;
TransDate transdate;
str NewField;
Dialog dialog;
DialogField dialogField;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
dialog = new Dialog("FileOpen");
dialogfield = dialog.addField(extendedTypeStr(Filenameopen), "File Name");
dialog.run();
if (dialog.run())
{
filename = (dialogfield.value());
}
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
{
ttsBegin;
row++;
itemid = cells.item(row, 1).value().bStr();
NewField = cells.item(row, 2).value().bStr();
inventTableLoc = InventTable::find(itemid,true);
inventTableLoc.NameAlias = NewField;
inventTableLoc.insert();
ttsCommit;
i++;
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
info("Done");
info(strFmt("%1", i));
}
Thanks
Suresh
Hi Musaad,
What data do you want to import from Excel to AX?
In AX2012 you have the AIF and the DIXF available that can help you importing the data.
What might fit best depends on what you try to import and do.
Best regards,
Ludwig
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156