In Teradata using bteq utility to load file .txt extension to a table.(import)

Problem Statement: In Teradata using bteq utility to load file .txt extension to a table.(import)

 ---
 A sample file:
hyperjcop1:~/samplepro # cat test.txt
first,second,lname
20,21,sam
21,22,ram
23,24,siri
25.26,sat
27.28,joe
29,30,jim
31,32,rita
33,34,doe
35,36,cathy
36,37,missy

-----------------------------------------------------
cli>bteq
it will prompt :
 Teradata BTEQ 13.10.00.00 for LINUX. Enter your logon or BTEQ command:
.logon dbc/vmtest
 .logon dbc/vmtest
Password:(enter your password)
 *** Logon successfully completed.
 *** Teradata Database Release is 13.10.00.10
 *** Teradata Database Version is 13.10.00.14
 *** Transaction Semantics are BTET.
 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.
 BTEQ -- Enter your SQL request or BTEQ command:

.run file = /root/samplepro/btscript1.btq(the script file your want to execute for import)
------------------------------------

hyperjcop1:~/samplepro # vi btscript1.btq

*(write the file and save it)

.logon dbc/vmtest,vmtest;

.remark "<script file to load data from test.txt to eztest table in vmtest >"

sel date, time ;
.set width 132;

.import VARTEXT ','  file = /root/samplepro/test.txt

.quiet on ;

.REPEAT *

using d1(varchar(10)), d2(varchar(10)), d3(varchar(20))
insert into vmtest.eztest
values(:d1,:d2,:d3);
------------------------------

Note:
a) the above sample file has a integer format fields in first,second 
b)but in using clause coded varchar(10) to represent them , because the import is taken as vartext with comma as delimiter(.import VARTEXT ',')
c)But the table eztest is having columns  first_field1,second_field2 as integers .
d)the above script runs fine without errors loading the first,second field of sample text (test.txt) into table as integer values .
d1(varchar(10)) ------>first_field1(integer) in eztest table

 d2(varchar(10))------->second_field2 (integer) in eztest table
the implicit conversion takes place.
e)
Hint :will load wrong values, but script will run fine without errors. (Doesn't see values as 20,21 as above, in the table but stored with  numeric conversation to a 10 digit value equivalent )
d1(integer) ------>first_field1(integer) in eztest table

 d2(integer)------->second_field2 (integer) in eztest table

No comments:

Post a Comment