Item import from CSV file made easy (Kinda)

The job below uses the axInventTable class to create items from a CSV file.  We gave the client an Excel file with columns identified for them to fill in  for new items they wanted then saved that without the header to a CSV file.  This job reads in a CSV file then uses the axInventTable class to create the new Item.  The axInventTable class manages all related tables for us.  If you look at the code below, you can see through this class we can easily set values in the related tables as well as the main inventTable record.  In the below job you can see we used a mix of values read from the file, and values hard coded, you can easily add fields to your import file to incorporate into the creation of your item as you need them.

One thing to be aware of is if Item templates exist AX will try to find the default Item template to use when it does the initialization of the tables.  If there is not a default template it will use the first template found. This is easy enough to work around, just create a new item with minimal fields set and make a template from that item and set it as default using the SysRecordTemplateTable form.

Note: As a side note Atlas 4.0 appears makes use of a similar process for item imports and has the same issue with the Item Template described above.

//-JobStart -->

static void GL_ItemImport(Args _args)

{

    axInventTable axInventTable;

    inventTable inventTable;

    itemID itemID;

    textBuffer tb = new textBuffer();

    int cnt;

    int numLines;

    int c;

    container inLine;

    Dimension finDim;

    itemType ItemType;

    ;

    tb.fromFile('C:\\items.txt');  //File name  with Path ...

    numLines = tb.numLines();

    if(numLines)

    {

        for(cnt = 0; cnt < numLines; ++cnt)

        {

            inLine = str2Con(tb.nextToken(true));

            if(conpeek(inLine, 1))  //first field item ID don’t do anything if blank ....

            {

                axInventTable =  new axInventTable();

                axInventTable.parmItemId(conPeek(inLine, 1));

                axInventTable.parmItemName(conPeek(inLine, 2));

                axInventTable.parmNameAlias(conPeek(inLine, 3));

                axInventTable.parmItemGroupId(conPeek(inLine, 4));

                axInventtable.parmItemType(str2enum(ItemType, conPeek(inLine, 5)));

                axInventTable.parmModelGroupId(conPeek(inLine, 6));

                axInventTable.parmDimGroupId(conPeek(inLine, 7));

                axInventTable.axInventTableModule_Sales().parmTaxItemGroupId(conPeek(inLine, 13));

                axInventTable.parmPrimaryVendorId(conPeek(inLine, 14));

                axInventTable.axInventTableModule_Purch().parmPrice(ConPeek(inLine, 15));

                //Set financial dimension

                finDim[2] = conPeek(inLine, 18);

                axInventTable.parmDimension(finDim);

                axInventTable.axInventTableModule_Purch().parmUnitId(conPeek(inLine, 20));

                axInventTable.axInventTableModule_Sales().parmUnitId(conPeek(inLine, 21));

                axInventTable.axInventTableModule_Invent().parmUnitId(conPeek(inLine, 22));

                axInventTable.parmPackagingGroupId('TMP');

                axInventTable.parmBOMUnitId(conPeek(inLine, 22));

                axInventTable.axInventTableModule_Invent().parmOverDeliveryPct(20);

                axInventTable.axInventTableModule_Invent().parmUnderDeliveryPct(20);

                axInventTable.axInventTableModule_Sales().parmOverDeliveryPct(20);

                axInventTable.axInventTableModule_Sales().parmUnderDeliveryPct(20);

                axInventTable.axInventTableModule_Purch().parmOverDeliveryPct(20);

                axInventTable.axInventTableModule_Purch().parmUnderDeliveryPct(20);

                axInventTable.save();

                ++c;

            }

        }

        info(strFmt('Imported %1 Items', c));

    }

}

//-Job End <--

Ledger Journal Import, General Journal

Importing from a CSV file to create a Ledger journal, we make use of the aX classes here and they simplify our work a lot compared to managing the tables directly.  The data being imported below is minimal; but the code should give you a good example of how to work with additional fields.

//Job Start -->

static void GL_GJImport(Args _args)

{

    textBuffer tb = new textBuffer();

    int cnt;

    int numLines;

    int c;

    container inLine;

    boolean first = true;

    Dimension finDim;

    amount amount;

    LedgerJournalACType LedgerJournalACType;

    ledgerJournalName ledgerJournalName;

    journalID JournalID;

    axLedgerJournalTable axLedgerJournalTable;

    axledgerJournalTrans axLedgerJournalTrans;

    LedgerJournalType JournalType = ledgerJournalType::Daily;

    ;

    tb.fromFile('C:\\sampleGLImport.csv');  //File name  with Path ...

    numLines = tb.numLines();

    if(numLines)

    {

        ttsBegin;

        for(cnt = 0; cnt < numLines; ++cnt)

        {

            inLine = str2Con(tb.nextToken(true));

            if(conpeek(inLine, 1) && !first)  //first field don’t do anything if blank

            {

                if(!JournalID) //Create Journal Haader

                {

                    select firstonly ledgerJournalName

                        where ledgerJournalName.JournalType == JournalType;

                    axLedgerJournalTable = new axLedgerJournalTable();

                    axLedgerJournalTable.parmJournalName(ledgerJournalName.JournalName);

                    axLedgerJournalTable.parmJournalType(JournalType);

                    axLedgerJournalTable.parmName("Journal Description");

                    axLedgerJournalTable.save();

                    JournalID = axLedgerJournalTable.parmJournalNum();

                }

                //Create Trans ...

                axLedgerJournalTrans = new axLedgerJournalTrans();

                axLedgerJournalTrans.parmJournalNum(JournalID);

                axLedgerJournalTrans.parmAccountType(  str2Enum(LedgerJournalACType, conPeek(inLine, 1)));

                axLedgerJournalTrans.parmAccountNum(conpeek(inLine, 2));

                axLedgerJournalTrans.parmOffsetAccountType(  str2Enum(LedgerJournalACType, conpeek(inLine, 3)));

                axLedgerJournalTrans.parmOffsetAccount(conpeek(inLine, 4));

                finDim[1] = conPeek(inLine, 6);

                finDim[2] = conPeek(inLine, 7);

                axLedgerJournalTrans.parmDimension(finDim);

                amount = conpeek(inLine, 5);

                axLedgerJournalTrans.parmAmountCurCredit((amount > 0 ? abs(amount) : 0));

                axLedgerJournalTrans.parmAmountCurDebit((amount < 0 ? abs(amount) : 0));

                axLedgerJournalTrans.save();

                ++c;

            }

            first = false;

        }

        ttsCommit;

        info(strFmt('Imported %1 Items', c));

    }

}

// Job End <--

Acct Type	Acct	Offset Type	OffSet Acct	Amount	Department	CostCenter
Bank	USA Oper	Ledger	300160	500		1201
Ledger	300160	Bank	USA Oper	-250		1102