FASTLOAD WITH FIXED WIDTH FILES

Database

FASTLOAD WITH FIXED WIDTH FILES

Hello..

I'm trying to FASTLOAD a fixed width file with 7 columns on it but when I set the record as "VARTEXT" I get an error which says: Field too large in vartext data record: 1, field: EMP_NBR.

When I set the record as "FORMATTED" I get an error which says: I/O Error on File Read: 16, Text: Unexpected data format

When I set the record as "UNFORMATTED" I get an error which says: The length of: EMP_NBR in row: 1 was greater than defined. Defined: 9, Received: 12353

My Source file looks like the following:

0003200  ABCDEFGHIJKLMN                LAMN      N  CALIFORNIA             1997-06-01  2004-06-30

0003200  ABCDEFGHIJKLMN                LAMN      N  LOS ANGELES           2004-07-01  2006-11-30

0003200  ABCDEFGHIJKLMN                LAMN      N  HARTFORD                2006-12-01  2007-12-31

0003200  ABCDEFGHIJKLMN                LAMN      N  OHIO                        2008-01-01  9999-12-31

0003201  ABCDEFGHIJKLMN ZZZ         LAMN      N  MIDWEST                 1998-10-01  2004-06-30

0003201  ABCDEFGHIJKLMN ZZZ         LAMN      N  PHOENIX                  2004-07-01  2006-11-30

0003201  ABCDEFGHIJKLMN ZZZ         LAMN      N  MINNESOTA              2006-12-01  2007-12-31

Can some one pls. help in this regard what could be the reason and which is the right format to be used ?

Appreciate any help in this regard.

Thanks.

19 REPLIES
Junior Contributor

Re: FASTLOAD WITH FIXED WIDTH FILES

Fixed width is no VARTEXT, but TEXT.

If there's always a blank between columns simply define it as CHAR(1).

Dieter

Re: FASTLOAD WITH FIXED WIDTH FILES

Thanks for the response.. I tried loading by changing it to Text but now getting a different error:

0008 DEFINE

     EMP_NBR (VARCHAR(9)),

     EMP_NM (VARCHAR(27)),

     EMP_DEPT (VARCHAR(10)),

     EMP_MGR(VARCHAR(3)),

     EMP_LOC_NM (VARCHAR(22)),

     EFF_DT (VARCHAR(12)),

     TERM_DT (VARCHAR(10))

     FILE = EMPLOYEE.DAT;

**** 15:53:08 FDL4803 DEFINE statement processed

0009 BEGIN LOADING TEST.EMPLOYEE

     ERRORFILES TEST.EMP_error_1, TEST.EMP_error_2;

**** 15:53:09 Number of AMPs available: 90

**** 15:53:09 BEGIN LOADING COMPLETE

     ===================================================================

     =                                                                 =

     =          Insert Phase                                           =

     =                                                                 =

     ===================================================================

0010 INSERT INTO TEST>EMP     VALUES(

     :EMP_NBR,

     :EMP_NM,

     :EMP_DEPT,

     :EMP_MGR,

     :EMP_LOC_NM,

     :EFF_DT,

     :TERM_DT

     );

**** 15:53:09 Number of recs/msg: 655

**** 15:53:09 Starting to send to RDBMS with record 1

**** 15:53:09 Bad file or data definition.

**** 15:53:09 The length of: EMP_NBR in row: 1 was greater than defined.

              Defined: 9, Received: 12353

Are you recommending that I define something as CHAR(1) between the columns due to blank ? And if there are multiple blanks still CHAR(1) or something else ?

Junior Contributor

Re: FASTLOAD WITH FIXED WIDTH FILES

VARTEXT = variable length -> VARCHAR

TEXT = fixed length -> CHAR

Everything must be defined using the correct length matching your input file.

If there are any delimiters between columns (which is unusual for fixed width) you should define them accordingly.

Dieter

Re: FASTLOAD WITH FIXED WIDTH FILES

Thanks for your help again.. I changed it to CHAR and now getting some other error:

**** 10:43:36 Number of recs/msg: 655

**** 10:43:36 Starting to send to RDBMS with record 1

**** 10:43:36 RECORD is too long by 7 byte(s)

              Field     1: 0003200

              Field     2: ABCDEFGHIJKLMN

              Field     3: LAMN

              Field     4: N

              Field     5: CALIFORNIA

              Field     6: 1997-06-01

              Field     7: 9999-12-31

I have defined the length based on the data as I see in the file. After the first field there are 2 blank spaces so I have defined the first field as CHAR(9), the data being of 7 characters. Similarly I have done after each field is there something else I need to define ?

Junior Contributor

Re: FASTLOAD WITH FIXED WIDTH FILES

If the source you posted is the actual data then it's not fixed length.

Open the file using a non-proportional font like "Courier New" to spot the differences.

Dieter

Enthusiast

Re: FASTLOAD WITH FIXED WIDTH FILES

Hello,

i'm facing same error. i'm new in teradata just start working few weeks back but i'm still feeling very uncomfortable with TERADATA help and manual. anyways, i wanted to Load few records in table using teradata fastlod. to make the test simple i created a table with just two number fields here is my sample script

.logon localtd/adeel,Aa123456
DATABASE mydb;
drop table fstload;
drop table D_ERR1;
drop table D_ERR2;;

