Issue in import excel file code

Finance and Operations/AX Developer Forum
1

i debugged this code and found that insert is not working what is the issue

public void run()
    {
        System.IO.Stream                    stream;
        OfficeOpenXml.ExcelWorksheet        worksheet;
        OfficeOpenXml.ExcelRange            range;
        ExcelPackage                        package;
        int                                 i = 2,rowCount;
        boolean                             errorFound;
        str                                 fileName;
        str                                 filePath,fileNameOnly;
        MainAccountNum                      mainAccountNum,offsetMainAccount;
        str                                 accountingLocation,brand,costCenter,CTC,department,employeeCodes,
                                            fixedAsset,importFileNumber,itemCategory,manufacturer,region,
                                            responsibilityCentre,size,topazCustomers,typeDim,variant;//businessUnit, department, costCenter, itemGroup;
        str                                 offaccountingLocation,offbrand,offcostCenter,offCTC,offdepartment,offemployeeCodes,
                                            offfixedAsset,offimportFileNumber,offitemCategory,offmanufacturer,offregion,
                                            offresponsibilityCentre,offsize,offtopazCustomers,offtypeDim,offvariant;
        filetype                            type;
        NumberSequenceTable                 numberSequenceTable;
        NumberSeq                           numberSeq;
        CustAccount                         customerAccount;
        Voucher                             voucher;
        LedgerJournalName                   ledgerJournalName;
        int                                 cnt;
        DimensionValue                      dimensionValue;
        LedgerJournalId                     journalNum;
        LedgerJournalACType                 accountType;
        DimensionDynamicAccount             ledgerDim;
            

        FileUpload fileUploadControl = this.getFormControl(dialog, FileUploadName);
        FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
        if (fileUploadResult != null && fileUploadResult.getUploadStatus())
        {
            textFile = fileUploadResult.getDownloadUrl();
            fileName = fileUploadResult.getFileName();
            [filePath, fileNameOnly, type] = fileNameSplit(fileName);
            stream  = fileuploadResult.openResult();
            try
            {
                ttsbegin;
                package = new ExcelPackage(stream);
                package.load(Stream);
                    
                worksheet   = package.get_Workbook().get_Worksheets().get_Item(1);
                range       = worksheet.Cells;
                rowCount    = worksheet.Dimension.End.Row;

                if(!(rowCount == 0))
                {
                    ledgerJournalTable.clear();
                    journalNum = range.get_Item(i,1).Value;
                    ledgerJournalTable = LedgerJournalTable::find(journalNum);
                    if(!ledgerJournalTable)
                    {
                        throw error("journal Num %1 not exist.",journalNum);
                    }

                    //if(ledgerJournalTableLocal.JournalNum == ledgerJournalTable.JournalNum)
                    //{
                    //    throw error("Journal %1 is not valid for this import",ledgerJournalTableLocal.JournalNum);
                    //}
                }

                while(i <= rowCount)
                {
                    //journalNum = range.get_Item(i,1).Value;
                    //ledgerJournalTable = LedgerJournalTable::find(journalNum);
                    //if(ledgerJournalTableLocal.JournalNum == ledgerJournalTable.JournalNum)
                    //{
                    //    //Create line
                        ledgerJournalTrans.clear();
                        ledgerJournalTrans.initValue();
                        ledgerJournalTrans.JournalNum   = ledgerJournalTable.JournalNum;
                        ledgerJournalTrans.TransDate    = range.get_Item(i,3).Value;

                        // voucher creation
                        ledgerJournalName   = LedgerJournalName::find(ledgerJournalTable.JournalName);
                        select firstOnly numberSequenceTable
                            where numberSequenceTable.RecId == ledgerJournalName.NumberSequenceTable;

                        if (numberSequenceTable)
                        {
                            numberseq                    = NumberSeq::newGetVoucherFromCode(numberSequenceTable.NumberSequence);
                            voucher                      = NumberSeq.voucher();
                            ledgerJournalTrans.voucher   = voucher;
                        }
                        ledgerJournalTrans.CurrencyCode     = range.get_Item(i,4).Value;
                        mainAccountNum  	                = range.get_Item(i,6).Value;
                        ledgerJournalTrans.AccountType      = str2Enum(accountType,range.get_Item(i,5).Value);
                        ledgerJournalTrans.Txt              = range.get_Item(i,23).Value;
                        ledgerJournalTrans.AmountCurDebit   = range.get_Item(i,24).Value;
                        ledgerJournalTrans.AmountCurCredit  = range.get_Item(i,25).Value;
                        ledgerJournalTrans.CurrencyCode     = range.get_Item(i,26).Value;
                        //Customer
                        if(LedgerJournalACType::Cust == ledgerJournalTrans.AccountType)
                        {
      
                            ledgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(mainAccountNum,LedgerJournalACType::Cust);
                            ledgerJournalTrans.LedgerDimension = ledgerDim;
                            }
                        //Vendor
                        else if(LedgerJournalACType::Vend == ledgerJournalTrans.AccountType)
                        {
      
                            ledgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(mainAccountNum,LedgerJournalACType::Vend);
                            ledgerJournalTrans.LedgerDimension = ledgerDim;
                        }
                        //Bank
                        else if(LedgerJournalACType::Bank == ledgerJournalTrans.AccountType)
                        {
      
                            ledgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(mainAccountNum,LedgerJournalACType::Bank);
                            ledgerJournalTrans.LedgerDimension = ledgerDim;
                            }
                        //ledger
                        else if(LedgerJournalACType::Ledger == ledgerJournalTrans.AccountType)
                        {
                            accountingLocation	= range.get_Item(i,7).Value;
                            brand				= range.get_Item(i,8).Value;
                            costCenter      	= range.get_Item(i,9).Value;
                            CTC      			= range.get_Item(i,10).Value;
                            department      	= range.get_Item(i,11).Value;
                            employeeCodes		= range.get_Item(i,12).Value;
                            fixedAsset			= range.get_Item(i,13).Value;
                            importFileNumber	= range.get_Item(i,14).Value;
                            itemCategory		= range.get_Item(i,15).Value;
                            manufacturer		= range.get_Item(i,16).Value;
                            region				= range.get_Item(i,17).Value;
                            responsibilityCentre= range.get_Item(i,18).Value;
                            size      			= range.get_Item(i,19).Value;
                            topazCustomers      = range.get_Item(i,20).Value;
                            typeDim      		= range.get_Item(i,21).Value;
                            variant    			= range.get_Item(i,22).Value;
      
                            ledgerJournalTrans.LedgerDimension  = TPZ_JournalCashVoucherImport::generateLedgerDimension
                            (mainAccountNum,['AccountingLocation','Brand','CostCenter','CTC','Department','EmployeeCodes','FixedAsset','ImportFileNumber','ItemCategory','Manufacturer','Region','ResponsibilityCentre','Size','TopazCustomers','Type','Variant'],
                                            [accountingLocation,brand,costCenter,CTC,department,employeeCodes,fixedAsset,importFileNumber,itemCategory,manufacturer,region,responsibilityCentre,size,topazCustomers,typeDim,variant]);                            
                        }
                        ledgerJournalTrans.OffsetCompany        = range.get_Item(i,27).Value;
                        ledgerJournalTrans.OffsetAccountType    = str2Enum(accountType,range.get_Item(i,28).Value);
                        offsetMainAccount                       = range.get_Item(i,29).Value;
                        ledgerJournalTrans.DocumentNum          = range.get_Item(i,46).Value;
                        ledgerJournalTrans.DocumentDate         = range.get_Item(i,47).Value;
                        ledgerJournalTrans.Invoice              = range.get_Item(i,48).Value;
                        ledgerJournalTrans.ExchRate             = range.get_Item(i,49).Value;
                    if(LedgerJournalACType::Cust == ledgerJournalTrans.OffsetAccountType)
                    {
      
                        ledgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(offsetMainAccount,LedgerJournalACType::Cust);
                        ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
                    }
                    //Vendor
                    else if(LedgerJournalACType::Vend == ledgerJournalTrans.OffsetAccountType)
                    {
      
                        ledgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(offsetMainAccount,LedgerJournalACType::Vend);
                        ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
                    }
                    //Bank
                    else if(LedgerJournalACType::Bank == ledgerJournalTrans.OffsetAccountType)
                    {
      
                        ledgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(offsetMainAccount,LedgerJournalACType::Bank);
                        ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
                    }
                       else if(LedgerJournalACType::Ledger == ledgerJournalTrans.OffsetAccountType)
                        {
                            offaccountingLocation	= range.get_Item(i,30).Value;
                            offbrand				= range.get_Item(i,31).Value;
                            offcostCenter      	    = range.get_Item(i,32).Value;
                            offCTC      			= range.get_Item(i,33).Value;
                            offdepartment      	    = range.get_Item(i,34).Value;
                            offemployeeCodes		= range.get_Item(i,35).Value;
                            offfixedAsset			= range.get_Item(i,36).Value;
                            offimportFileNumber	    = range.get_Item(i,37).Value;
                            offitemCategory		    = range.get_Item(i,38).Value;
                            offmanufacturer		    = range.get_Item(i,39).Value;
                            offregion				= range.get_Item(i,40).Value;
                            offresponsibilityCentre = range.get_Item(i,41).Value;
                            offsize      			= range.get_Item(i,42).Value;
                            offtopazCustomers       = range.get_Item(i,43).Value;
                            offtypeDim      		= range.get_Item(i,44).Value;
                            offvariant    			= range.get_Item(i,45).Value;

                            ledgerJournalTrans.OffsetLedgerDimension  = TPZ_JournalCashVoucherImport::generateLedgerDimension
                            (offsetMainAccount,['AccountingLocation','Brand','CostCenter','CTC','Department','EmployeeCodes','FixedAsset','ImportFileNumber','ItemCategory','Manufacturer','Region','ResponsibilityCentre','Size','TopazCustomers','Type','Variant'],
                                                [offaccountingLocation,offbrand,offcostCenter,offCTC,offdepartment,offemployeeCodes,offfixedAsset,offimportFileNumber,
                                                offitemCategory,offmanufacturer,offregion,offresponsibilityCentre,offsize,offtopazCustomers,offtypeDim,offvariant]);
                            
                        }

                        if(ledgerJournalTrans.validateWrite())
                        {
                            ledgerJournalTrans.insert();
                        }
                        i++;
                    //}
                    //else
                    //{
                    //    throw error("Journal %1 is not valid for this import",journalNum);
                    //}
                }
                ttscommit;
            }
            catch
            {
                errorFound = true;
                throw error("Failed to uplode the data.");
            }
        }
    }
2

I’m sorry, but there is no universal solution for “not working”. You’ll need to describe the problem to us.

3

the excel is not getting imported - it gives me error saying failed to upload the data

4

You said you debugged your code, but you either failed to do it or you still keep the details just for yourself.

Your code throws “Failed to uplode the data.” when an exception is thrown inside the try block. You need to find out what failed there; i.e. what exception was thrown and on which line of code. Use the debugger to find it out.

As you see, you can’t fix the problem without any idea what the problem is, therefore getting more information is a necessary step.

5

when I debugged it it showed the exception error on insert line,

if(ledgerJournalTrans.validateWrite())
{
ledgerJournalTrans.insert();
}

6

That’s a good first step, but it still tells you very little, therefore you need to continue. You still don’t know what the error is and where it was thrown; all you know so far that an exception was thrown in some code called from insert().

In the debugger, you can look at the exception details. Properties Message and StackTrace are the most important ones.

In most cases, you’ll also see the error message in infolog (both in debugger and the UI).

7

this was the error message which i found when I debugged the code further

image
image1871×744 311 KB

8

That’s your own error that you throw when the real error occurs.

If you don’t know how to debug the code, it may be easier for you to remove the try/catch block and call the code directly. Then you’ll see the previous error without your code hiding it.