Search This Blog

Monday, March 5, 2012

AX2012 Import Ledger Journal Trans from CSV


Smaple code to import Ledger Journal Trans from CSV file.

Before doing this code, I have created parm Method of LedgerJournaTable which have LedgerJournalTable record with journal Num.

Please change the Container positions according to your file in createJournalTrans method.



class NavisionPayrollImportDialog extends RunBaseBatch
{
    DialogRunbase                               dialog;
    DialogField                                 dialogFilename;
    DialogField                                 dialogPostingdate;

    Filename                                    filename;
    TransDate                                   postingdate;
    LedgerJournalId                             journalId;
    LedgerJournalTable                          ledgerJournalTable;

    LedgerJournalEngine                         journalEngine;

    #define.CurrentVersion(2)
    #localmacro.CurrentList
        filename,
        postingdate
    #endmacro
}

//Parm method that stores Ledger Journal Table

public LedgerJournalTable  parmParmLedgerJournalTable(LedgerJournalTable  _parmLedgerJournalTable = ledgerJournalTable)
{
    ledgerJournalTable    = _parmLedgerJournalTable;
    return ledgerJournalTable;
}

// Main Method

public static void main(Args args)
{
    NavisionPayrollImportDialog  navisionPayrollImportDialog;
    ledgerJournalTrans             localJournalTrans;
    ;
    navisionPayrollImportDialog =NavisionPayrollImportDialog::construct();

    if(args.dataset() ==tableNum(LedgerJournalTrans))
    {
       localJournalTrans=args.record();
       navisionPayrollImportDialog.parmParmLedgerJournalTable(localJournalTrans.ledgerJournalTable());
    }

    if (navisionPayrollImportDialog.prompt())
    {
        navisionPayrollImportDialog.run();
    }
}



// Run Method where it initiates Import functionality
public void run()
{
    if(this.validate())
    {
        journalEngine = new LedgerJournalEngine();
        journalEngine.ledgerJournalTable(ledgerJournalTable);
        this.importPayrollFromNavision();
    }
}


