Import CSV files

As you have seen the Exporting Complete Table Record into CSV in the last post. Same way here i have written Code for Importing CSV into Table

static void Import_CSV(Args _args)
{
common TableBuffer;
DictTable dictTable = new DictTable(tableNum(TableName)); // Define Table Name
str TableName = dictTable.name();
DictField dictField;
int i;
CommaIo file;
container line;
str path = 'C:\\Users\\saadullah\\Documents\\'+TableName+'.csv'; // Directory Path
#define.filename(path)
#File
file = new CommaIo(#filename, #io_read);
if (!file || file.status() != IO_Status::Ok)
 {
 throw error("File cannot be opened.");
 }
TableBuffer = dictTable.makeRecord();
while (file.status() == IO_Status::Ok)
 {
 line = file.read(); // Selecting File values line by line as assigning to Container
 if (line != conNull())
 {
 // Looping the Table Field
 // we have Defined - 15 from the Counting of Fields 
 // we do not Require 15 System Field such as RecId, RecVersion, Created By, Created Datetime, Modified By, etc...
 for( i =1; i <= dictTable.fieldCnt() -15; i++)
 {
 // Selecting the Fields
 dictField = new DictField(dictTable.id(), dictTable.fieldCnt2Id(i));
 // Assigning the Container values to the Selected Table Field
 TableBuffer.(dictField.id()) = conpeek(line, i);
 }
 // Inserting Record 
 TableBuffer.doInsert();
 }
 }
}
Advertisements

Import from Excel file using X++ in AX 2009

//Apply this method in any Button Override Method.
// This method is used to Import the Excel Data to AX Table
//
//
// Excel File Contains 3 Columns which is EquipmentId, Cost, EffectiveDate
// Same way here we have created Code For the particular Three field....
public void ImportData()
{
 SysExcelApplication application;
 SysExcelWorkbooks workbooks;
 SysExcelWorkbook workbook;
 SysExcelWorksheets worksheets;
 SysExcelWorksheet worksheet;
 SysExcelCells cells;
 COMVariantType type;
 System.DateTime ShlefDate;
 FilenameOpen filename;
 dialogField dialogFilename;
 Dialog dialog;
 #AviFiles
 // Progress Bar
 SysOperationProgress progress = new SysOperationProgress(1, NoYes::Yes);
 TestXls _TestXls; // Table name and decleration
 str equipId;
 str cost;
 str effectiveDate;
 int datevalue, row = 0;
 real costr;
 Date dater;
 #Excel
 str COMVariant2Str(COMVariant _cv,
 int _decimals = 1,
 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(_cv.date(),
 123,
 2,
 1,
 2,
 1,
 4);
 case (COMVariantType::VT_EMPTY):
 return "";
 default:
 throw error(strfmt("@SYS26908",
 _cv.variantType()));
 }
 return "";
 }
 ;
 dialog = new Dialog("ExcelUpload");
 dialogFilename = dialog.addField(typeId(FilenameOpen));
 dialog.filenameLookupFilter(["@SYS28576",#XLS, "@SYS28576",#XLSX]);
 dialog.filenameLookupTitle("Upload from Excel");
 dialog.caption("Excel Upload");
 dialogFilename.value(filename);
 if(!dialog.run()) Continue reading