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