Importing fixed length records with BTEQ in Windows environment

Tools & Utilities
Enthusiast

Importing fixed length records with BTEQ in Windows environment

I'm trying to import a flat file that is in a fixed width format on a Windows machine using BTEQ but cannot find any examples that work. Here's a sample script:

.IMPORT FILE='C:\test_file.txt';

.QUIET ON

.REPEAT *

USING (col1 VARCHAR(10), col2 VARCHAR(5), col3 VARCHAR(8), col4 VARCHAR(37))

INSERT INTO MYDB.TMP_PGN_TEST (CMF10, MKT_ID, PGN, FILL_TXT)

VALUES (:col1, :col2, :col3, :col4);

.QUIET OFF

.QUIT ERRORCODE ;

I've tried specifying different types of imports (DATA, REPORT and VARTEXT) and leaving it out as in the sample above. I've trimmed the file down to a single row for testing and counted the bytes to ensure the field sizes are correct. The script above gives this error:

 *** Warning: No IMPORT mode was given, assuming field mode.

 *** Growing buffer to 65473

 *** Failure 2673 The source parcel length does not match data that was defi

 ned.

                Statement# 1, Info =1

 *** Warning: Out of data.

If I specify DATA as the import type (as the two examples of fixed length imports do that I've been able to find) I get the following results:

 *** Growing Buffer to 12337

 *** Error: Import data size does not agree with byte length.

            The cause may be:

                1) IMPORT DATA vs. IMPORT REPORT

                2) incorrect incoming data

                3) import file has reached end-of-file.

 *** Warning: Out of data.

I've tried adding a field to the USING statement to account for CR/LF pairs in the file in case it was being read as a stream, but no luck.

Is it even possible to import a flat text file of fixed length records using BTEQ on Windows?

Thanks.

4 REPLIES
Senior Supporter

Re: Importing fixed length records with BTEQ in Windows environment

You are mentioning fixed length records but you are deining variable length records with

USING (col1 VARCHAR(10), col2 VARCHAR(5), col3 VARCHAR(8), col4 VARCHAR(37)).

Fixed length would require char only.

Can you share the DDL and some rows of your test_file.txt?

Enthusiast

Re: Importing fixed length records with BTEQ in Windows environment

Here's a sample of the file. I've formatted the last row into alternating bold & underline to show the fields. The layout is 10, 5, 8 and 37 characters in each field respectively:

1009828001000PP00000005                                    

1009828002000PP00000005                                                                       

1013382032000RE00000014                                    

I know my data is good as I load it elsewhere.

Here's the table:

CREATE MULTISET TABLE MYDB.TMP_PGN_TEST

  (CMF10 VARCHAR(10),

 MKT_ID VARCHAR(5),

 PGN VARCHAR(8),

 FILL_TXT VARCHAR(37))

PRIMARY INDEX (CMF10);

So if I just go from VARCHAR to CHAR in my USING statment, it should work? I didn't think it would make a difference as each row always has 60 characters in it (not counting CR/LF pairs).

Thanks for your help.

Enthusiast

Re: Importing fixed length records with BTEQ in Windows environment

No need to respond - going from varchar to char did the trick.

Thank you sir!

Senior Supporter

Re: Importing fixed length records with BTEQ in Windows environment

There is a different - a varchar field need to tell how long it is and is information is expect in front of each varchar column. And this was not found in your file as it was fix length char...