All Forums Database
DZanke 14 posts Joined 11/15
26 Jan 2016
BTEQ export FastLoad Import issue

I am trying to export data using BTEQ and import it using FastLoad. We are using Teradata version using a windows 7 client. I read that BTEQs default export mode is FastLoad, yet I've made multiple attempts to load the data exported from BTEQ  using FastLoad w/o success . Every time I attempt a load, I get a message like "Bad file or data definition. The length of: definedvar in row 1 was greate than defined. Defined: 17, Received: 18519". My define statement looks ok, and teh exported file consists of 5 records, the first 2 bytes give the length, followed by the data column, a lf/cr, then the next column length, which looks like it's probably correct.
BTEQ export:

.logon ip/dzanke;

.Set RecordMode On;

.EXPORT Data File=DZanke2.dat;

Select  Vinnie

    From database.DZanke;


.EXPORT Reset;



This exports a single column, 5 rows as described in the first paragraph.





logon ip/dzanke;


Begin Loading databasename.DZanke

 Errorfiles databasename.ERR1,databasename.ERR2;


Define Vinnie ( VarChar(17))

File = DZanke2.dat ;


Insert Into databasename.DZanke




Values (      :Vinnie


End Loading;



I've tried everything I can think of, looked in the manuals and on the web and still get this same error. I'm sure I missed something simple, and would appreciate it if someone could tell me what it is, because I can't figure it out.



Adeel Chaudhry 773 posts Joined 04/08
26 Jan 2016

Can you share the sample output file that is generated via export?

-- If you are stuck at something .... consider it an opportunity to think anew.

DZanke 14 posts Joined 11/15
27 Jan 2016

I could not upload the actual file, but here is a look at it in a Hex editor. To keep it simple, there is only 5 rows, one column, 17 characters long.

Fred 1096 posts Joined 08/04
27 Jan 2016

Is the column defined as CHAR(17), in which case you need to specify that in the DEFINE as well?
FastLoad format starts with a record length and if the field was VARCHAR then there would be a field length after that, followed by the data value.

DZanke 14 posts Joined 11/15
27 Jan 2016

Wow, good catch Fred! It was char 17 in the DB, but I read in one of my sources for FastLoad scripts that "FastLoad DEFINE statement allows only VARCHAR format". Either that is incorrect, or I misunderstood what was meant. I changed the define to char and it worked! Thanks, Fred, I was really spinning my wheels on this one, and thanks for the info on FastLoad format.

Fred 1096 posts Joined 08/04
27 Jan 2016

To clarify the restriction:

DEFINE must describe the input fields (not necessarily the same as the target table columns). And if the file format is specified as VARTEXT (delimited text), then the I/O module parses the input record and passes the fields to FastLoad as VARCHARs.

You must sign in to leave a comment.