Inserting End-of-Line Characters and Format Data in Data Transfer Downloads

Resolving The Problem

There are cases where it is desirable to have a line break between two fields or to be able to add line breaks between records in a download file type that does not normally have line breaks. To do this, the data transfer data options can be modified to select the individual fields and insert the literal values for the carriage return and line feed characters as required.

As an example, we will download the QIWS/QCUSTCDT file to a text file; however, we want it in a format that we can use to create address labels. We want to get the output in the following format:

INIT LSTNAM
STREET
CITY, STATE ZIPCODE

Step 1: Create a new data transfer from the IBM System i system. Type the System i library and file name, and the PC file name and extension so that the transfer request looks similar to Figure 1.



Step 2: Press the Data Options button to open the Change Data Options window, and press the Details button in the window that opens. This opens the Receive Request Details window that is opened to the Select tab. The Select tab has a list of the fields in the file and allows you to select the fields by double-clicking on the fields. Double-click on the fields in the following order: INIT, LSTNAM, STREET, CITY, STATE, and ZIPCOD. Your Receive Request Details window Select clause should now look similar to Figure 2 with all of the selected fields in a comma separated list.



Step 3: Add some SQL functions and literal values to this select clause to get it in a format we want. The first line should be INIT LSTNAM; that is INIT, space, LSTNAM. Ideally, we want to have any trailing blanks trimmed from the INIT data. To accomplish this, change the select clause from:

INIT,LSTNAM,STREET,CITY,STATE,ZIPCOD

to

TRIM(INIT)||' '||LSTNAM,X'0D25',STREET,CITY,STATE,ZIPCOD

The first line of the record in the PC file now has the initials, right trimmed to remove trailing blanks, followed by a space, followed by the last name. The X'0D25' inserted as a literal value between LSTNAM and STREET inserts a carriage return and line feed character after the last name (LSTNAME). Note that the X'0D25' is the EBCDIC hex value for a carriage return and line feed, the standard text end-of-line characters.

The second line is the street address. Therefore, insert another line break between STREET and CITY. For the last line, trim the CITY column, and append spaces between the CITY and the STATE and between the STATE and the ZIPCOD. Finally, add two extra line breaks in addition to the one that an ASCII Text file will put at the end of a record. We added the three blank lines so that the addresses print out correctly on our sheet of address labels. After all the manipulations to the select clause, the clause now looks like this:

TRIM(INIT)||' '||LSTNAM,X'0D25',STREET,X'0D25',TRIM(CITY)||' '||STATE||' '||ZIPCOD,X'0D25',X'0D25'

Finally, we decided that we would like the list to print in order, alphabetically by last name. We went back to the Data Options and typed in the Order by field the value, LSTNAM. We could have done this by clicking Details, selecting the Order by tab, and double-clicking the LSTNAM field. We run the data transfer download and the output file looks similar to the following:

M T Abraham
392 Mill St
Isle MN 56342

J S Alison
787 Lake Dr
Isle MN 56342

J W Doe
59 Archer Rd
Sutter CA 95685

J J Javahead
123 Main
Bogus WI 12345

J A Johnson
3 Alpine Way
Helen GA 30545