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 for the response.. I tried loading by changing it to Text but now getting a different error:
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(
**** 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 ?
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.
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 ?
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.
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
drop table fstload;
drop table D_ERR1;
drop table D_ERR2;;
CREATE TABLE mydb.FSTLOAD
UNIQUE PRIMARY INDEX ( PID );
FILE = C:\TEMP\FASTLOAD.txt;
BEGIN LOADING mydb.FSTLOAD
INSERT INTO mydb.FSTLOAD
here is my data source
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
**** 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
#1: FORMATTED is a binary format
SET RECORD VARTEXT
#2: VARTEXT expects all fields defined as VRACHAR
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.
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.
drop table contact;
drop table Error1;
drop table Error2;
CREATE TABLE contact
,contact_name CHAR(30) NOT NULL
,phone INTEGER NOT NULL
,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))
BEGIN LOADING mydb.contact ERRORFILES
INSERT INTO mydb.contact
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