AS400 - Have a File - Need Steps to Download it to CSV format Using FTP.

You can't really do something like that directly with FTP.

Try using the CpyToImpF command. That should create your CSV file on the IFS, and you can then send it to your PC.

Let me know if you need help with CpyToImpF. It's easy once you get the hang of it, but it can be somewhat daunting at first.

HTH,
DaveSlash

Alternately, another method I use for "one-time" data transfers is to import the data into MS Access through ODBC. Then, you can easily export it to Excel from Access.

HTH,
DaveSlash

Hi Dave,

Yes I need help on the CpyToImpF command... let me know what parameters you need.  I gave you the exact lib and file names.

and or ODBC. - I use to do it with the ODBC, but since I don't know if I have the driver any more it may not work.

So I prefer a try on the 1st solution.  I read about it but not sure what the parameters mean.

The following command should copy the contents of your table to a CSV file on the IFS in a folder called YourFolder.

CPYTOIMPF FROMFILE(IKA/QSALD_SERF)    
          TOSTMF('/YourFolder/QSALD_SERF.txt')
          MBROPT(*REPLACE)            
          STMFCODPAG(*PCASCII)        
          RCDDLM(*CRLF)

Select allOpen in new window

You should be able to modify it to meet your needs. The OS manuals give much more in-depth explanation if the command. Check it out.

HTH,
DaveSlash

If you truly don't know if you have the ODBC driver any more, it should be pretty easy to check ... at least, it is in Windows. Just go to Control Panel / Data Sources (ODBC) and look for an existing IBM i data-source.

Sorry Dave,

I think my AS400 is too old, it doesn't recognize the command.

Can you help me with the ODBC part.  

I set it up long ago .. but I just can't remember.  We actually have been off the as/400 since 2005.

Any steps would be helpful...

Thanks!

The CPYTOIMPF command has been around for a lot of years, well before 2005. If it isn't recognized as a command, you'll need to tell us the OS version. The possible methods might be different for very old releases.

The certain methods involve writing programs to build the output. Less certain methods would take some experimenting. Even ODBC might be difficult due to possible networking and other mismatches with more recent Windows versions.

Tom

Thoughts:

1) Would have to be very old to not support CPYTOIMPF.   Any chance it is just an authority issue?  Perhaps that command is locked down?  Do you have access to a profile with *ALLOBJ special authority,such as QSECOFR?

2) Haven't used Rumba in a long time, but last time I did, it had a file transfer function that would do the trick.  What version of Rumba are you using?

3) Consider moving the file to another system with a newer version of the OS.  At least one hosting provider I know offers free AS/400 accounts on a V5R3 system.  Details below.

4) Depending on the data types of the columns in the file, you may be able to use FTP to pull down a fixed-format version of the file in ASCII and use Excel's Text Import tool to convert to XLS or CSV.  Need to see the layout of the file, though.  Use the command:

DSPFFD IKA/QSALD_SERF

And cut and paste the results of this command (probably multiple pages) into a text file and post that here.


===============================================

Can you provide us some version information on Rumba (Help menu in Rumba) and the AS/400 OS version (GO LICPGM, option 10, F11 to view version info).

Sounds like this system must be on a very old release if CPYTOIMPF isn't there.  If this is a "one shot" project, you might just want to upload the file to a system with a newer version of the OS and use CPYTOIMPF from there.

AS/40 hosting provider http://www.rzkh.de/ provides free accounts on a V5R3 system that would do the trick.

Basic process:

1) On your system, create a save file using the CRTSAVF command.  This is a container, like a PC ZIP file that you can use to transfer to another system.

2) Use the SAVOBJ command to save the file you want to convert to the save file you just created.

3) Use FTP to do a BINARY transfer of the save file.  If your firewall allows outbound FTP, you might be able to just use the AS/400 FTP client and PUT directly from your AS/400 to the hosting provider's system.  Otherwise you can download the file to your PC using FTP, and then PUT it up to the hosting provider's machine.

4) Restore the file from the save file using the RSTOBJ command on the hosting provider machine.

