Comma as Decimal Separator in File Layout?

We have an interface that sends in Voucher information as CSV files. We load data from these files into the Voucher Staging tables using a File Layout and an App Engine. Recently a new Business Unit tested this interface and reported that it was broke!

We investigated their interface file and found that they were using comma as a decimal separator in the CSV file. Our File Layout which had used a Signed Number field for the amount field was not able to capture a value that contained a comma. As comma was commonly used as a decimal separator in France, we had no choice but to modify our File Layout.

This is an outline of the modifications that we did.

1. Change the Field Type

At the File Layout level, we need to change the File Layout field type from Signed Number to Character so that the value that contained the comma can be captured. An example of the value could be 1236,50 which would mean 1236.50

File Layout Field Properties: Amout as Number

File Layout Field Properties: Amout as Number

File Layout Field Properties: Amout as Char

File Layout Field Properties: Amout as Char

2. Replace the Comma with a Decimal Point

In the App Engine PeopleCode, while reading the value, check if it has a comma. If it has one, just replace it with a decimal point.

/* Code to accommodate comma as decial separator 
 &GK_AMT is the variable in which the Amount 
values is captured from the File Layout*/
If Find(",", &GK_AMT) <> 0 Then
   &GK_AMT = Replace(&GK_AMT, Find(",", &GK_AMT), 1, ".");
   &GK_AMT = Value(&GK_AMT);
Tags: , ,
Subscribe to Comments RSS Feed in this post
3 Responses
  1. Hi,

    Thanks for the solution but will this not cause any performance issue if my report contains more than 10,000 rows of data etc?

    • Hi Rao,

      This works fine for our interface files with couple of hundred rows. Have not tested for 10k+ rows.

      BTW, why do you think this could cause a performance issue?

  2. Hi Rakesh,

    Thanks for the solution.

Leave a Reply

Your email address will not be published. Required fields are marked *