Re: Removing thousands separator from CSV quoted string

A super simple FSM is what I used. If you're inside a quote and come across a comma or dollar sign, it goes away.


As I mentioned... 15 minutes.


Roger Harman

COMMON Certified Application Developer - ILE RPG on IBM i on Power







________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of Alan Campin <alan0307d@xxxxxxxxx>
Sent: Wednesday, September 7, 2016 11:12 AM
To: Midrange Systems Technical Discussion
Subject: Re: Removing thousands separator from CSV quoted string

I never read a text file with anything except an RPG program reading the
data directly using unix api's(My open source API's). There is just too
many things that get screwed up when you try to copy data to a flat file.

Reading it directly is always the way to go.

As far as parsing, in the past I have used a state engine to parse this
kind of thing but don't know if it is complex enough to warrant the time to
create a state diagram.

Could you send a copy of what the data looks like? Maybe just one line.

On Wed, Sep 7, 2016 at 11:04 AM, Paul Nelson <nelsonp@xxxxxxxxxxxxx> wrote:

Copy to a flat file, and then read an internally defined file, and output
to
fields in an externally defined file.

I've used different versions of the same program for over 20 years. The
only
tweaking is for different banks. The output file hasn't changed much over
the years.

Paul Nelson
Cell 708-670-6978
Office 409-267-4027
nelsonp@xxxxxxxxxxxxx


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Berendt
Sent: Wednesday, September 07, 2016 12:59 PM
To: Midrange Systems Technical Discussion
Subject: RE: Removing thousands separator from CSV quoted string

You read stream files from RPGII? Impressive.
Or were you just saying that if you treat the file as internally defined
(which really isn't a language thing) then that helps for this level of
checking?


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Paul Nelson" <nelsonp@xxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 09/07/2016 01:56 PM
Subject: RE: Removing thousands separator from CSV quoted string
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



RPGII still works like a champ for this kind of thing.

Paul Nelson
Cell 708-670-6978
Office 409-267-4027
nelsonp@xxxxxxxxxxxxx


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Berendt
Sent: Wednesday, September 07, 2016 12:29 PM
To: Midrange Systems Technical Discussion
Subject: Re: Removing thousands separator from CSV quoted string

I too find that interchanging data from CSV (or other stream files) are
fraught with errors and really harkens back to that first COBOL program I
wrote in college. Basically never assume any character was valid.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Luis Rodriguez <luisro58@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 09/07/2016 01:11 PM
Subject: Re: Removing thousands separator from CSV quoted string
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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.

--

Luis Rodriguez
--
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.


--
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.


--
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.


--
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.


--
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.

--
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.