AX 2012 - Importazione dati da file CSV / EXCEL

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();  
 }