Loading a Tilde-Delimited File into the Database

September 27, 2002 Timothy Prickett Morgan

Hey, Ted:

I received via an e-mail attachment a file that did not contain any sort of CR/LF record separators, although the stream of information did have tilde characters (~) between each logical record.

So I tried to use Copy from Import File (CPYFRMIMPF) command to upload it to a 96-byte single-field “catch” file, specifying the tilde character as the RCDDLM (record delimiter), but OS/400 says this isn’t allowed. Do you have any suggestions?

— Bill

Since you’re using CPYFRMIMPF, I take it the file is in the IFS. Use Qshell’s stream editor, sed, to convert the tilde characters to line-feed characters:

 sed 's/~/
 /g'   inputfile  > mytildedata

The first line has to end with a backslash (). You have to press the Enter key after the backslash to insert a hard carriage return in the replacement string. Sed writes to the standard output file (stdout), which I redirected to IFS file mytildedata.

Then I was able to make the CPYFRMIMPF work:

CPYFRMIMPF FROMSTMF('/home/mydir/mytildedata')
           TOFILE(QTEMP/ZZZ)               
           RCDDLM(*LF)                     
           STRDLM(*NONE)                   
           RMVBLANK(*NONE)                 
           FLDDLM(*TAB)                    
           ERRRCDOPT(*REPLACE)             
           RPLNULLVAL(*FLDDFT)

— Ted

Sponsored By

ADVANCED SYSTEMS CONCEPTS

SEQUEL meets all your iSeries and AS/400 data access needs in a single, integrated solution:

Windows, Web or host user interfaces
Convert AS/400 data into PC file formats
E-mail or FTP query results, reports and spool files
Run-time prompted queries and reports for end users
IF-THEN-ELSE logic in queries and reports
Report, form and label formatting second to none
Easily convert date fields, character-to-numeric, numeric-to-character and other data manipulation
SORT or JOIN using a calculated field
Quick summarization of data with Tabling function
Run multiple SEQUEL requests as one with the SEQUEL Scripting function
OLAP Business Intelligence at a fraction of the cost of comparable solutions

Take 6 minutes to view a SEQUEL ViewPoint ScreenCam movie to see how simple Windows-based AS/400 and iSeries data access can be! In just a few short minutes, you can find out ways to make your job easier and improve data access throughout your organization. Download the ViewPoint movie here .

For more information or a FREE trial of SEQUEL, call 847/605-1311 or visit Advanced Systems Concepts.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search