Announcement

**tomliotta** · October 20, 2014, 08:09 PM

Re: Need help with AS/400 csv file processing

...the consultant we hire to help with system move (from old system38 to new ISeries) does not like to work with IFS thus no help there.

That sure raised a big red flag for me. My first step would be find a different consultant. This one is described as not knowing the environment, and that's a bad state for a consultant. IMO, most of this process ought to involve processing IFS data objects.

**RussellPK1** · October 21, 2014, 05:06 AM

Re: Need help with AS/400 csv file processing

I agree with Tom - you need a better consultant. The IFS is about as important as any feature of the IBM i operating system.

For FTP, you can use Scott Klement's FTPAPI - it will allow you to read a directory for the file names, then retrieve the ones you want - I've used it for that very purpose, works great. You need to write some RPGLE, but there are examples.

Regarding CPYFRMIMPF, are there null-values coming in? If so and your target file doesn't allow them, use the parameter RPLNULLVAL(*FLDDFT).

**vbp1@hotmail** · October 21, 2014, 05:53 AM

Re: Need help with AS/400 csv file processing

frankly I am not the one to judge as I do not know a thing about the environment myself.
also I am not in the position to make decisions about hiring and whatnot and he came recommended and so far is doing a pretty good job converting old system.

as far as I understand with AS/400 there is more than one way of doing things(like in any other environment) and thus I am sure I can do this without using IFS.
it might not be pretty or very efficient but doable.

so far I am able (manually) to call FTP and get (A) file into system.
i.e. Call FTP (FTP RMTSYS('192.168.1.xx') this is an internal ftp server that I use for my needs and for testing things)
login using user/password
load a file using Get FileName MyLib/HDR1 <-- this is the "*FILE PF-DTA" object that get created and it is a single column flat file that looks like
""
id,account_id,shipping_address_name, ...
3174,57##1-#####,"ABC Inc.", ...

"
where column name is "Order_0000"

I know I can use SQL to delete the header record like so " delete from hdr1 where left(hdr1,2) = 'id' "

but that is all. I am not sure what I need to do after all that and how.
also I am not sure how to do all that in an automated way as in

DO I WRITE all this in CL program?
if so, what is the proper way of doing this?
as there are mavbe multiple files how do I process all of them.
the consultant suggested I use a call to ftp to get a listing of all files available and put it in local file than use that to build the loop.
but I am not 100% sure how to do it or where. do I do this in CL program or RPG program?

DO I need an RPG program as well to process the data into a permanent table?

I have tried to run "CPYFRMIMPF FROMFILE(QWEBORDERS/HDR1) TOFILE(QWEBORDERS/HDRPROC) "
where HDR1 is the imported flat PF and HDRPROC is specifically created database PF with expected table structure. as in all columns defined and such.
but the command fails with a range of errors (I get error 4,3 and 7) but not sure how to get more detail info on them.

it might be a good learning experience but it getting frustrating fast.

**danlong005** · October 21, 2014, 06:41 AM

Re: Need help with AS/400 csv file processing

Ok, #1 send that consultant packing. This is what the IFS is for. For working with other machines.

#2 Try this. This example will pickup file.csv and put it in the Physical File(Table) MYFILE in the library MYLIB.

Your PF or Table will have something like the following source.

Code:

 CREATE TABLE MYLIB/MYFILE (
   MYFIELD CHAR(500)
 ) RCDFMT RMYFILE;

In a CL put the following code(This could be scheduled to run ever 15 minutes).

Code:

OVRDBF     FILE(INPUT) TOFILE(MYLIB/QRPGLESRC) +
             MBR(MYFTPSRC)                    
FTP        RMTSYS(LOOPBACK)                     
DLTOVR     FILE(INPUT)

In the MYFTPSRC file put the following.

Code:

