All Forums Tools
Crow.King 8 posts Joined 12/11
20 Mar 2012
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';

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


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
                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?


ulrich 816 posts Joined 09/09
20 Mar 2012

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?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Crow.King 8 posts Joined 12/11
23 Mar 2012

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:


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

Here's the table:

  (CMF10 VARCHAR(10),

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.

Crow.King 8 posts Joined 12/11
23 Mar 2012

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

Thank you sir!

ulrich 816 posts Joined 09/09
24 Mar 2012

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...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.