Transferring files to AS400

Posted on 2010-08-17
Medium Priority
Last Modified: 2012-06-27
I am trying to transfer an Ascii file from a PC to an AS400 using FTP and need to translate to EBCDIC. Not familiar with the AS400. What mode do I need to use and/or should I be using something else to perform transfer.
Question by:tmark01
LVL 57

Accepted Solution

giltjr earned 2000 total points
ID: 33454063
You should just use text, not binary.  The FTP server on the AS/400 should automatically convert from ASCII to EBCDIC.
LVL 36

Expert Comment

by:Gary Patterson
ID: 33454222
AS/400 to PC data transfer with FTP can be a bit complicated.  Data is stored in EBCDIC, not ASCII.  The FTP server will automatically convert EBCDIC to ASCII, but it does not handle certain numeric format conversions for you (Packed and Integer, for example).  If you must use FTP, and your database file contains any columns that are not CHAR, you'll need to use the CPYTOIMPF command first to export to a CSV (or other import/export friendly format), and then do an ASCII transfer of the stream file that you created using CPYTOIMPF.

Other easy transfer options.

1) Use any of the IBM-supplied data providers (ODBC/JDBC/OLEDB, ADO.NET) that come with Client Access (iSeries Access / System i Access) from your favorite database or data access utility(SQL server, MySQL, Access, AquaData, etc.).  These data providers handle all of the details of converting AS/400 numeric/date formats and EBCDIC-ASCII conversion.

2) Use the Data transfer function from the "Client Access" (iSeries Access, System i Access) program.  Again, this handles all of the numeric conversions, EBCDIC-ASCII conversion, and supports lots of handy target file formats.  Just open a Client Access 5250 Terminal Session, pull down the Action menu, and select "Receive File From Host".  You can save a transfer request once it is set up and run it any time you like.

- Gary Patterson
LVL 27

Expert Comment

ID: 33457765
(Packed and Integer, for example)

Gary's point is important when trying to use FTP.

The FTP TEXT mode is exactly what it says -- a mode that handles "text". FTP isn't a protocol that is intended to be used for database files/tables. Try it with a MS SQL Server table for example. Any number of DBMSs will hold tables that simply are not appropriate for FTP. An AS/400 is no different in that respect.

Instead, FTP would be used to transfer an EXPORT or IMPORT file. Once that is transferred, there will be some form of EXPORT or IMPORT function or command that converts the data into the DBMS format.

On an AS/400, you would IMPORT with the CPYFRMIMPF (Copy From Import File) command and EXPORT with CPYTOIMPF (Copy To Import File).

Normally, an IMPORT or EXPORT file will be text and therefore appropriate for FTP. Because FTP may connect to any remote system that might have any DBMS installed, there is no way that we can expect FTP to know what to do to format transferred rows correctly. Send the IMPORT/EXPORT file with FTP, and use the DBMS facility to do the formatting.

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.


Expert Comment

ID: 33462632
For situations like this, I like to use IFS .. IE using NetServer on the 400 and Windows you can map a drive to an IFS directory share on the 400 and copy the file to the mapped drive. You can then use the CPYFRMSTMF/CPYTOSTMF commands to get it into an AS400 database file(or out of) and write code to parse it from there. This can also handle integer fields, Dates, etc.. You just have to parse the text into the right fields/field types with a pgm on the 400. We also generate CSV stream files all the time and pull them directly into Excel from the 400 using these methods.

If you are locked into FTP, then when you hit the 400 change to "NAME FORMAT 1" to FTP PC files to the IFS directory of your choice. If you are use the ROOT file system you can even handle long names like windows uses. This is not all that hard a process, lots of examples on Google. If you are using DOS FTP, the command is "quote site namefmt 1" to change the format.

I also use Access or other SQL databases and ODBC to link tables on the 400, then you can use windows commands to update a database on the 400 in those programs. You can import a CSV or Text file, parse it, query it, etc..in Excel or Access, then use a macro or VBA program to update the 400 database by updating the PC linked table fields. I usually don't directly manipulate a system database like INFOR Mapics GL or something, but update a work file database and then process that data on the 400 into the structures I need it for. Safer that way.

Lastly you can use the ISeries Access Upload/Download applications to send/receive files on the 400. But you need to have the particular file structures setup on the 400 and you have to deal with field type translations. I usually use this as a last resort, but have tons of customers who do multiple transfers of this type on a daily basis. Once again I recommend working with a work file then using a 400 side application to import/export the data like Tom & Gary suggested.

Anyway IFS structures make it much easier to work with this type of function.

Michael Cody
LVL 33

Expert Comment

ID: 33552077
you lucky guys don't have to deal with importing multiple codepages and RTL text :(

If all else fails, you may have to use the ICONV library to convert progammatically


Expert Comment

ID: 33626680
Hope the below instuctions can help you for transferring PCfiles with extension .TXT & .DAT to AS400.
No data conversion required.
Though there are other transfer methods ,it seems to me that the below one is simple.  
In AS400 :-

Create a Library of your choice using the command:
If already any user defined library is there ,make a note of its name and use while FTPing later .
In your PC:-
Check if the TCP IP connection is enabled and FTP is possible to AS400.
From START>RUN >CMD  ,open the DOS command prompt window.
ex: FTP
Then Login with Username  & password
Change your current library of AS400 into one in which you want the file to be placed ,
hereit would be MYLIB,so type in :
Then Change your current directory of the PC  from which you have the File to be transferred.
Suppose my file is in location C drive ,Backup folder   ,then type :
LCD C:\Backup      
Then Use Put command to place the file on to the Current Library in AS400.
Suppose my file name at  C:\Backup folder is TESTFILE.TXT  ,then type in as :
Information Messages will be displayed with the details of total bytes FTPed.

You can use the same above process to transfer data from AS400 to PC
By simply using GET command and filename(since from AS400 there will be no file extension).

EX: GET TESTFILE,this will receive the TESTFILE from Library MYLIB to C:\Backup folder
To view the file ,open it in Notepad.
Login to AS400 and verify whether required  file has been received

Use Command :
One Physical file named TESTFILE (with out any file extension,ie .TXT)containing the data will will be available on the LIBRARY.

To View the contents in file ;Use command-

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Read this tutorial to learn how to fix repeating password error prompts when setting up Gmail IMAP with Microsoft Outlook. The entire process is described with step by step, illustrated instructions. Enjoy...
With the emergence of Office 365 as a superior email communication platform, many organizations have started switching over to it.  After migrating to Office 365, sometimes users, as well as organizations, will have to import PST files to Office 36…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Watch the video to know the simple way to remove or recover or reset lost or forgotten passwords of Outlook PST file. With Kernel Outlook Password Recovery tool such operation is very easy to perform. It is a freeware with limitation to use with 500…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question