CSV file to AS/400

FTP is "line oriented." It has no concept of 'database record formats.' It transfers strings of characters with essentially no knowledge of 'fields' or 'columns' that make up 'records.' A 'record' to FTP is usually just whatever string of characters that is found between record delimiters.

Trying to FTP into a DB2 externally-described database file requires having strings of characters that are (1) laid out to match the receiving record format and (2) either are already converted to the proper CCSID and transferred as BINARY, or are ASCII characters that would result in conversions to all the right characters and transferred as ASCII.

Regardless of which alternative is chosen for "(2)", the handling of record format positions that would match up with packed-decimal or other non-character type fields would be very tricky.

That's where a somewhat 'standard' file format such as .CSV comes in.

However, you don't transfer the .CSV file directly over the top of the DB2 file. Instead, you transfer the .CSV file into a simple directory on your AS/400 and then use it as the source for a DB2 import function. On your AS/400, the import function is most often the CPYFRMIMPF command.

The import takes the .CSV file and handles all of the mappings for field positions and data types.

As such, the sequence becomes (1) FTP the .CSV file to the AS/400, and (2) run CPYFRMIMPF on the AS/400.

Note that the format of the .CSV file makes a difference. You need to ensure proper CCSID settings, proper quoting where necessary, proper record separators, etc. Make sure that you generate a .CSV that can be converted to the record format of the eventual target database file. Lots of possible details, but _far_ easier than trying to construct a matching DB2 record format on a different platform.

If you have an ODBC driver for your database on the AS400 and MS SQL server?s tools installed, you can use SSIS from SQL server to parse and import the CSV file into a table in your AS400. IF you do not have access to SQL server, contact me I have tools to do this with out SQL Server, but you will need an ODBC driver, and it would help to know what you are putting the data in on an AS400, but you could be running many different platforms and storing the data in many formats.

Good Luck