user userid password                 
ascii                              
lcd MYLIB                        
rename file.csv MYFILE.MYFILE 
get MYFILE.MYFILE (replace   
close                              
quit

Now you will have a PF/Table with one field in that has a whole line of CSV data in it. I.E. MYFIELD = field1,field2,field3,"field4".
Then you can write and RPGLE program to process that file and split it up.

Here is an example rpgle to get you started.

Code:

       dcl-s array char(500) dim(50) inz;
      
       exec sql
            declare mycursor cursor for
            select myfield
            from mylib/myfile;

       exec sql
            open mycursor;
       if sqlstate <> '00000';
         *inlr = *on;
         return;
       endif;

       exec sql
            fetch next from mycursor into :field1;
       dow sqlstate = '00000';

         array = split( field1 : ',' );

         // now you have each field in each cell of the array
         // array[1] = "field1" 
         // array[2] = "field2"
     
         exec sql
              fetch next from mycursor into :field1;
       enddo;

       exec sql
            close mycursor;
       if sqlstate <> '00000';
         *inlr = *on;
         return;
       endif;

       *inlr = *on;
       return;
      
      
      **************************************************************************                    
      * split                                                                  *                    
      * ====================================================================== *                    
      * instring - the string to split up                                      *                    
      * delimter - the delimter to split the string up by                      *                    
      *                                                                        *                    
      * This will return an array of the strings that have been split out of   *                    
      * the original string.                                                   *                    
      **************************************************************************                    
     p split           b                   export                                                   
     d split           pi           500a   dim(50)                                                  
     d instring                   32000a   varying const                                            
     d delimiter                     10a   varying const                                            
                                                                                                    
     d retArray        s            500a   dim(50) inz                                              
     d start           s              5  0 inz(0)                                                   
     d stop            s              5  0 inz(0)                                                   
     d index           s              5  0 inz(1)                                                   
     d length          s              5  0 inz(0)                                                   
                                                                                                    
      /free                                                                                         
         clear retArray;                                                                            
         length = %len(%trim(instring));                                                            
                                                                                                    
         dow index < 50;                                                                            
             start = stop + 1;                                                                      
             if start <= length;                                                                    
                 stop = %scan(delimiter:instring:start);                                            
                 if stop = *zeros;                                                                  
                     stop = %len(%trim(instring)) + 1;                                              
                 endif;                                                                             
                 retArray(index) = %subst(instring:start:stop-start);                               
             else;                                                                                  
                 index = 49;                                                                        
             endif;                                                                                 
                                                                                                    
             index = index + 1;                                                                     
         enddo;                                                                                     
                                                                                                    
         return retArray;                                                                           
      /end-free                                                                                     
     p split           e

Hope that helps...

**danlong005** · October 21, 2014, 06:43 AM

Re: Need help with AS/400 csv file processing

by the way "get MYFILE.MYFILE (replace " is right. There is not a closing parenthesis on that.

**chris_hird** · October 21, 2014, 06:49 AM

Re: Need help with AS/400 csv file processing

vbp1

I think you are confused about what the CPYFRMIMPF does? You say you are not using the IFS yet you are using a command that copies from the IFS? So it is never going to give you any results.

FTP I assume you are using a binary copy? So when you FTP the object from your Windows Server to a library you are simply copying ASCII text in binary for an EBCDIC based DB file. That will never provide you with the results you need as IBMi DB files can store any data but it needs to be converted to be used correctly.

Basically you need to set up the IFS to receive the ASCII based .csv file. You can use FTP or map a network drive in the IFS as a Windows drive(Google NETSERVER for IBM i/AS/400 setup) Once you have that set up a simple batch process to run the CPYFRMIPMF will convert the stream files to DB records in your file. You have not supplied any code so I cannot determine how you set up the files (I would use SQL to create the files in the same manner you did on the MSSQL server) and ensure the content of the .csv files is good before doing the copy. Lots of additional work needs to be done to make sure this is done properly and does not affect the security of the IFS so this is only a push in the right direction.

Chris...

**tomholden** · October 21, 2014, 07:06 AM

Re: Need help with AS/400 csv file processing

Originally posted by vbp1@hotmail View Post

frankly I am not the one to judge as I do not know a thing about the environment myself.
also I am not in the position to make decisions about hiring and whatnot and he came recommended and so far is doing a pretty good job converting old system.

FWIW we had a consultant work for us (for about 2 weeks before he got the axe) that came highly recommended. after some code review and some issues he caused it took a couple of weeks to recover from some of his mistakes and they were so obvious a 1st semester college student would have caught them. he "appeared" to be doing a good job as well. so just be aware that recommendations do not a good developer make...buyer beware

**vbp1@hotmail** · October 21, 2014, 07:35 AM

Re: Need help with AS/400 csv file processing

thanks danlong005 , that should get me going for a wile :-)

