Is it possible to take alpha-numeric formatted fields and convert them to a numeric value

[Cathy Wilbur]

Is it possible to take alpha-numeric formatted fields and convert them to a numeric value

We have to write a new interface and all the numeric dollar value fields coming in are formatted with dollar signs, commas, and decimal places as alpha-numeric. The amounts come in as comma delimited fields but these fields are not a fixed length. The field is only as long as it needs to be when exported. The data fields are sent in a CSV file. We want to take this CSV file and do an Oracle DB table load. Should I load all these fields as string and do a conversion in OW to convert them back to a numeric values?

We are coding our program using the table conversion tool.

I would have to find business functions that takes my string data and convert them to an unformatted amount field. One of my amount fields is an exchange rate field so I would have to use a business function "string to numeric conversion" that allows me to specify the maximum number of decimal places.

The client, that is passing us this GL interface data, is doing an Export from a product called Raiser's Edge. The export formats all the numeric decimal data complete with ("$" "," and ".").

I asked them to export the data with all the fields with fixed format lengths with no special formating but the client said they do not have the option.

Has anyone had this problem and if so how did you resolve it?

 

[smss]

Re: Is it possible to take alpha-numeric formatted fields and convert them to a numeric value

You can actually use the business function that would trim the characters like $ and "," from the incoming formatted field and then use the resultant string to convert to MathNumeric.

 

[JMR]

Re: Is it possible to take alpha-numeric formatted fields and convert them to a numeric value

Cathy,

A few options come to mind.

1. Just try an assignment in Event Rules. Use the F(x) button and try assigning the input string to a numeric variable. This may not work.

2. While I don't know of one, you should try searching existing bsfn's for this functionality.

3. There is an API called, ParseNumericString, that you can call from a C bsfn that might do the trick. Although, I am betting the option I mentioned in #1 actually uses this api behind the scenes so if #1 doesn't work, then this probably won't either.

4. Write a bsfn (or it can be done in ER) to strip all non-number, non-'.' characters from the string. In other words, check each character of input string and if it is a number or '.', then store that character in another string variable in the Nth position. Where N is the number of 'good' characters found so far from the input string. Then an assignment from a string to a number will work (using option #1).

Good luck and if you find an exising bsfn, please let us know.

 

[Null Value]

Re: Is it possible to take alpha-numeric formatted fields and convert them to a numeric value

In Function "Convert String To Numeric" of B4000770, you'll notice a 'switch' statement containing a couple of 'case' statements.

Add the following statement immediately after the switch statement:-

case _J('$'): break;


So now it will look like:-

switch (lpDS->szGenericString[iCounter])
{
case _J('$'): break;



You can now use this function to pass your string values and retrieve the corresponding numeric value.

Ofcourse, if customizations of vanilla JDE functions is out of question, you can copy the C function to a custom function and make those changes there.

 

[peterbruce]

Re: Is it possible to take alpha-numeric formatted fields and convert them to a numeric value

Cathy,

It seems to me that having commas imbedded in the field in a variable field length csv file would cause all sorts of problems just extracting the field values, let alone converting them to numerical values. It would make things a lot simpler if all fields represent numeric values and have the "$", a decimal point and a consistant number of decimal places. In this case you could use the "$" as you delimitor in the TC and ignore the first <null> value.

A csv row like this could be extracted this way and then parsed to remove the commas (there may be a bsfn to do this or you could write you own):

$123,123.34,$12.00,$12,456.50,$0.00

would come out like:

<null> 123,123.43, 12.00, 12,456.50, 0.00

and parsing:

<null> 123123.43 12.00 12456.50 0.00

As far as I am aware, the TC row format needs a defined number of fields and you would not be able to use a comma as a delimitor. If you do use the comma as the delimitor (and don't use a TC), you would have determine the parts of and rejoin the numbers that are broken. You would have to concatenate every resulting value between a value starting with "$" and either a value which includes the decimal point, or up until but not including the next value starting with a "$", removing the "$". Thus:

$123,123.34,$12.00,$12,456.50,$0.00

would come out like:

$123 123.34 $12.00 $12 456.50 $0.00

concatenated:

123123.34 12.00 12456.50 0.00

Inconsistancies in the number of decimal places and whether or not a decimal point exists or not could be overcome. However if there are fields that do not represent numbers then the problems increase. Especially if these are in between the values that do represent numbers.

That's my AUD 0.02 worth. You have a complex problem to solve. I hope I have helped, if only a little bit.