Converting string to real import data from excel to ax 2009 table

(0) Share

Report

Posted on by Vignesh R

535

Hi Experts,

I want to clear about converting Excel data to Ax table. While importing data from excel real value is converting properly to ax table.

For example:

In excel :3023.71

In Ax Table: 3024

In this, I used "any2real " to convert decimal value.

Thanks in Advance and Regards,

Vignesh

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (3)

Piyush Adhikari 2,546 on at

Like (0)

Report

u mean it is not converting text excel column value (3023.71) to AX Table (3024) properly when you are using excel import utility .

Was this reply helpful? Yes No
Vignesh R 535 on at

Like (0)

Report

Hi Piyush,

Yes I want exact value from excel. if it is 3023.71 in excel , i want same value 3023.71 in ax table but it is converting to 3024.

Regards,

Vignesh

Was this reply helpful? Yes No
Verified answer

Piyush Adhikari 2,546 on at

Like (0)

Report

Your datatype of AX table field should be "Real".In case if you are using EDT, then "NoOfDecimals" property should be set to AUTO in EDT.

Apart from this, you can also try

str2num('3023.71');

It will be good if you can share the code.

Was this reply helpful? Yes No
Vignesh R 535 on at

Like (0)

Report

Hi Piyush,

Please check this code and let me know. What I was did wrongly?

Static void ImportFromExcel

{

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

COMVariantType type;

System.DateTime ShlefDate;

FilenameOpen filename;

dialogField dialogFilename;

Dialog dialog;

//Table Declarations Starts

ERB_InvoiceStagingTestTable erb_InvoiceStagingTestTable;

//Table Declarations Ends

int row = 1;

str Customer,SINO, NumberSequenceGroup, Currency, InvoiceNo, ItemSalestaxgroup, GSTNumber,SASCode, Sales1, IGST, SGST, CGST, InvAmt, Sales;

date InvDate, WorkMonth;

#Excel

// convert into str from excel cell value

str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)

{

switch (_cv.variantType())

{

case (COMVariantType::VT_BSTR):

return _cv.bStr();

case (COMVariantType::VT_R4):

return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_R8):

return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_DECIMAL):

return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_DATE):

return date2str(_cv.date(),123,2,1,2,1,4);

case (COMVariantType::VT_EMPTY):

return "";

default:

throw error(strfmt("@SYS26908", _cv.variantType()));

}

return "";

}

;

dialog = new Dialog("Excel Upoad");

dialogFilename = dialog.addField(typeId(FilenameOpen));

dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);

dialog.filenameLookupTitle("Upload from Excel");

dialog.caption("Excel Upload");

dialogFilename.value(filename);

if(!dialog.run())

return;

filename = dialogFilename.value();

application = SysExcelApplication::construct();

workbooks = application.workbooks();

try

{

workbooks.open(filename);

}

catch (Exception::Error)

{

throw error("File cannot be opened.");

}

workbook = workbooks.item(1);

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(1);

cells = worksheet.cells();

try