#1 is not under my jurisdiction :-) and even so, we all have our preferences.
I am OK with not getting the real file (as in real .CSV) onto the system but just ftp / get the file directly into lib/object.
it works, and it does exactly the same thing as your first 2 examples directly

I can just " get file.csv hdr1 (replace " in the MYFTPSRC and it will work just fine
I do not even need the "CREATE TABLE MYLIB/MYFILE" it will create it for me

my issue is actually transfer that data into proper database table.
and processing more than one file as needed.

you see, it will be run every 15 min.
it will go to FTP and check for any CSV files there.
if there are any file(s) exist (that folder will/should only have proper order files) process them into the system and move the files on remote server into processed folder so they will not be imported again.

I do this as a learning exercise but want it to be a productive one too :-)

it will give me a way to learn how the system works in real time real system. I am going through the ATS learning books/tapes and exercises but they are too simple for me to grasp the gist of how things work under the hood. and since I am coming from windows only environment it is too different to grasp at the moment.

**vbp1@hotmail** · October 21, 2014, 08:37 AM

Re: Need help with AS/400 csv file processing

hi everyone, I am not sure what is going on but non of my replies are coming through.

**tomliotta** · October 21, 2014, 07:16 PM

Re: Need help with AS/400 csv file processing

Note that CPYFRMIMPF is fully capable of using either IFS streamfiles or native physical files as input. It would be very rare that I would use a native physical file, but it usually would only be a problem if the record length was too short.

Also, it's not clear if FTP BINARY would be reasonable in this case; but it could certainly be an issue if it had to be used for some reason. This case seems much more likely to be defaulted to FTP ASCII. If any binary data is involved, that would make things worse; but it doesn't seem likely here.

**vbp1@hotmail** · October 22, 2014, 09:12 AM

Re: Need help with AS/400 csv file processing

ok, after a quick email to the admin, I can see all my posts here and was told that my issue with posting should be fixed (YAHOOOOO).

no for some quick update on the issue at hand.

#1. after extensive research on IFS and FTP options on AS/400(iSeries) I now have script (a txt object to use as input file for FTP command) to login to ftp and get all the files AS-IS into /home/myuser folder on IFS. it works and works very very well.

I also figure out why my initial use of "CPYFRMIMPF" did not work. turns out I need to use "RCDDLM(*EOR)" option when source object is a data file in the system (PF-DAT) and I was consistently overriding it with "*CRLF" as I though since the original file was CSV that is the default record deliminator.
I am sure that now if I will be using the file from IFS I would need to use that instead of EOF.

now all I need is to figure out how to:

DONE >> 1. get a list of all the files in local folder from CL or RPG program
I just added the LS (ls order*.csv (disk ) and rename (syscmd rnmobj lsoutput *file ftplst ) commands to the main script
so now my get files CLP dows both gets a list of files from ftp and mgets them into IFS /home/myuser folder
I am sure I can read the ftplst file as it is a native AS/400 object

DONE >> 2. using the above list go through each file name and extract and validate the name to identify the file type (as in, is it a header file or details file)
DONE 3. based on that call up the CPYFRMIMPF with proper file name to import it into database

Created an RPG program that calls the ImpiortFromFTP CL and than reads the List member (the ftp CL program creates the FTPLIST PF from the LSOUTPUT file)
validates the file name (checks for existance or Header or Items string with in the name) and based on that calls
appropriate CL program passing the file name into it. the CL calls CPYFRMSTMF

4. build the rename script for ftp to move all downloaded files on the ftp server into processed subfolder (I think the danlong005 code will work for that)
5. call the rename script when all files are processed into db
6. delete all local files from IFS
7. call RPG program to edit data in the db as needed. I need to set the status of the orders, process date for the records etc.

when I figure all that I am golden :-)

Announcement

Need help with AS/400 csv file processing

Need help with AS/400 csv file processing

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment