All Forums Tools
25 Feb 2009
Need help in BTEQ(Import)

Hi,I am getting below error while inserting data into table ** 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.I am importing data from below file:cat /home/user/jjxyz 1abc 2BTEQ Script as below:.logon xyz/loginid,password.IMPORT DATA FILE = /home/user/jj,SKIP = 2.QUIET ON.REPEAT *USING NAME(VARCHAR(25)),ID(INTEGER)INSERT INTO DB.TABLE(NAME,ID)VALUES(:NAME,:ID);COMMIT; .LOGOFF..QUITMy table defination as:CREATE SET TABLE DB.TABLE,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( NAME VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'MANAGER_NAME' NOT NULL, ID INTEGER)PRIMARY INDEX ( ID );Please help me out of this.Thanks a lot.Regards,Ravi.

bhartiroyal 3 posts Joined 02/09
28 Feb 2009

Hi RaviWhat i understud is :1.u mensioned name as varchar(25),after that space is present ur not considering this space.i feel use.filler * char(1) as another field after name.and change varchar(25) into char(3) correct me if i am wrong.i understud something like that.regradsBharati

Fred 1096 posts Joined 08/04
28 Feb 2009

Your input file is not in the proper format for IMPORT DATA. Looks like VARTEXT delimited by ' ' based on your post.Note that when using VARTEXT the USING clause must describe each input field as VARCHAR; Teradata will translate to the proper column datatype.

07 Mar 2009

Thanks for the reply...I was wrong here only in place of USING NAME(VARCHAR(25)),ID(INTEGER)we can take USING NAME(VARCHAR(10)),ID(VARCHAR(10))TD internally insert data as per data type of columns...

klnsreenivas 12 posts Joined 12/11
18 Nov 2012

Hi,
Can we Import integer values in BTEQ, please help me how to import.
 

K.L.N.Sreenivas

dnoeth 4628 posts Joined 11/04
18 Nov 2012

Of course you can import integers.
What's the format of the input file, binary or text?
For binary you should DEFINE the column as INT else [VAR]CHAR(11)
Dieter

Dieter

klnsreenivas 12 posts Joined 12/11
20 Nov 2012

My file is Employee.CSV
 
 

K.L.N.Sreenivas

dnoeth 4628 posts Joined 11/04
20 Nov 2012

For CSV you should try VARCHAR(11) and set the delimiter to the appropriate char, default is '|'.
Dieter

Dieter

klnsreenivas 12 posts Joined 12/11
20 Nov 2012

Hi,
I have 1,40,000 rows in my CSV file and I need to import into Volatile table,
this is my code
Creating table:
Create Volatile table Employee_Report(F1 Varchar(10), F2 Varchar(10),F3 Varchar(10)) ON COMMIT PRESERVE ROWS
Importing data into table:
Import vartext ',' File=c:\\Employee.CSV;
.REPEAT *;
USING F1 (Varchar(10)), F2 (Varchar(10)),F3 (Varchar(10))
INSERT INTO Employee_Report Values(:F1,:F2,:F3);
Issue:
There is no response in BTEQ screen for hours and i need to close BTEQ forcefully
Please guide me, is BTEQ handles large amount of data?

K.L.N.Sreenivas

dnoeth 4628 posts Joined 11/04
20 Nov 2012

Could you show how the actual script, how you call it and the output?
Does it work when you do a "REPEAT 10"?
BTEQ is not really fast for a larger number of rows, but when you add a ".PACK 1000" this should be finished much faster.
Dieter

Dieter

10 Jan 2013

Hello There,
is there any way to put where condition in Bteq import script. I am getting syntax error
*** Failure 3706 Syntax error: expected something between ')' and the 'wher
 e' keyword.
 
However full file is getting loaded succesfully. I need to filterou some records. Please suggest if any alternate is possible.
.SET ERROROUT STDOUT
.SET QUIET ON
.IMPORT VARTEXT ',' FILE="test.txt";
.REPEAT *
USING
(
id integer,
Name varchar(20)
)
INSERT INTO test_table
values
(
(:id),
(:name)
)
where  id in (sel id from table1);

.logoff;

.QUIT;
 

Qaisar Kiani 337 posts Joined 11/05
11 Jan 2013

In the same script, you can load the records from the file in a staging table and then load the target table from the staged table with the filter condition which you want to apply...

You must sign in to leave a comment.