In questo post vediamo come importare dati da un file .CSV in AX, inserendo i dati in un tabella custom chiamata "MyTable" avente due campi "Field1" e "Field2" di tipo stringa. Il metodo sottostante effettua l'operazione leggendo i dati dal file C:\TEMP\test.csv
static void CSVLoadData(Args _args)
{
TextIo inFile;
container line;
Counter records;
SysOperationProgress simpleProgress;
container fileContainer;
Counter loopCounter;
Mytable Mytable;
CustTable CustTable;
InventTable InventTable;
str filename;
#OCCRetryCount
#AviFiles
#File
if (curExt() != 'DAT')
{
throw error("This script must run in the DAT company!");
}
filename = WinAPI::getOpenFileName(0,
[WinAPI::fileType(#csv),#AllFilesName + #csv],
@'C:\users\',
"@SYS53008"
);
if(!filename)
{
return;
}
try
{
//Caricamento file per righe, ogni riga è memorizzata in un container
inFile = new TextIo(filename, 'r');
inFile.inRecordDelimiter('\n');
inFile.inFieldDelimiter(';');
while (inFile.status() == IO_Status::OK)
{
fileContainer += [infile.read()];
}
inFile = null;
}
catch
{
throw error(strFmt("@SYS18678", filename));
}
simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, "Importazione...", conLen(fileContainer));
ttsBegin;
records = 0;
//il ciclo parte da 2 perchè si suppone che la prima riga sia di intestazione
for (loopCounter = 2; loopCounter <= conLen(fileContainer) - 1 ; loopCounter++)
{
Mytable.clear();
simpleProgress.incCount();
try
{
line = conPeek(fileContainer, loopCounter); // lettura della riga i-esima
Mytable.Field1 = conPeek(line, 1);
Mytable.Field2 = conPeek(line, 2);
Mytable.insert();
records++;
simpleprogress.setText(strfmt("@SYS76835", loopCounter, Mytable.RecId));
}
catch (Exception::Deadlock)
{
if (xSession::currentRetryCount() < #RetryNum)
{
retry;
}
}
}
ttsCommit;
info(strFmt("Inseriti %1 record", records));
}
Per leggere i dati da EXCEL possiamo invece usare il seguente job:
static void EXCELLoadData(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
int row;
container TableDataContainer,
line;
SysOperationProgress simpleProgress;
Counter BLrecords,ITrecords,ITRecordsNotFound;
Counter loopCounter;
InventTable inventTable;
ItemId itemId;
boolean previewMode = true; //EXECUTION MODE
#AviFiles
#File
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(_cv.date(),123,-1,-1,-1,-1,-1);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
filename = WinAPI::getOpenFileName(0,
[WinAPI::fileType(#xlsx),#AllFilesName + #xlsx],
strFmt(@'C:\users\%1\Desktop',WinApi::getUserName()),
"@SYS53008"
);
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
application.quit();
throw error("@SYS19358");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 1;
//read data
do
{
row++;
line = [cells.item(row, 1).value().bStr()
,COMVariant2Str(cells.item(row, 2).value())
,COMVariant2Str(cells.item(row, 10).value(),-1,-1,-1,-1)];
TableDataContainer += [line];
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
