Import Data dari file excel .CSV ke table AX2012

Di AX2012 ini memang bikin kita senewen, terutama untuk kita yang sudah terbiasa dengan fasilitas import Data Excel ke table AX, karena fasilitas ini dihilangkan oleh si mihkocok.

Belum lagi kita dibikin mumet dengan Addin kita yang gaaaaa solve solve errornya.

Ok.
Kali ini saya mau berbagi script untuk meimport data dari file excel terutama yang berformat .CSV.

1.Asumsikan kita mempunyai file excel dengan data yang sudah dimasukna ke kolom2nya.
Oh iya, script ini saya buat untuk mengimport data ke table custom kita bukan table standard AX.(ane masih mumet cuuuy dengan struktur table AX2012 ini :( )
2.Asumsikan kamu mempunyai table custom yang sudah kamu buat.

lalu buat lah job seperti ini.

Di job ini sebelumnya saya sudah membuat table bernama HYNImportTable.
Dan saya juga sudah maping kolom mana saja yang akan saya ambil nilainya dan selanjutnya saya tembak ke field2 yang sesuai dengan field yang ada ditable saya itu.

static void HYNimporttable(Args _args)
{
    #File
    CommaTextIo        commaTextIo;
    FileIOPermission   permission;
    container          containFromRead;
    int                x;
    int                cols;

    PurchId             purchid;
    AccountingDate      accountingdate;
    VendAccount         vendaccount;
    Line                line;
    TaxGroup            taxgroup;
    TaxItemGroup        taxitemgroup;
    PurchPoolId         poolid;
    LogisticsDeliveryName   Dlvaname;
    Addressing              dlvaddress;
    DlvDate                 dlvdate;
    CurrencyCode            currencycode;
    ItemId                  itemid;
    EcoResCategoryName      procurementcategory;

    PurchQty                        purchQty;
    PurchUnit                       purchunit;
    PurchPrice                      purchPrice;
    AmountCur                       lineAmount;
    PurchReqId                      PRid;
    PurchQtyOrdered                 deliveryremainder;
    ProjCategoryId                  ProjCategoryId;
    VersioningDocumentState         Approvalstatus;
    PurchReqAttention               Attention;
    ItemFreeTxt                     POtext;
    AmountCur                       progpaymamount;
    Name                            PaymTermId;
    ProjId                          ProjId;

    EcoResItemConfigurationName     configId;
    InventLocationId                warehouseid;
    InventSiteId                    siteId;
    EcoResItemSizeName              inventSizeid;
    EcoResItemColorName             inventColorid;
    EcoResItemStyleName             inventStyleid;



    HYNImportTable         hynimporttable;
    ;

    permission = new FileIOPermission('d:\\Upload data PTG\\PO Open Part2 4.csv',#io_read);//D:\Upload data PTG
    permission.assert();

    commaTextIo = new CommaTextIO('d:\\Upload data PTG\\PO Open Part2 4.csv','R');

    containFromRead = commaTextIo.read();
     ttsBegin;
    while(containFromRead)
    {

        cols = conLen(containFromRead);
        for(x=1;x<=cols;x++)
        {
            //accounting date
            if(x == 1)
            {
                accountingdate = str2Date(conPeek(containFromRead,x),-1);
            }
            //PO number
            if(x == 2)
            {
                purchid = strLTrim(conpeek(containFromRead,x));
            }
            //vendaccount
            if(x == 3)
            {
                vendaccount = strLTrim(conPeek(containFromRead,x));
            }

            //line
            if(x==4)
            {
                line = conPeek(containFromRead,x);
            }
            //taxgroup
            if(x==5)
            {
                taxgroup = conPeek(containFromRead,x);
            }
            //taxitemgroup
            if(x==6)
            {
                taxitemgroup = conPeek(containFromRead,x);
            }
            //pool
            if(x==8)
            {
                poolid = conPeek(containFromRead,x);
            }
            //Dlvname
            if(x==9)
            {
                Dlvaname = conPeek(containFromRead,x);
            }
            //dlvaddress
            if(x==10)
            {
                dlvaddress = conPeek(containFromRead,x);
            }
            //dlvdate
            if(x==12)
            {
                dlvdate = str2Date(conPeek(containFromRead,x),-1);
            }
            //currencycode
            if(x==13)
            {
                currencycode = conPeek(containFromRead,x);
            }
            //Payemtermid
            if(x==14)
            {
                PaymTermId = conPeek(containFromRead,x);
            }
            //ItemId
            if(x==15)
            {
                itemid = conPeek(containFromRead,x);
            }
            //Procurementcategory
            if(x==16)
            {
                procurementcategory = conPeek(containFromRead,x);
            }
            //configId
            if(x==17)
            {
                configId = conPeek(containFromRead,x);
            }
            //inventSizeId
            if(x==18)
            {
                inventSizeid = conPeek(containFromRead,x);
            }
            //inventCOlorId
            if(x==19)
            {
                inventColorid = conPeek(containFromRead,x);
            }
            //inventStyleId
            if(x==20)
            {
                inventStyleid = conPeek(containFromRead,x);
            }
            //inventSiteId
            if(x==21)
            {
                siteId = conPeek(containFromRead,x);
            }
            //inventLocationId
            if(x==22)
            {
                warehouseid = conPeek(containFromRead,x);
            }
            //Qty
            if(x==23)
            {
                purchQty = conPeek(containFromRead,x);
            }
            //PurchUnit
            if(x==24)
            {
                purchunit = conPeek(containFromRead,x);
            }
            //PurchPrice
            if(x==25)
            {
                purchPrice = conPeek(containFromRead,x);
            }
            //lineamount
            if(x==26)
            {
                lineAmount = conPeek(containFromRead,x);
            }
            //PR
            if(x==27)
            {
               PRid = strLTrim(conPeek(containFromRead,x));
            }
            //deliveryremainder
            if(x==28)
            {
                deliveryremainder = conPeek(containFromRead,x);
            }
            //ProjId
            if(x==30)
            {
                ProjId  = conPeek(containFromRead,x);
            }
            //ProjCategoryId
            if(x==31)
            {
                ProjCategoryId = conPeek(containFromRead,x);
            }
            //Approvalstatus
            if(x==38)
            {
                Approvalstatus = str2enum(Approvalstatus,conPeek(containFromRead,x));
            }
            //Attention
            if(x==39)
            {
                Attention = conPeek(containFromRead,x);
            }
            //POtext
            if(x==40)
            {
                POtext = conPeek(containFromRead,x);
            }
            if(x==42)
            {
                progpaymamount = conPeek(containFromRead,x);
            }



        }
            hynimporttable.AccountingDate = accountingdate;
            hynimporttable.PurchId = purchid;
            hynimporttable.VendAccount = vendaccount;
            hynimporttable.Line = line;
            hynimporttable.TaxGroup = taxgroup;
            hynimporttable.TaxItemGroup = taxitemgroup;
            hynimporttable.PurchPoolId = poolid;
            hynimporttable.DlvName = Dlvaname;
            hynimporttable.DlvAddress = dlvaddress;
            hynimporttable.DlvDate = dlvdate;
            hynimporttable.CurrencyCode = currencycode;
            hynimporttable.PaymTermId = PaymTermId;
            hynimporttable.ItemId = itemid;
            hynimporttable.ProcurementCategory = procurementcategory;
            hynimporttable.ConfigId = configId;
            hynimporttable.InventSiteId = siteId;
            hynimporttable.InventLocationId = warehouseid;
            hynimporttable.InventSizeId = inventSizeid;
            hynimporttable.InventColorId = inventColorid;
            hynimporttable.InventStyleId = inventStyleid;
            hynimporttable.PurchQty = purchQty;
            hynimporttable.PurchUnit = purchunit;
            hynimporttable.PurchPrice = purchPrice;
            hynimporttable.LineAmount = lineAmount;
            hynimporttable.PurchReqId = PRid;
            hynimporttable.DeliverRemainder = deliveryremainder;
            hynimporttable.ProjId = ProjId;
            hynimporttable.ProjCategoryId = ProjCategoryId;
            hynimporttable.ApprovalStatus = Approvalstatus;
            hynimporttable.Attention = Attention;
            hynimporttable.POText = POtext;
            hynimporttable.ProgPaymAmount = progpaymamount;
            hynimporttable.insert();

        containFromRead = commaTextIo.read();

    }
    ttsCommit;
    info("done");
   

}

Ok selamat mencoba