5) CPYTOIMPF, and download the exported CSV file using FTP.

This is what I have done so far.

I am running the file on the As/400, not sure version.  (We moved off of it around 2006.)

The file has 3 columns.  I will use the FTP command to export it to my PC.  Ok no problem.

I've downloaded the file in Binary and it's a mess.  

I tried the FTP download again without adding "BIN" .. which I thought would be Ascii?  Still looks like a mess.

Can I do this?

If it has anything but CHAR fields, it'll look like a mess if you try to FTP it.

I suppose you could change all colums to CHAR using SQL's "alter table" command.

(I still think it's be easier to install the ODBC driver and import it into Accesss.)

-- DaveSlash

Actually, upon further reflection, I don't think you can alter a numeric column to CHAR. The types are not compatible.

Many AS/400 DB files are stored with CHARACTER-type columns encoded in EBCDIC.  Numeric column encoding is also a problem with FTP.

Your Windows PC doesn't like EBCDIC.  Try an ASCII download - the FTP server will automatically convert EBCDIC to ASCII.  IF you just have character data, it may be usable - but it won't be delimited.  

If you have numeric data, those columns may need some work.

- Gary Patterson

Gary,

Actually 3 Columns NOW
DSPFFD command used
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Input parameters                                                              
   File  . . . . . . . . . . . . . . . . . . . :  QSALD_SERF                  
     Library . . . . . . . . . . . . . . . . . :  IKA                          
 File Information                                                              
   File  . . . . . . . . . . . . . . . . . . . :  QSALD_SERF                  
     Library . . . . . . . . . . . . . . . . . :  IKA                          
   File location . . . . . . . . . . . . . . . :  *LCL                        
   Externally described  . . . . . . . . . . . :  Yes                          
   Number of record formats  . . . . . . . . . :      1                        
   Type of file  . . . . . . . . . . . . . . . :  Physical                    
   File creation date  . . . . . . . . . . . . :  02/12/13                    
   Text 'description'. . . . . . . . . . . . . :  Output file for IKA/QSALD_SE
 Record Format Information                                                    
   Record format . . . . . . . . . . . . . . . :  IQOUTPUT                    
   Format level identifier . . . . . . . . . . :  2AC5AB0667019                
   Number of fields  . . . . . . . . . . . . . :      3                        
   Record length . . . . . . . . . . . . . . . :     40                        

 Field Level Information                                                      
              Data        Field  Buffer    Buffer        Field    Column      
   Field      Type       Length  Length  Position        Usage    Heading      
   SDLOTN     CHAR           30      30         1        Both                  
                                                                  Serial #    
                                                                  --------    
     Field text  . . . . . . . . . . . . . . . :  Serial Number. . . . . . . .
     Coded Character Set Identifier  . . . . . :     37                        
   SDIVD      ZONED        6  0       6        31        Both     Invoice      
                                                                    Date      
                                                                  -------      
     Field text  . . . . . . . . . . . . . . . :  Invoice Date . . . . . . . .
     Editing Information                                                      
       Edit code . . . . . . . . . . . . . . . :  J                            
                                                                                                                                                           STDDAT     PACKED       6  0       4        37        Both     Date        
                                                                 MM/DD/YY    
    Field text  . . . . . . . . . . . . . . . :  Standard Format: MM/DD/YY  
    Editing Information                                                      
      Edit code . . . . . . . . . . . . . . . :  Y

Thanks for all your feedback.  Geeezeeeee it was a frustrating 2 days.  I couldn't give up with your feedback. I wanted tooo!!!!

I tried to create the ODBC connection - Couldn't. Frustration.

So I try what you gentlemen said, I basically made the other or last field a char field and downloaded it, extracted all three fields into a the single long string in excel and it worked like a champ.

Wow, thank you guys... you guys are animals with these great suggestions!!    

I will try Client Access test in a week or so because I have to do this again with more fields.  Do you guys know if I need anything special to install and make this client access version to work?  I've used it 10 years ago but don't remember if I needed any special licenses.

Thank you !!!