Need to retrieve .CSV files via FTP into PF

[kcmmk5]

We are changing to a new EDI VAN. I need to be able to pickup .CSV files provided by the VAN via FTP and copy the data into a physical file. When I go to pick up the .CSV file(s), there may be more than one file to be picked up. Once I pickup the files, I have to delete them from the remote directory. Then I need to have the records from the .CSV files copied into a single physical file. All of this has to be done as a batch program. Has anyone done this before? Does anyone have any program examples to get me started? Thanks.

[DeadManWalks]

Re: Need to retrieve .CSV files via FTP into PF

1) are the file names unique or do you just want to crab them all and delete all. Ie MGet MDel in FTP.
2) are you getting the CSV file and loading into an IFS (much better).
3) make a PF that is flat, ie 1 field that is way way long.
4) the CpyFrmIfs should do the trick. Play with the CR LR parms to get what you want.
5) read flat file and load to what ever file you need to.

Hey look, I can help someone.

[tomholden]

Re: Need to retrieve .CSV files via FTP into PF

you mean CPYFRMSTMF perhaps?

[kcmmk5]

Re: Need to retrieve .CSV files via FTP into PF

1) The CSV files will have various names. I would want to grab all and delete all. One concern that I have about MGet and MDel is if something could get missed. Do you know if it is possible for a file to get placed in the directory after the MGet starts and get deleted by the MDel? Or, is there something in the MGet and MDel that prevent that from happening?
2) I can utilize the IFS. I am just a little unclear on how to do that. I assume that I would create a folder under Root such as "ftpin" or "ediin" and have the ftp commands place the files there but, how do I reference that location with the ftp commands? Is there some way of combining the files together into one file during the transfer?
3) For the flat PF, are we talking about a field length long enough to hold all records of the CSV file into a single field? Or, a field long enough to hold a single record? They are able to provide the CSV files in one of 2 different formats (either comma delimited or tab delimited). Which would be better to go with?
4) I am not familiar with CpyFrmIfs. Is that an AS400 command (I don't see it on my system)? Is it a custom program/command?

Ultimately, I am looking to get the contents of the CSV files into a PF with the data space delimited so I can process it using an RPG program.

Thanks in advance for your assistance.

[tomholden]

Re: Need to retrieve .CSV files via FTP into PF

ftp script:

userid password
namefmt 1
lcd /ftpin
mget *.whatever
mdel *.whatever
quit

and i believe he was after the CPYFRMIMPF command

CPYFRMIMPF FROMSTMF('/ftpin/file1.whatever') TOFILE([I]YOURLIB[/I]/[I]YOURFILE[/I]) RCDDLM(*CRLF)
  FLDDLM(',') MBROPT(*REPLACE)

[kcmmk5]

Re: Need to retrieve .CSV files via FTP into PF

When I run the CPYFRMIMPF command I get garbage data. What could I be doing wrong? I tried to upload the CSV file to this thread but, it doesn't let me. Please look at the attached screen shots. The command that I ran was: CPYFRMIMPF FROMSTMF('/ftpin/test862.csv') TOFILE(KRHTEMP/CSVTEST) MBROPT(*REPLACE) RCDDLM(*CRLF) FLDDLM(',')




Thanks.

[tomholden]

Re: Need to retrieve .CSV files via FTP into PF

what CCSID is the file you're copying into? looks like a translation error. if you look at the actual file you retrieved how does it look?

[Rico]

Re: Need to retrieve .CSV files via FTP into PF

If you copy the file from the IFS to a PC and view the data, does it look as expected?

[kcmmk5]

Re: Need to retrieve .CSV files via FTP into PF

It was an issue with the CCSID. It works (sort of) when I change the command to: CPYFRMIMPF FROMSTMF('/ftpin/test862.csv') TOFILE(KRHTEMP/CSVTEST) MBROPT(*REPLACE) TOCCSID(297) RCDDLM(*CRLF) FLDDLM(',')

Now the data is legible but, it only contains the first field in each record. The rest of the fields from the CSV are truncated. I noticed one of the parameters on the CPYFRMIMPF command is "Field definition file". Do I need to create and reference a field definition file in order to capture the additional fields in the CSV file?

[kcmmk5]

Re: Need to retrieve .CSV files via FTP into PF

If you copy the file from the IFS to a PC and view the data, does it look as expected?

Yes, it does.

[kcmmk5]

Re: Need to retrieve .CSV files via FTP into PF

I think I'm getting there. I discovered that if I create the PF (that I am copying to) with the actual fields defined, I do get all of field data from the CSV.

What I have been able to do now successfully is:
1) Grab the files via ftp and clear the ftp remote ftp directory.
2) Copy a single named CSV file from the IFS to a PF.

Any ideas on how to take multiple CSV files (with names unknown) in a directory on the IFS and combine them into one file?

Thanks.

[Mark A Smith]

Re: Need to retrieve .CSV files via FTP into PF

http://www.code400.com/forum/showthread.php/8457-CPYFRMIMPF-multiple-files

[kcmmk5]

Re: Need to retrieve .CSV files via FTP into PF

http://www.code400.com/forum/showthr...multiple-files

Thanks. That thread shows a couple different ways to do it. I tried this one:

QSH CMD('touch -C 1252 /home/Myfolder/myfileResult | find ''/home/Myfolder/'' -name ''myfil*.csv'' -exec sort -n {} >/home/Myfolder/myfileResult \;')

But, I had to modify it a little to make it work. The original command has the result file (myfileResult) located in a subdirectory under the main directory where the source files were located. That causes it to duplicate records. I simply created a new directory outside of the source directory and it works perfect.

QSH CMD('touch -C 1252 /result/myfileResult.csv | find ''/ftpin/'' -name ''*.csv'' -exec sort -n {} >/result/myfileResult.csv \;')

[gcraill]

Re: Need to retrieve .CSV files via FTP into PF

Just to wind back a fraction. How often are the files being created in the "landing" folder?

If they can appear at any time then doing an MGET MDEL may either grab files already processed, or delete files not processed yet.

You could first do an LS command from the FTP session to a local file, then read the file and compare the file names against a PF that lists files processed and status flags etc.

Then you only need to GET files that have not yet been processed, and only delete files that have been processed ok.

[jamief]

Re: Need to retrieve .CSV files via FTP into PF

There is also some code here: look at the top two examples

http://www.code400.com/inside.php?category=OPERATIONS

jamie