Showing posts with label Import csv file in AX 2012. Show all posts
Showing posts with label Import csv file in AX 2012. Show all posts

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 !!!!