How to export decimal fields without 1000s seperator and with decimal point??

Business Central/NAV Developers Forum
1

Hi All

I m using Excel buffer table for exporting data into an excel file. This excel file is then used to import the data into some other system (other than NAV), which uses this excel file and converts it into some file which has continuous data per line with specific length for each field. The problem i am facing is

The other system requires the decimal fields to in the format for example: 587398.00

i.e without comma seperator for 1000s and with the decimal point even if the value after decimal is zero. Also, if the amount field is 0, it shud come as 0.00 or it shuld come as blank in the excel cell.

Please help!!

2

When i use Format function to export though a dataport, in the text file i am able to send the output as desired. But using Excel Buffer table and then exporting to excel, it doesnt take it. For example, a figure of say 50780.00 is exported as 50780.00 in the txt file (using dataport) and as 50,780 in the excel cell (using excel buffer). i use the following line of code to format

AmountText := FORMAT(Amount,0,’<Decimals,3><Comma,.>’);

Please suggest.

3

Look at report 82 Export Budget to Excel, Look at the Function InsertCell and you will see code ExcelBuf.NumberFormat := NumberFormat, no need to use your format string.

The numberFormat has no Navision help but the value is used to tell Excel how to Format the Cell, just copy this function and use it in your export, and for your decimal values call it with the NumberFormat ‘0.00’, it is as easy as the example below.

EnterCell(RowNo,ColNo,DecimalAmount,FALSE,FALSE,‘0.00’);

If you are not using a function then create a Decimal Local or Global and add a bit of code to test for Numeric

IF EVALUATE(MyDecimal, ExcelBuf.“Cell Value as Text”)THEN

ExcelBuf.NumberFormat := ‘0’00’

ELSE

ExcelBuf.NumberFormat = ‘’;

David

4

Thanks a lot David!! It works now.