Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Slick way of handling numeric fields in a flat file?
has no ratings.
Published: 02 Dec 2011
Revised: 23 Jan 2013 - 4109 days ago
Last viewed on: 24 Apr 2024 (6735 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Slick way of handling numeric fields in a flat file? Published by: Chris Proctor on 02 Dec 2011 view comments(9)

Happy Friday, everyone! I have a couple of new flat files coming in with numeric data, comma delimited, that I need to parse out and write to an externally defined file.

I've been coding the usual %scan() for the next comma, determine which field it has to be placed into, and if it's numeric, figure out how long it is, move it with %subst() to a char field with the same length then convert it to decimal with %dec().

I thought this seems pretty clunky and I was wondering if anyone has come up with a slick way of handling this type of conversion. The numeric field can be anywhere from 1 to 15 in length. I tried the following, but the compiler doesn't like it. Too bad!

 sohunt = %dec(%subst(bulkdata:s:length):length:0); 

Any suggestions would be greatly appreciated! Have a good weekend, y'all!

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: clbirk
Premium member *
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 12 years 4 months 22 days 4 hours 53 minutes ago

there is an rpgxtool that bob cozzi sells that has a function that you supply it a field and it will strip out such and return you a good numeric field.

 

It will take like $1,234.56 and give you back a 123456 in a numeric field. I can't remember the function name but it works really well.

Posted by: clbirk
Premium member *
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 12 years 4 months 22 days 4 hours 50 minutes ago

chartonum is the function and as I recall it returns a 15/9 field but you can always "move" (z-add, etc.) into the sort of field you are after.

 

 

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 12 years 4 months 21 days 15 hours 48 minutes ago

You can strip the commas, currency symbols, etc using an %xlate, something like:

NewChar = %xlate(',':' ':OldChar)

Just add the extra symbols where I put the comma, and an extra space per symbol.  Leave the decimal point in there.  Then you convert the character field to numeric, note that the characters that were xlate'd to spaces get suppressed out.  Wrap it all in a procedure and you can then:

eval NumVale = myProc(CharVal)

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 12 years 4 months 21 days 11 hours ago

Thanks to everyone for the info. I was wondering, will a CPYFRMIMPF of a csv file to an externally defined file work if numeric fields are defined in the file? They are quantities, so there is no decimal place. The file will also contain dates in MM/DD/YYYY format that I'm hopin will map fine to a DATFMT(*USA) field in the externally defined file. 

It would be so nice if I don't have to write a program to parse out the fields. I'm not lazy, just trying to streamline the process as much as possible.

Thanks everyone!

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 12 years 4 months 21 days 10 hours 48 minutes ago

Chris, CPYFRMIMPF will copy the file just as you want it. It looks at the target field definitions and converts the CSV data to that format. If the data can't fit into the format, you get an exception. Numerics (packed or otherwise) work just fine.

FYI, tomorrow's RPG Report will have something on parsing CSV.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 12 years 4 months 21 days 10 hours 41 minutes ago

Excellent, Bob. I'll check out the report tomorrow. I thought for sure that it wouldn't work because I have a value anywhere from 0 to 2000 in the csv field, and it's defined as 15.0 in the external file and the error is coming back saying that "the data for the field is not valid for the data type in the tofile". Huuummmm.......

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 12 years 4 months 21 days 10 hours 15 minutes ago

if it is really 0 to 2000 and it is whole numbers (not 37.5 or blank) then it will work fine using CPYFRMIMPF. Is CPYFRMIMPF blowing up or your code using %DEC? Because %DEC and %INT do not like 'blanks as zero' values.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 12 years 4 months 21 days 10 hours 7 minutes ago

Hi Bob. I'm just calling the CPYFRMIMPF from a command line, trying to get the csv file converted and into the externally defined file. It's not associated with any RPG program, so I'm not using %DEC. Yes, they're all whole numbers, no decimals.

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 12 years 4 months 21 days 7 hours 38 minutes ago

do you have a constraint on the fields? I mean if they're DDS, I wonder if a COMP or RANGE keyword is specified for the field restricting the range of data in the field. CPYFRMIMPF should not be blowing up... unless you are on an early release (pre-v5r3).