help with pulling data from a txt file from unix box to TD table?

Database

help with pulling data from a txt file from unix box to TD table?

Folks, i am new to TD, especially to BTEQ, can any one provide me a script to pull data from a txt file on a UNIX server to TD data base?

Below is one eg row (with column names) that i have in my UNIX file...i need pull the data to a table, but also making sure to remove the "|" pipe symbol in between values.

date|IDentity|acct_num|segment_cd|NNN|pcode|pcode_desc|trans_cnt
--------- ----- -------- ---------- ----------------- ----- ----------------------------------------200801 |825 |2849 |2 | 7027409466.|MP |PREMIUM|6

can any one help with the scrip or direct me to something?
thanks in advance...
S
2 REPLIES

Re: help with pulling data from a txt file from unix box to TD table?

i am using the following code, but running into errors

bteq
.logon DB/U_NAME,*******;
.SET ECHOREQ off
.defaults
.set errorout stderr;
.import REPORT file = sb_nonatm_data.txt
.quiet on
.repeat*
using (ASOF_YYYYMM char(6),CO_ID char (3),ACCT_NUM4 CHAR(5),Segment_Code CHAR(1),ECN DEC(15,0),
PCODE CHAR(2),PCODE_DESC CHAR(40),tran_cnt char (3))
insert into DTUAU.SK_0804_ATM_FEE_LOB_FILE_2 (ASOF_YYYYMM,CO_ID,ACCT_NUM4,Segment_Code,ECN,PCODE,PCODE_DESC,tran_cnt)
values(:ASOF_YYYYMM,:CO_ID,:ACCT_NUM4,:Segment_Code,:ECN,:PCODE,:PCODE_DESC,:tran_cnt)
.quit
;
-------------------------
ERROR
-------------------------
.quit.SET ECHOREQ off
.defaults
.set errorout stderr;
*** Starting Row 0 at Thu Mar 20 13:22:57 2008

.quit.SET ECHOREQ off
$
*** Failure 3706 Syntax error: expected something between ')' and '.'.
Statement# 2, Info =360
*** Total elapsed time was 1 second.

*** Warning: Repeat is cancelled.
*** Finished at input row 1 at Thu Mar 20 13:22:57 2008
*** Total number of statements: 1, Accepted : 0, Rejected : 1

*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
.import REPORT file = 'sb_nonatm_data.txt'

.import REPORT file = 'sb_nonatm_data.txt'
BTEQ -- Enter your DBC/SQL request or BTEQ command:
.quiet OFF

.quiet OFF
BTEQ -- Enter your DBC/SQL request or BTEQ command:
.repeat*
using (ASOF_YYYYMM char(6),CO_ID char (3),ACCT_NUM4 CHAR(5),Segment_Code CHAR(1),ECN DEC(15,0),

.repeat*
BTEQ -- Enter your DBC/SQL request or BTEQ command:

Re: help with pulling data from a txt file from unix box to TD table?

i modified my code as below, now i dont get any error, but the data is not getting loaded onto the table specified.

on the UNIX-BTEQ Screen it just displays nothing after i run the query...its as if its waiting for me to give more commands...

Can some one advise me on what wrong in the below code?
appreciate ur help.

******* CODE *********

.SET ECHOREQ off
.set defaults
.set errorout stderr;
.IMPORT VARTEXT '|' file =/users/apps/affinium/skelapu/test.txt;
.repeat*
USING
(
ASOF_YYYYMM (VARCHAR(6),
CO_ID (VARCHAR(3)),
ACCT_NUM4 (VARCHAR(5)),
Segment_Code (VARCHAR(1)),
ECN (VARCHAR(15)),
PCODE (VARCHAR(2)),
PCODE_DESC (VARCHAR(40)),
tran_cnt (VARCHAR(3))
)
INSERT INTO DTUAU.SK_0804_ATM_FEE_LOB_FILE_3(ASOF_YYYYMM,CO_ID,ACCT_NUM4,Segment_Code,ECN,PCODE,PCODE_DESC,tran_cnt)
VALUES(:ASOF_YYYYMM,:CO_ID,:ACCT_NUM4,:Segment_Code,:ECN,:PCODE,:PCODE_DESC,:tran_cnt)
;
.QUIT
;