private void importPayrollFromNavision()
{
    AsciiIo                    csvFile;
    container                  readCon;
    AxLedgerJournalTrans        journalTrans;
    LedgerJournalACType         accountType;
    #File
    ;
    try
    {
        new FileIOPermission(filename, #io_read).assert();
        csvFile = new AsciiIo(filename,#io_read);
        CodeAccessPermission::revertAssert();
        csvFile.inFieldDelimiter(',');
        csvFile.inRecordDelimiter(#delimiterCRLF);
        if (csvFile)
        {
            while (csvFile.status() == IO_Status::OK)
            {
                readCon = csvFile.read();
                if (readCon)
                {
                    this.createJournalTrans(readCon);
                }
            }//while close

        }// if csv file close

    }
    catch (Exception::Deadlock)
    {
        retry;
    }
    catch(Exception::Error)
    {
       throw error(strFmt("File Read Error,fileName:%1",filename));
    }
    csvFile.finalize();

}// method close

// create the journal Trans.

private void createJournalTrans(container   _c)
{
    LedgerJournalTrans      ledgerJournalTrans;

    LedgerAccountContract   ledgerAccountContract;
    DimensionStorage        dimensionStorage;
    AxLedgerJournalTrans    axJournalTrans;
    DimensionAttributeValueContract attributeValueContract;

    str                     acType;
    MainAccountNum          mainAccount;
    Amount                  amount;
    str                     department;

    mainaccount                     = strLRTrim(conPeek(_c,5));
    if(mainAccount::findByMainAccountId(mainaccount).RecId>0)
    {
        journalEngine.initValue(ledgerJournalTrans);
        ledgerJournalTrans.Company        = curext();
        if(strLRTrim(conPeek(_c,12))=="")
        {
            ledgerJournalTrans.CurrencyCode   = CompanyInfo::standardCurrency();
        }
        else
        {
            ledgerJournalTrans.CurrencyCode =strLRTrim(conPeek(_c,11));
        }
        ledgerJournalTrans.initForCurrency(ledgerJournalTable);

        ledgerAccountContract = new LedgerAccountContract();
        ledgerAccountContract.parmValues(new List(Types::Class));

        axJournalTrans = new AxLedgerJournalTrans();
        axJournalTrans.ledgerJournalTrans(ledgerJournalTrans);
        axJournalTrans.parmJournalNum(ledgerJournalTable.JournalNum);
        axJournalTrans.parmTransDate(postingdate);
        axJournalTrans.parmDocumentDate(str2Date(conPeek(_c,7),321));
        axJournalTrans.parmAccountType(LedgerJournalACType::Ledger);
        axJournalTrans.parmOffsetAccountType(LedgerJournalACType::Ledger);
        axJournalTrans.parmTxt(strLRTrim(conPeek(_c,10)));
        amount = str2num(conPeek(_c,13));

        if (amount > 0)
            axJournalTrans.parmAmountCurDebit(amount / 100);
        else
            axJournalTrans.parmAmountCurCredit(amount / 100);

        department = strLRTrim(conPeek(_c,14));

        ledgerAccountContract.parmMainAccount(mainAccount);
        if (department)
        {
            attributeValueContract = new DimensionAttributeValueContract();
            attributeValueContract.parmName("@SYS850");
           attributeValueContract.parmValue(department);

            ledgerAccountContract.parmValues().addEnd(attributeValueContract);
        }

        dimensionStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(ledgerAccountContract);

        axJournalTrans.parmLedgerDimension(dimensionStorage.save());

        axJournalTrans.currentRecord().insert();
    }
    else
    {
        info(strFmt("Main Account Id is Not available %1",mainaccount));
    }

}





20 comments:

  1. Could you tell me what's under @GNX279?

    ReplyDelete
  2. I changed the string in the blog, thanks.

    ReplyDelete
  3. Hi Krishna

    I want to convert a amount value (seprated by a comma e.g: 5289,65)in the .csv file to real value.
    when iam reading the value from csv file and converting to num using str2num, iam missing the decimal values.

    can you suggest me a way to do it ?

    ReplyDelete
  4. Hi Gangadhar,
    The file should be having the data as following.
    "1","krishna","1234","10,70"
    This is how the CSV file should like, you have to ask to customer as to provide the data in between the quotations, if they didnt provide, you cant identify which value is which one.

    ReplyDelete
    Replies
    1. Hi krishna,

      Actually the data is ";" seperated and i know the column numbers in which column which data is.(the csv file is in a excel)
      I am retreving the specified column value (using container) that value is like 4567,65.

      Delete
  5. Before passing the value to str2num please convert that string bys using the following code then it will returns the decimals.
    str2num(strReplace(5289,65,",","."));

    I hope this will solves the issue

    ReplyDelete
  6. thank you.
    i have already tried the same thing its working :)

    ReplyDelete
  7. Hi,

    How to schedule a job in ax 2012, Let us suppose that I have class which generates a CSV file, the user want to schedule a job for that class without using a Runbase batch extension or customizing the class. The class should not be modified and the user should be able to schedule the job with the help of AX client. Is it possible in AX 2012.

    Please help me on this.

    Thanks,

    Ashish

    ReplyDelete
    Replies
    1. Hi Ashish,
      I dont think without customizing the class you can add in the batch job.
      if you dont want to customize the existing class create a new class which extends runbase batch and call that class in this class run method.

      Other wise you have create BOF classes to use Batchjobs.
      you can see the following post

      http://krishhdax.blogspot.dk/2011/09/ax2012-bof-business-operation-framework.html

      Regards,
      krishna.

      Delete
  8. Hi Krish,
    Thanks for ur answer.
    How to import multiple journals and their lines from Excel Using X++.

    Thanks,
    Ashish

    ReplyDelete
    Replies
    1. Hi Ashish,
      you can see the sample of code how to read the Excel file

      http://krishhdax.blogspot.dk/2012/01/imports-data-from-excel-in-ax2012-using.html

      Regards,
      krishna.

      Delete
  9. Hi Krish,

    I've seen the link but what exactly I need is, I should be able to import the journal header and its lines(journal Trans as in above post), how to insert a new header once the previous lines are inserted. If there is any validation failures i should be able to throw the error with Row number.
    Plz help me on this.

    Thanks.

    ReplyDelete
    Replies
    1. that means you will have the one file with multiple journals...and you want to create the multiple journals from the same file...so in the file do you have any identification that this journal is this,

      if you have then while creating the journallines make sure you have to validate that it was the new journal and existing journal, and based on that you can create and you have to create the counter variable and increment the counter variable for each line and reset the counter to 0 when you created the new journal....

      I hope you got the way to implement.

      Regards,
      krishna.

      Delete
    2. I will try this
      Thanks Krish

      Delete
  10. Hi , Can you help me to import data from excel in LedgerjournalTrans table in ax 2012.

    Thanks
    Mayur

    ReplyDelete
    Replies
    1. Hi you can refer to the post how to use excel to import data into AX.

      http://krishhdax.blogspot.dk/2012/01/imports-data-from-excel-in-ax2012-using.html

      Regards,
      krishna.
      krishna.dynamics@gmail.com

      Delete
  11. I could give the format you use in the csv
    to know what data and in what order the raisins
    thank you very much

    ReplyDelete
  12. Hi

    I need to import to records with a vendor account type.

    Do you have amy examples with this?

    Regards Martin

    ReplyDelete
  13. Hi Krishna,

    I am getting error while importing csv file only in a specific case where file is location in folder XXX in user desktop.
    If i put file under C: drive it works fine.
    Please could you tell how to allow AX to read file from any place in the local machine.

    Used permssion also like
    permission = new FileIOPermission(filename, #io_readwrite);
    permission.assert();

    Tried AsciiIO, CommaIO, CommaTextIO and TextIO. Got error like Object was not initializing.

    Thanks,
    Ambanna Yatnal

    ReplyDelete

Thanks for visiting my blog,
I will reply for your comment within 48 hours.

Thanks,
krishna.