Output to CSV

david.weeks

Hello All

I am outputting data from Navision to a CSV file.
However, there is another product involved later down the chain and they want me to remove "" and all , from numbers. For example, I currently output 1,323.34 They want it 1323.34

I am using the following to write to my csv file etc.

LostOutstream.WRITETEXT(STRSUBSTNO('"%1",', LrecSalesInvoice.Amount));

I have tried using FORMAT but to no success.

Ideas?

How do we remove the , from these numbers?

Thanks

Sorcerer

Whats about a DELSTR?

RobertMo

You mean DELCHR. Like

LostOutstream.WRITETEXT(DELCHR(FORMAT(STRSUBSTNO('"%1",', LrecSalesInvoice.Amount))),'=',',');

Sorcerer

Right, because of the variable position of the dot(s)/comma, the DELCHR function should be used...

Dec1 := 12345678;
Dec2 := 122.34;
Dec3 := 1222;
Message('D1:%1, D2:%2, D3:%3',DELCHR(FORMAT(Dec1),'=',','),
DELCHR(FORMAT(Dec2),'=',','),
DELCHR(FORMAT(Dec3),'=',','));

fb

Wouldn't this work (to remove the enclosing quotes and any commas within the Amount)?

LostOutstream.WRITETEXT(FORMAT(LrecSalesInvoice.Amount,0,2) + ',');

As a general rule, you can achieve most output formatting results via a careful understanding and use of the second and third [optional] parameters to the FORMAT function. Agreed, the documentation in C/SIDE on-line help is a little cryptic (and even contains a few small errors), but a little experimentation will go a long way.

StephenG

Hi David
You can use the FORMAT Command with Standard format 1 or 2.

The Standard Formats
The following tables list the predefined formats for each data type:

Decimal Format Example

<Sign><Integer Thousand><Decimals> 0 -76,543.21

<Sign><Integer><Decimals> 1 -76543.21

<Sign><Integer><Decimals><1000Character,.> 2 -76543.21

<Integer Thousand><Decimals><Sign,1> 3 76,543.21-

<Integer><Decimals><Sign,1> 4 761543.21-

LostOutstream.WRITETEXT(FORMAT(LrecSalesInvoice.Amount,0,1) + ',');

fb

The extract from help just cited contains what I believe is a small error.

The help implies that Decimal Standard format 2 is equivalent to using the string '<Sign><Integer><Decimals><1000Character,.>' as the third argument to the FORMAT function.

I believe, however, that the <1000Character,x> attribute is used to specify the character to be used to separate the thousands groups, and is only useful in a format string that has specified <Integer Thousand>. As I understand it, <Integer Thousand> means: "Break the integer portion up into groups of three, and separate the groups with the <1000Character,x> attribute (if specified), otherwise use the 'Digit Grouping Symbol' specified in the for the Locale selected in the Control Panel." Furthermore, on experimentation, the <1000Character,x> attribute seems to be ignored even if specified for Decimal formats.

For those who wish to try for themselves, here's a hint:

MESSAGE('%1',FORMAT(1234.56,0,'<Sign><Integer Thousand><Decimals><1000Character,.><Comma,,>'));

The correct 'fully specified equivalent' for Decimal Standard Format 2 is '<Sign><Integer><Decimal><Comma,.>' where the <Comma,x> attribute specifies the character to use to separate the Integer and Decimal portions of the result.

The difference between Standard Format 1 and Standard Format 2 is that Standard Format 2 is guaranteed to use a period for the decimal point, whereas Standard Format 1 will use whatever 'Decimal Symbol' is specified by the current user in the Control Panel.