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 - 2185 days ago
Last viewed on: 16 Jan 2019 (5268 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)

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: 7 years 1 months 13 days 7 hours 20 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: 7 years 1 months 13 days 7 hours 17 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: 7 years 1 months 12 days 18 hours 14 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: 7 years 1 months 12 days 13 hours 27 minutes 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
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 7 years 1 months 12 days 13 hours 15 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: 7 years 1 months 12 days 13 hours 8 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
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 7 years 1 months 12 days 12 hours 42 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: 7 years 1 months 12 days 12 hours 34 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
Comment on: Slick way of handling numeric fields in a flat file?
Posted: 7 years 1 months 12 days 10 hours 4 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).