Thursday, September 20, 2012

How to Import CSV files and how make Log files in Ax 2012 through X++

Through following code we can import data in Data base and can make log file for details !!!!

static void CsvFileReadAndInsert(Args _args)
{
    Dialog      dialog  = new Dialog();
    DialogField dialogField;
    AsciiIo importFile,logFile;
    str filePath,fileNameOnly;
    filetype type;
    container record;
    CustTable custTable;
    CustAccount custAccount;
    CustName custName;
    CustGroupId custGroupId;
    CustCurrencyCode custCurrencyCode;
    DirPartyTable dirPartyTable;
    int totalRecords, totalInserted,totalFail;
;
    dialogField=dialog.addField(extendedTypeStr(FilenameOpen),"Select File","Select file to import");
    dialog.caption("File Picker");
    dialog.filenameLookupFilter(['csv','*.csv']);
    if(!dialog.run())
        return;
    [filePath, fileNameOnly, type] = fileNameSplit(dialogField.value());
    importFile = new AsciiIo(dialogField.value(), 'R');
    if((!importFile) || (importFile.status() != IO_Status::Ok))
    {
        warning("Error in opening import file");
        throw(Exception::Error);
    }
    importFile.inFieldDelimiter(",");
    logFile = new AsciiIo(filePath + "\\Customer Import Log File.txt", 'a+');
    if((!importFile) || (importFile.status() != IO_Status::Ok))
    {
        warning("Error in opening log file");
        throw(Exception::Error);
    }
        logFile.write(strfmt("Log Details"));
        logFile.write(strfmt("Import Date: %1  Import Time: %2",date2str(today(),123,2,-1,2,-1,4),time2str(timenow(),1,1)));
    try
    {
    ttsbegin;
    record = importFile.read();
    while(importFile.status() ==  IO_Status::Ok)
    {
        record = importFile.read();
        if(!record)
            break;
        totalRecords = totalRecords + 1;
        custAccount = "";
        custAccount = conpeek(record,1);
        custName=conpeek(record,2);
        custCurrencyCode=conpeek(record,2);
       
        if(custAccount == "")
        {
             logFile.write("Account Num is blank");
             totalFail = totalFail + 1;
             continue;
        }
        select custTable
        where custTable.AccountNum == custAccount;
        if(custTable.RecId)
        {
            logFile.write((strfmt("Customer %1 already exists.",custAccount)));
            custTable.clear();
            custTable = null;
            totalFail = totalFail + 1;
            continue;
        }
            custTable.AccountNum = custAccount;
            custTable.CustGroup=custGroupId;
            custCurrencyCode=custCurrencyCode;
            custTable.insert();
           
       
       
        totalInserted = totalInserted + 1;
    }
    logFile.write((strfmt("Import Log:")));
    logFile.write((strfmt("Total records: %1,added/imported in to the database: %2 and rejected / NOT imported into the database: %3",totalRecords,totalInserted,totalFail)));
    ttscommit;
    }
    catch(Exception::Error)
    {
        Throw(Exception::Error);
    }
}

Happy Coding !!!!

No comments:

Post a Comment