{

ttsbegin;

do

{

row++;

SINO = COMVariant2Str(cells.item(row, 1).value());

Customer = COMVariant2Str(cells.item(row,2).value());

NumberSequenceGroup = cells.item(row, 3).value().bStr();

InvDate = cells.item(row, 4).value().date();

Currency = cells.item(row, 5).value().bStr();

InvoiceNo = cells.item(row, 6).value().bStr();

WorkMonth = cells.item(row, 7).value().date();

Sales = COMVariant2Str(cells.item(row, 8).value());

IGST = COMVariant2Str(cells.item(row, 9).value());

SGST = COMVariant2Str(cells.item(row, 10).value());

CGST = COMVariant2Str(cells.item(row, 11).value());

InvAmt = COMVariant2Str(cells.item(row, 12).value());

ItemSalestaxgroup = cells.item(row, 13).value().bStr();

SASCode = COMVariant2Str(cells.item(row, 14).value());

GSTNumber = cells.item(row, 15).value().bStr();

if(row > 1)

{

//Insert into InventSize Table

select firstOnly erb_InvoiceStagingTestTable where erb_InvoiceStagingTestTable.InvoiceNo == InvoiceNo;

if(erb_InvoiceStagingTestTable)

{

erb_InvoiceStagingTestTable.InvoiceNo = InvoiceNo;

erb_InvoiceStagingTable.SINO = SINO;

erb_InvoiceStagingTable.Customer = Customer;

erb_InvoiceStagingTable.NumberSequenceGroup = NumberSequenceGroup;

erb_InvoiceStagingTable.InvoiceDate = InvDate;

erb_InvoiceStagingTable.Currency = Currency;

erb_InvoiceStagingTable.InvoiceNo = InvoiceNo;

erb_InvoiceStagingTable.WorkMonth = WorkMonth;

erb_InvoiceStagingTable.Sales = any2real(Sales);

erb_InvoiceStagingTable.IGST = any2real(IGST);

erb_InvoiceStagingTable.SGST = any2real(SGST);

erb_InvoiceStagingTable.CGST = any2real(CGST);

erb_InvoiceStagingTable.InvoiceAmount = any2real(InvAmt);

erb_InvoiceStagingTable.ItemSalesTaxGroup = ItemSalestaxgroup;

erb_InvoiceStagingTable.SASCode = SASCode;

erb_InvoiceStagingTable.GSTNumber = GSTNumber;

erb_InvoiceStagingTable.insert();

info(strfmt(“Item(%1) uploaded successfully”,InvoiceNo));

}

type = cells.item(row+1, 1).value().variantType();

}while (type != COMVariantType::VT_EMPTY);

application.quit();

ttscommit;

}

catch

{

Error("Upload Failed");

}

}

Best Regards,

Vignesh

Was this reply helpful? Yes No
Verified answer

Piyush Adhikari 2,546 on at

Like (0)

Report

here is the gap

you have mentioned _decimals = 0

str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)

{

}

you can change _decimals = 2 .

Was this reply helpful? Yes No
Vignesh R 535 on at

Like (0)

Report

Hi Piyush,

It's working properly . Thanks for your help.

Best Regards,

Vignesh

Was this reply helpful? Yes No
Vignesh R 535 on at

Like (0)

Report

Hi Piyush,

I have one more doubt . If I set decimal = 2 SINo and Customer also come with decimal value .

For Eg:

SINo Customer it's coming like this SINo Customer

1 100 1.00 100.00

Help me to complete this issue.

Thanks in advance and Regards,

Vignesh

Was this reply helpful? Yes No
Khurshid Wali 928 on at

Like (0)

Report

This post from

https://community.dynamics.com/ax/f/33/t/44272

is the best method to import cell value from excel file.

Basically, It checks its Variant Type and formats it accordingly.

I recommend you should use this method.

{{{{

When you’re importing data from Excel, you need to check what the variant type for Excel cell is, and do the appropriate conversion. This way you can avoid problems with different cell formatting, e.g. if some cells are strings (BSTR), while other were formatted as some of the number types. You can use the code below, it checks the variant type (using the variantType() method for a given cell), and then depending on variant type do the appropriate conversion to get the account number as string value:

switch(RCell.value().variantType())

{

case COMVariantType::VT_BSTR:

accNo = strFmt("%1", RCell.value().bStr());

break;

case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:

accNo = strFmt("%1", any2int(RCell.value().double()));

break;

case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:

accNo = strFmt("%1", RCell.value().int());

break;

case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:

accNo = strFmt("%1", RCell.value().uLong());

break;

case COMVariantType::VT_EMPTY:

accNo = '';

break;

default:

throw error(strfmt('Unhandled variant type (%1).', RCell.value().variantType()));

}

}}}}

Was this reply helpful? Yes No
Vignesh R 535 on at

Like (0)

Report

Hi Khurshid,
It's not working for me. Could you help me to fix this issue regarding to above code? It's throwing error for mine. The table does not contain field -->In this line-->(switch(RCell.value().variantType()).

Best Regards,

Vignesh

Was this reply helpful? Yes No
Suggested answer

Khurshid Wali 928 on at

Like (0)

Report

Hi Vignesh,

You have to first connect Excel document using some code like this

//create an instance

ExcelApp= SysExcelApplication::construct();

//Open the file.

ExcelApp.workbooks().open('D:\SalesPrice.xlsx');

cells=ExcelApp.activeSheet().cells();

Then You can use

cells.item(1,1).value()

to access value.. The code I shared previously is just to convert data according to cell value in excel.

The code in this post should be before this switch call.

try to run it in a job and learn by adding bit by bit.

regards,

Khurshid

Was this reply helpful? Yes No