Re: Removing thousands separator from CSV quoted string

Another alternative would be just copying your .CSV to an intermediate PF.
Over that PF create a view that has the data eactly as you want it. Your
$1,123.99 column could have a statement like this:

replace( replace(amount, ',' , ''), '$', '') as new_amount
('' are to single quotes without spaces between them)

Process, copy, etc. your view as any other file.

Me? I read the .CSV directly from the IFS using a RPGLE program (thanks SK
!), read each record into an array (using SCAN) and process each column
according to my PF specs (you have no idea of the kind of data we have
received in what is supposedly a well defined .CSV :-( ).


Regards,

Luis




On Wed, Sep 7, 2016 at 9:26 AM Justin Dearing <zippy1981@xxxxxxxxx> wrote:

On Tue, Sep 6, 2016 at 5:43 PM Roger Harman <roger.harman@xxxxxxxxxxx>
wrote:

You're pretty much describing it as-is. We FTP the CSV file(s) - there
may be 1 to n. They then get copied to a flat 2048 byte record before

the

preliminary transform. That then gets copied to a real PF.

Let me clarify. I'm suggesting you copy the raw columnal data, not the raw
row data.

So do a CPYFRMIMPF to a physical file that has the same columns as the CSV,
but a VARCHAR for the money column. Then do a scalar UDF to transform that
column.

If you copy each record in the CSV to a one column record in a physical
file, your doing a lot of parsing that CPYFRMIMPF or Scott's tools can do
for you, and have been better battle tested for edge cases. If those tools
can't parse the $ signs, you just have to work on that line.

Or is CPYFRMIMPF failing to parse the columns?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.