Import COA and Account Structures from standard Excel add-in

(0) Share

Report

Posted on by Ludwig Reinhard Microsoft Employee

Hi,

I am wondering whether it is possible to import a chart of accounts including the elements that can be found in the main account legal entity table which are required for example to fix cost centers/departments, etc. from Excel into Dynamics AX.

A second issue related to the first question is whether it is possible to import the elements that can be found in the account structure configuration window in General Ledger.

Any hint would be highly appreciated.

Many thanks,

Ludwig

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Community Member on at

Like (0)

Report

Yes it is possible to use Excel add Ins to import Main Account into Dynamics AX 2012

Data Import Export Framework will also be a good tool.

but as per my understanding for Account structure configuration it is not recommended to have data import, we should do it manually

please verify and update us with your findings

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hello Nitesh Ranjan,

Thank you very much for your feedback.

Can you give me a hint how I might be able to import company specific features such as fixed financial dimension values via the Excel-Addin into my chart of accounts as I have not been able to do it properly?

Using the Data Import Export Framework for the account structure configuration is not an option for me as I do not always have somebody with some technical background around that is able to help me with the import. At the same time a manual setup / change of the account structures is difficult because I plan to setup more than 1000 rules to ensure that ledger postings do not go wrong. Setting up this manually takes a very long time :-( Do you know any other possibility how for example an ordinary accountant can setup and change complex account structures quickly?

Hope you can help.

Ludwig

Was this reply helpful? Yes No
Suggested answer

Chandu Naresh 982 on at

Like (0)

Report

Hi Ludwig,

You can use the excel add-in to import main accounts. But first you need to create the name of the Chart of accounts

Go to GL-> Chart of Accounts -> Chart of Accounts

Create a new Chart of Accounts (COA) and enter the Name & Description (For ex: Assume you named it "General COA")

Now go to Excel Add-in and Click Add Tables

Select the table "MainAccount" and click OK

By default the 3 mandatory fields "Chart of accounts" , "Main Account" & "Name" are displayed.

You can drag & drop all the required fields as may be necessary

In "Chart of Accounts" column enter the value as "General COA" (or the name you may have chosen for the COA set)

in the "Main Account" field enter the ledger account code.

in the "Name" field enter the name as required for the ledger account

repeat this for all Ledger accounts. You may alternatively copy-paste the values.

Click on Publish and all the records are published into the COA.

You are done. Good Luck :)

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hello Chandu Naresh Chilukuri,

Many thanks for your answer.

Importing some ledger accounts via the Excel-Addin works fine in the you describe

... yet, my problem ist that I need to import values in the MainAccountLegalEntity table and have no clue how this can be realized.

Would be great if you could let me know what I am possibly doing wrong.

Many thanks and best regards,

Ludwig

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 301,053 Super User 2025 Season 2 on at

Like (0)

Report

HI Ludwig,

The table MainAccountLegalEntity will give you probably an error in the Excel client because of an unsupported field type? Then it is not possible to use the Excel add-in.

You can create a Main account service (AIF) to be able to use it in Excel addin.

You stated that you don't use the Data Import Export Framework because you need a technical resource. In many cases this is not needed. Usually it is just setup of the source data format and the processing group. The Main account entity in DIXF supports the default dimensions.

The account structures are based on several tables with various links on record id's, It is not possible to import this in an easy way. So unfortunately it can be a lot of work, but the way to do it is using the AX client. Investigating and/or developing other options might take too much time either.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi Ludwig!

I see that your original question was a long-long time ago, but I see no solution on the replies.

I think this procedure could be used to solve your second point: the account structures:

http://www.asterax.eu/2016/08/loading-account-nodes-into-account.html

Cheers,

mrt

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

This job should do it.

static void CSVImportMainAccountLegalEntity(Args _args)
{

MainAccountLegalEntity QQ ;

Dialog dialog;
DialogField dialogFileName;
SysOperationProgress simpleProgress;
Filename filename;

AsciiIo csvFile;

container readCon;
Container filterCriteria;
int numLines;
int cnt;
FileIOPermission permission;
TextIO textIO;
NoYesId Data;

int strcnt=0;

SelectableDataArea CompanyInfo_DataArea;
str LedgerChartOfAccounts_Name;
MainAccountNum MainAccount_MainAccountId;
str TaxCode;
str TaxDirection;
str TaxGroup;
str TaxItemGroup;
textBuffer tb = new textBuffer();
#File
#avifiles
;

dialog = new Dialog("Select CSV File - MainAccountLegalEntity");

dialogFileName = dialog.addField(ExtendedTypeStr(FilenameOpen), "File Name");
filterCriteria = ["*.csv"];
filterCriteria = dialog.filenameLookupFilter(filterCriteria);
dialog.run();

if (dialog.run())
    {
    filename = dialogFileName.value();
    if(!filename)
    info("Filename must be filled");

    permission = new fileIOpermission(filename,#io_read);
    permission.assert();
    textIO = new TextIO(filename,#io_read);
    if (!textIO)
    throw error("Error reading file");

    tb.fromFile(filename); //File name with Path …
    numLines = tb.numLines();
    csvFile = new AsciiIO(filename, "R");
    csvFile.inFieldDelimiter(';');//("¢"); // Delimiter…
    simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, "Importing data",1000);

    try
        {
        if (csvFile) //Checking for csv file.
            {
            readCon = csvFile.read();//reading the file.
            while (csvFile.status() == IO_Status::Ok)
                {
                strcnt++;

        CompanyInfo_DataArea         =           Conpeek(readCon,1);
        LedgerChartOfAccounts_Name   =           conPeek(readCon,2);
        MainAccount_MainAccountId    =           conPeek(readCon,3);
        TaxCode                      =          conPeek(readCon,4);
        TaxDirection                 =            conPeek(readCon,5);
        TaxGroup                     =          conPeek(readCon,6);
        TaxItemGroup                 =          conPeek(readCon,7);

                    if(1==1)
                    {
                    ttsBegin;
              while select forupdate qq where
                   QQ.LegalEntity == CompanyInfo::findDataArea(CompanyInfo_DataArea).RecId
                  && QQ.MainAccount == MainAccount::findByMainAccountId(MainAccount_MainAccountId).RecId
                        {
                  QQ.TaxCode = TaxCode;
                        if(TaxDirection == 'Sales')
                        {
                        QQ.TaxDirection = SalesPurch::Sales;

                        }
                        if(TaxDirection == 'Purchase')
                        {
                        QQ.TaxDirection = SalesPurch::Purch;
                        }
                   QQ.TaxGroup = TaxGroup;
                   QQ.TaxItemGroup = TaxItemGroup;
                        QQ.doUpdate();
                        }
                    ttsCommit;

                    }

                readCon = csvFile.read();
              // info("ID:-" +itemid+"-");
                }
            info("Records updated: "+ int2str(strcnt));
            }
        }
    catch
        {
        throw error("ERROR!!!!");
        }
    }
}

Was this reply helpful? Yes No
ThuNgo 978 on at

Like (0)

Report

Hi Ludwig,

I know this post is quite long ago,

But did you manage to export the Account structure without the help from Technical ?

Thank and best regards,

Thu,

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi Thu,

Is it possible that you create a new thread where you describe your issue in more detail?

Also - and very important - please provide us an information about your system version that you operate.

Many thanks and best regards,

Ludwig

Was this reply helpful? Yes No
ThuNgo 978 on at

Like (0)

Report

Hi Ludwig,

Below is the link of the new thread,

Please help me with that:

community.dynamics.com/.../export-account-structure-ax-2012-r2

Thank and best regards,

Thu,

Was this reply helpful? Yes No