Excel And CSV File import in Ax 2012
CSV File Import
Create a table like below figure and also add your needed fields.
Create a class and paste code, alter your fields and you're based on the requirement
Create a class
class RRCSVFileImport
{
// Title : CSV File import to ax 2012 R3
// Author : Rajaram.E
}
Create a method and paste code like below
private void fileImport()
{
// Table
RRCSVFileImportTable csvFileImportTbl;
// File macro
#File
// Input Output File - System class we are notable to view
IO iO;
// Variable
CustAccount custAccount;
CustName custname;
FilenameOpen filename;
Container record;
str one;
boolean first = true;
str Delimiter = ",";
int totalRecords;
// Create dialog a
Dialog dialog;
DialogField dialogFilename;
dialog = new dialog();
dialog.caption("select a file");
// get file url
dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));
//To filter the files while selecting
dialog.filenameLookupFilter(record);
dialog.run();
if(dialog.closedOk())
{
filename = dialogFilename.value();
iO = new CommaTextIo(filename,#IO_Read);
if (! iO || iO.status() != IO_Status::Ok)
{
throw error("@SYS19358");
}
record = iO.read();
while(iO.status() == IO_Status::Ok)
{
record = iO.read();
if(!record)
break;
// Retrive from container
csvFileImportTbl.CustName=conpeek(record, 1);
csvFileImportTbl.ItemName=conpeek(record, 2);
csvFileImportTbl.insert();
}
}
info("Import completed");
}
Create the main method and call the fileImport method
static void main(Args _args)
{
RRCSVFileImport objClass = new RRCSVFileImport();
objClass.fileImport();
}
CSV File format
Open your class
Output
//---------------------------------------------------------------------
Excel file import
Create a table like below figure and also add your needed fields.
Create a class and paste code, alter your fields and you're based on the requirement
Create a class
class RRExcelImport
{
// Title : Excel file import in Ax 2012.
// Author : Rajaram.E
}
Create a method
private void excelImportFile()
{
// Excel file classes
// Ms-Office Application class
SysExcelApplication application;
// workbooks class find the number of workbooks
SysExcelWorkbooks workbooks;
// get workbook name in the class
SysExcelWorkbook workbook;
// Get number of worksheets
SysExcelWorksheets worksheets;
// Get worksheet number
SysExcelWorksheet worksheet;
// Worksheet cells
SysExcelCells cells;
// Variable
COMVariantType type;
int row=1;
ItemId itemid;
Name name;
FileName filename;
// Table
RRExcelImportTable importTable;
//To filter the files while selecting
container conFilter = ["Microsoft Excel 97-2003 Worksheet (.xls)" ,"*.xlsx"];
// Dialog
Dialog dialog;
DialogField dialogFilename;
dialog = new dialog();
dialog.caption("select a file");
dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));
//To filter the files while selecting
dialog.filenameLookupFilter(conFilter);
dialog.run();
if(dialog.closedOk())
{
filename = dialogFileName.value();
// Create construct of application - casting
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
// excel file open
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
// which workbook item whant to import
workbook = workbooks.item(1);
// get number of worksheets
worksheets = workbook.worksheets();
// worksheet item name
worksheet = worksheets.itemFromNum(1);
// find the cells
cells = worksheet.cells();
do
{
row++;// skip header
name = cells.item(row, 1).value().bStr();// bStr() is string, tThere is many datatype you can change your requirements
itemId = cells.item(row, 2).value().bStr();
// assign values
importTable.CustName = name;
importTable.ItemName = itemid;
importTable.insert();
// find and variant increase column
type = cells.item(row+1, 1).value().variantType();
}
// while checking when we quit import file
while (type != COMVariantType::VT_EMPTY);
// quit application
application.quit();
}
info("Import completed");
}
Main method
public static void main(Args args)
{
RRExcelImport importExcel = new RRExcelImport();
importExcel.excelImportFile();
}
Excel file format:
Run your class
Output
