Hello Roger,
Not sure if you're open to commercial options, but our AutoMate (Like CL Programming for Windows) software could easily rip through that CSV file and strip out the improper values and re-write the data and place the results back in the IFS, a Windows folder or database.
Feel free to email me a sample CSV file and your desired criteria and I'd be happy to put together a little sample automation task for you.
Or you can download a trial from:
http://www.helpsystems.com/automate
Regards,
Richard Schoen
Director of Document Management
e. richard.schoen@xxxxxxxxxxxxxxx
p. 952.486.6802
w. helpsystems.com
------------------------------
message: 5
date: Tue, 6 Sep 2016 18:50:18 +0000
from: Roger Harman <roger.harman@xxxxxxxxxxx>
subject: Removing thousands separator from CSV quoted string
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
midrange.com