CREATE TABLE mydb.FSTLOAD
     (
      PID INTEGER,
      Store_Code INTEGER)
UNIQUE PRIMARY INDEX ( PID );

DEFINE
 PID    (INTEGER)
       ,Store_Code   (INTEGER)
FILE = C:\TEMP\FASTLOAD.txt;

BEGIN LOADING mydb.FSTLOAD

ERRORFILES mydb.D_Err1,mydb.D_Err2
CHECKPOINT 1000;

INSERT INTO mydb.FSTLOAD
  ( :PID
  ,:Store_Code);

END LOADING;
LOGOFF;

here is my data source

1|8

2|8

3|8

4|8

i user teradata standard format therefore i did not use any set record command. i read in manual by default formatted is used and | pipe is default delimiter.

when i ran this i got error at insert statement like

**** 11:10:14 Number of AMPs available: 2

**** 11:10:14 BEGIN LOADING COMPLETE

     ===================================================================

     =                                                                 =

     =          Insert Phase                                           =

     =                                                                 =

     ===================================================================

0009 INSERT INTO mydb.FSTLOAD

                ( :PID

                ,:Store_Code);

**** 11:10:14 Number of recs/msg: 4943

**** 11:10:14 Starting to send to RDBMS with record 1

**** 11:10:14 I/O Error on File Read: 16, Text: Unexpected data format

     ===================================================================

any help? ihave tried many option but every time get error at this point. i have detailed study teradata fastload command manual but still not able to load 4 record in table

Junior Contributor

Re: FASTLOAD WITH FIXED WIDTH FILES

#1: FORMATTED is a binary format

SET RECORD VARTEXT

#2: VARTEXT expects all fields defined as VRACHAR

DEFINE

 PID    (VARCHAR(11))

       ,Store_Code   (VARCHAR(11))

 

 

Dieter

 

 

Enthusiast

Re: FASTLOAD WITH FIXED WIDTH FILES

Thank, you are the only one who is helping here. why people of Teradata not very activitely reply as on Oracle forum? i must say teradata should review its documentation or even make it simple so fresh reader can understand what they are delivering. My problem not solve by just adding SET RECORD VARTEXT i have done this earlier when i put SET RECORD VARTEXT in script file error change with this text " I/O Error on File Read: 35, Text: EOF encountered before

end of record" then i have to press enter and again execute the command which ran successfully this time. could you tell me how can i load integer data without define it varchar in DEFINE clause? i have seen many example where people using Integer as a integer. i think it is possible only when you have mix format data like some fields belog to varchar some are integer and some represent date format. well i will run all the cases and let you know if i stuck somewhere. we have plan to shift our data from oracle to teradata but it seems to be very difficult becoz there is very fewer help on web. furthermore, very least books are available. can you suggest me any good book for build sound ground in teradata.

regrads

adeel

Enthusiast

Re: FASTLOAD WITH FIXED WIDTH FILES

Now i have tried another file to be loaded by fast load this time no error occured and record count was 6 but no row inserted in table. error table 1 filled with 6rows according to doc


Name of the first error table.


A name that duplicates the name of an existing table cannot be used


unless restarting a paused Teradata FastLoad job. The name must be a


new table name.

SESSIONS 4;

ERRLIMIT 25;

.logon localtd/adeel,Aa123456

DATABASE mydb;

drop table contact;

drop table Error1;

drop table Error2;

CREATE TABLE contact

(contact_number INTEGER

,contact_name CHAR(30) NOT NULL

,phone INTEGER NOT NULL

,extension INTEGER

,last_call_date DATE FORMAT 'YY/MM/DD' NOT NULL)

UNIQUE PRIMARY INDEX (contact_number);

SET RECORD VARTEXT "|";

DEFINE contact_number (varchar(25)),contact_name (VARCHAR(30))

,phone (varchar(25)),extension (varchar(25))

,last_call_date (varchar(25))

FILE=C:\TEMP\SAMPLE1.txt;

SHOW;

BEGIN LOADING mydb.contact ERRORFILES

mydb.Error1,mydb.Error2;

INSERT INTO mydb.contact

(contact_number,contact_name,phone,extension

,last_call_date)

VALUES

(:contact_number,:contact_name,:phone,:extension

,:last_call_date

);

END LOADING;

LOGOFF;

data source

|8007|Smith,Ginny|3792152|333|88/08/05|

|8010|Brayt,onConnie|1112345|112|87/07/21|

|8005|Hughes,Jack|5432126|710|87/08/05|

|8001|Leblanc,James|2213456|221|87/08/01|

|8008|Torres,Alison|5487890|444|88/08/14|

|8015|Dibble,Nancy|2713387|652|88/08/09|

Now why fasload sending all row in table one bad record any insght?

 Output of table 1

      2679 PHONE          0x8000000400383030380d00546f727265732c416c69736f6e0700353438373839300300343434
      2679 PHONE          0x8000000400383030310d004c65626c616e632c4a616d65730700323231333435360300323231
      2679 PHONE          0x8000000400383031300e0042726179742c6f6e436f6e6e69650700313131323334350300313132
      2679 PHONE          0x8000000400383031350c00446962626c652c4e616e63790700323731333338370300363532
      2679 PHONE          0x8000000400383030370b00536d6974682c47696e6e790700333739323135320300333333
      2679 PHONE          0x8000000400383030350b004875676865732c4a61636b0700353433323132360300373130