Announcements
No record found.
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 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
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
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.
There are many methods to import data from excel into AX.
DIXF the best way to import Excel.
I tried with this code but there is error saying SysExcelWorkbook does not denote any class table.....
how can i clear this
Can you confirm you are using ax 2012? Sounds like 365fo error to me.
Hi,
If you use AX2012, you can use below code:
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...'); } } }
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
Congratulations to our community stars!
Expanding mentorship, skilling, and AI innovation
These are the community rock stars!
Stay up to date on forum activity by subscribing.
