Tuesday, 7 January 2014

Code / job to import CSV / Excel to CustTable in Dynamic AX 2012


1.Prepare the CSV (If it was excel convert ti CSV)  file containing following fields in same order as below
Customer account
English Name
English Search Name
Invoice account
Customer group
Terms of payment
Currency
One-time customer
Country/region
Delivery terms
Mode of delivery
Misc. charges group
Language

2. Create a class using below codes and just run by using |> run button or F5

public class CustomerMasterImport extends RunBase
{
    CommaIo                     csvFile;
    Filename                    filename;
    DialogField                 dialogFilename;
    #define.CurrentVersion(2)
    #localmacro.CurrentList
    filename,
    insertIncorrectRecords,
    #endmacro
    #localmacro.ListVersion1
    filename,
    insertIncorrectRecords
    #endmacro
    #File
}
/*********************************************/
public Object dialog()
{
    DialogRunbase       dialog = super();
    ;
    dialogFilename  = dialog.addField(extendedTypeStr(FilenameOpen));
    dialogFilename.value(filename);
    dialog.filenameLookupFilter(["All files", #AllFiles]);
    return dialog;
}
/********************************************/
public boolean getFromDialog()
{
    filename                = dialogFilename.value();
    return true;
}
/*******************************************/
void run()
{
    //CommaTextIO                             csvFile;
    container                               readCon;
    counter                                 icount,inserted;
    Dialog                                  dialog;
    DialogField                             dfFileName;
    DirPartyRecId                           partyRecId,contactPartyRecid;
    Name                                    name,contactName;
    CustTable                               vendtable;
   // VendTable                               vendtable;
    str                                     contactperson;
    DirPartyPostalAddressView               addressView;
    DirPartyContactInfoView                 contactView;
    ContactPerson                           contactpersonTable;
    LogisticsElectronicAddressMethodType    enumType;
    DirContactPersonsService                dirContactPersonsService;
    DirContactPersons                       dirContactPersons;
    DirContactPersons_ContactPerson         dirContactPersons_ContactPerson;
    DirContactPersons_Person                dirContactPersons_Person;
    DirContactPersons_PersonName            dirContactPersons_PersonName;
    AifEntityKeyList                        aifEntityKeyList, aifEntityKeyList_DirContactPerson;
    str                                     fName, mName, lName;
    VendAccount                             vendorAccount;
    DirParty                                dirParty;
    LogisticsPostalAddress                  address;
    LogisticsElectronicAddress              logisticsElectronicAddress;
    BinData                                 binData;
    str                                     stringImage;
    LogisticsAddressStateID                 stateId;
    str                                     accountnum,accountName,vendgroup,currency,dlvmode,paymtermid,countryid,street,city,mobile,fax,email,zipcode,pobox,phone;
    //
    CustInvoiceAccount  invoiceaccount;
    LogisticsAddressCountryRegionId  countryregion;

    NoYes               ontimecustomer;
    CustDlvTermId       termid;
    CustMarkupGroupId   chargegroup;
    ItemBuyerGroupId    buyergroupid;
    ;

    csvFile = new CommaIO(filename, 'r');
    try
    {
        if (csvFile)
        {
            readCon = csvFile.read();
            while (csvFile.status() == IO_Status::OK)
            {
                readCon = csvFile.read();
                if(readCon)
                {
                icount++;
                accountnum = conPeek(readCon,1);
                accountName = conPeek(readCon,2);
                name = conPeek(readCon,3);
                invoiceaccount = conPeek(readCon,4);
                vendgroup = conPeek(readCon,5);
                paymtermid = conPeek(readCon,6);
                currency = conPeek(readCon,7);
                ontimecustomer = conPeek(readCon,8);
                countryregion= conPeek(readCon,9);
                termid=conPeek(readCon,10);
                dlvmode = conPeek(readCon,11);
                chargegroup = conPeek(readCon,12);
                if(!name)
                break;
                partyRecId = DirPartyTable::createNew( DirPartyType::Organization, name).RecId;
                vendtable.clear();
                vendtable.initValue();
                vendtable.Party = partyRecId;
                vendtable.AccountNum = accountnum;
                vendtable.CustGroup  = vendgroup;
                vendtable.Currency   = currency;
                vendtable.DlvMode    = dlvmode;
                vendtable.PaymTermId   = paymtermid;
                vendtable.OneTimeCustomer   =  ontimecustomer;
                vendtable.DlvTerm =termid;
                    vendtable.DlvMode = dlvmode;
                   vendtable.MarkupGroup = chargegroup;
                    vendtable.InvoiceAccount = invoiceaccount;
                    vendtable.PartyCountry = countryregion;
                if(contactperson != '')
                {
                    contactname = ContactPerson;
                    ContactPerson::findOrCreateNameParty(partyRecId,contactname);
                }
               vendtable.insert();
                stateId = subStr(stateId,1,25);
                address.PostBox = strLRTrim(PoBox);
                address.CountryRegionId = strLRTrim(Countryid);
                address.State = stateId;
                address.ZipCode = strLRTrim(ZipCode);
                address.Street  = strLRTrim(Street);
                address.county = countryregion;
                address.City    = strLRTrim(City);
                addressView.LocationName = name;
                addressView.IsPrimary = NoYes::Yes;
                addressView.Party = partyRecId;
                addressview.initFromPostalAddress(address);

                DirParty = DirParty::constructFromPartyRecId(addressView.Party );
                DirParty.createOrUpdatePostalAddress(addressView);
         inserted++;
                }
            }
        }
        icount--;//Remove header recount from total record count
    }
    catch(Exception::Error)
    {
        info(strFmt("%1 %2",Exception::Error,icount));
    }
}
/***********************************************/
static void main(Args  args)
{
    CustomerMasterImport        VendorMasterImport;
    ;
    VendorMasterImport =  new CustomerMasterImport();
    if(VendorMasterImport.prompt())
    {
        VendorMasterImport.run();
    }
}
/*********************************************/
3. Now you will find a browser like this.

4. Select your CSV file and proceed.

Regards,

No comments:

Post a Comment

SQL/SSRS Interview questions I thought of blogging some SQL/SSRS interview questions.

Below are some. I will add more, when I complete the compilation 1. What is OLTP(Online Transaction Processing)? OLTP stands ...