Solved

Transferring files to AS400

Posted on 2010-08-17
6
1,234 Views
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.
0
Comment
Question by:tmark01
6 Comments
 
LVL 57

Accepted Solution

by:
giltjr earned 500 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.
0
 
LVL 34

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
0
 
LVL 27

Expert Comment

by:tliotta
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.

Tom
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Expert Comment

by:mkc451
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
0
 
LVL 32

Expert Comment

by:shalomc
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

ShalomC
0
 
LVL 1

Expert Comment

by:xaviercsc
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:
CRTLIB LIB(MYLIB) TYPE(*TEST) TEXT('FTP Library)')
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.
Type  FTP <IPADDRESS OF AS400 >
ex: FTP 10.60.245.13
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 :
 CD MYLIB
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 :
PUT TESTFILE.TXT
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 :
WRKOBJ MYLIB/TESTFILE
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-
DSPPFM MYLIB/TESTFILE
----------------------------------------------------------------------------------------------
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
In  today’s increasingly digital world, managed service providers (MSPs) fight for their customers’ attention, looking for ways to make them stay and purchase more services. One way to encourage that behavior is to develop a dependable brand of prod…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now