Re: Manipulating a CSV File on the IFS

I get reason code 4 - The file or directory cannot be found.

I'm using a valid IFS reference, being '/home/EUISPMSM/test.csv'

Although I have used SQL on CLOB/BLOB data types within a table, I didn't know the same technique could be used to manipulate IFS files directly.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: 01 February 2011 16:00
To: Midrange Systems Technical Discussion
Subject: Re: Manipulating a CSV File on the IFS

What's the reason code? What SQL statement is giving the error?

On Tue, Feb 1, 2011 at 10:48 AM, McGovern, Sean
<Sean.McGovern@xxxxxxxxxxxx> wrote:

Birgitta,

I receive SQL0452 when running the following code on our V5R4 and V6R1 box (I've changed the IFS reference).

I'd like to this working. Could you re-visit ?

Thanks,
Sean


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: 01 February 2011 07:35
To: 'Midrange Systems Technical Discussion'
Subject: AW: Manipulating a CSV File on the IFS

Hi,

you may try to define the IFS file as CLOB_File (or DBCLOB_File or BLOB_File
depending on the data type) and access it via embedded SQL.
LOB_File data can be accessed with SQL commands or SQL scalar functions like
any character (host) variable.
Something like this:

D MyFromCLOB      S                   SQLTYPE(CLOB_File)
D MyToCLOB        S                   SQLTYPE(CLOB_File)

D ParToIFSFile    S            256A   Varying
D                                     inz('/home/Hauser/CLOBFILE.txt')
D ParFromIFSFile  S            256A   Varying
D                                     inz('/home/Hauser/CLOBFILE.txt')
D StrPos          S             10I 0
D Length          S             10I 0
 /Free
   //From CLOB File Information
   MyFromCLOB_Name = '/home/Hauser/CLOBFILE.txt';

   MyFromCLOB_NL   = %Len(MyFromCLOB_Name);

   MyFromCLOB_FO   = SQFRD;                   //Read Only



   //To CLOB File Information

   MyToCLOB_Name = '/home/Hauser/CLOBFile.txt';

   MyToCLOB_NL   = %Len(MyToCLOB_Name);

   MyToCLOB_FO   = SQFOVR;                    //Override existing file


   //Search for credit card information for example:
   Exec SQL Set :StrPos = Locate('Creditcard': :MyFromCLOB);

   //Determine the length of creditcard information (or whatever you want
to do)
   Exec SQL Set :Length = ....

   //Manipulate the data (for example)

   Exec SQL Set :MyToCLOB = Substr(:MyFromCLOB, 1, :StrPos-1)
                            Concat
                            Substr(:MyFromCLOB, :StrPos + :Length);

   If SQLCOD < *Zeros;

      Dsply SQLCOD;

   EndIf;

   *INLR = *On;
 /End-Free

SQLFRD (=2) and SQLFOVR (=32) are constant values that will be defined
integrated by the SQL Precompiler.

Not tested, because I've currently no access to an iSeries.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gary Thompson
Gesendet: Monday, 31. January 2011 22:11
An: Midrange Systems Technical Discussion
Betreff: Re: Manipulating a CSV File on the IFS

Scott Klement has an e-book and RGPLE code he has made available that
should get you started.

We used this approach to create several applications using .csv and .txt
files in the IFS
to exchange data with Windows servers, other iSeries on our network, and a
third party
that shipps .csv files to us.

The examples I have do not read/write IFS files directly, (we use
CPYFRMIMPF and CPYTOIMPF)
       but Scott, and others, provide examples to get you started.





"Harvell, Joel" <jbharvell@xxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/31/2011 01:39 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

Subject
Manipulating a CSV File on the IFS






I have a file on the AS400 IFS that has Credit Card information in the
file.  I could download the file to a file on the AS400 clear the
offending fields and put the file back on the ifs, but I would like a
more efficient way of clearing the CC information on the files.  Is
there an API or a way of using SQL to accomplish this??



Joel B. Harvell

Sr. Programmer/Analyst

Delhaize America

(704) 633-8250 x2709


**************************************************************************
This electronic message may contain confidential or privileged information
and is intended for the individual or entity named above.  If you are
not the intended recipient, be aware that any disclosure, copying,
distribution or use of the contents of this information is prohibited.
If you have received this electronic transmission in error, please notify
the sender immediately by using the e-mail address or by telephone
(704-633-8250).
**************************************************************************
--
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.


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


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

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

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

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