Re: Removing thousands separator from CSV quoted string

Have you considered Scott Klement's CSV_h routines?
You can customize your csv files to read the entire record and
edit each field as desired. I use it edit a lot of incoming CSV files.


CSV_getfld(csvfile: Amount: %size(Amount));
scanResult = %scan('$':Amount);
if ScanResult > 0;
%subst(Amount:scanResult:1) = ' ';
endif;
scanResult = %scan(',':Amount);
if ScanResult > 0;
%subst(Amount:ScanREsult:1) = ' ';
endif;

"CONFIDENTIALITY NOTICE: This e-mail transmission (and/or the attachments
accompanying it) contain confidential information belonging to the sender.
The information is intended only for the use of the intended recipient.
If you are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution or the taking of any action in reliance
on the contents of the information is strictly prohibited. Any
unauthorized interception of this transmission is illegal under the law.
If you have received this transmission in error, please promptly notify
the sender by reply e-mail, and then destroy all copies of the
transmission."






From: Roger Harman <roger.harman@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 09/06/2016 01:52 PM
Subject: Removing thousands separator from CSV quoted string
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I have a CSV file coming from a trading partner that uses formatted dollar
amounts - "$2,123.45". I need to remove the thousands separator comma
before I import the file into a real PF.


I receive a number of other formats from trading partners and have SQL
routines to remove the $ sign, trailer records, etc. from the data stream.
I remove commas in another file but it is tab-delimited.


Looking for suggestions to remove just the thousands separator if it
exists without killing the comma field separators. Preferably SQL as a
lot of this upfront preprocess happens in a generic CL which has the
RUNSQL routines I need.


I've only come up with brute force - search for $1,.. $2,.. etc.


Thanks.



Roger Harman

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