Importing data in data mode is failing in BTEQ

General
Enthusiast

Importing data in data mode is failing in BTEQ

Hi All,

i have just started an exercise "Importing Data in Data Mode uisng BTEQ" ,however my scirpt failed ,below are the details.

Script

.SET SESSION TRANSACTION ANSI

.SET FORMAT ON

.SET INDICDATA OFF

.LOGON 127.0.0.1/dbc,dbc

 DATABASE USER_DBC;

.IMPORT DATA FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt'

.REPEAT *

USING

     (

      ORG VARCHAR(30),

      PARTY_ID INTEGER,

      PARTY_FST_NM VARCHAR(30),

      PARTY_LST_NM VARCHAR(30),

      PARTY_LOC VARCHAR(30),

      PARTY_INCOME INTEGER,

      JDATE DATE

)

INSERT INTO PARTY1

VALUES 

(

:ORG,

:PARTY_ID,

:PARTY_FST_NM,

:PARTY_LST_NM,

:PARTY_LOC,

:PARTY_INCOME,

:JDATE

);

.LOGOFF

.QUIT


.IMPORT DATA FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt'

Data in the file- --IMPORT_DATA_PARTY1

ABC,1,Rakesh Reddy,Gopidi,Hyderabad,750000,2014-11-26

Error Message

 Teradata BTEQ 15.00.00.00 for WIN32. PID: 6148

 Copyright 1984-2014, Teradata Corporation. ALL RIGHTS RESERVED.

 Enter your logon or BTEQ command:

.LOGON 127.0.0.1/DBC

.LOGON 127.0.0.1/DBC

Password: 

***

 *** Logon successfully completed.

 *** Teradata Database Release is 13.00.00.12                   

 *** Teradata Database Version is 13.00.00.12                     

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command: 

.SET SESSION TRANSACTION ANSI.SET SESSION TRANSACTION ANSI

.SET FORMAT ON

.SET INDICDATA OFF

.LOGON 127.0.0.1/dbc,dbc

 DATABASE USER_DBC;

.IMPORT DATA FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt'

.REPEAT *

USING

     (

      ORG VARCHAR(30),

      PARTY_ID INTEGER,

      PARTY_FST_NM VARCHAR(30),

      PARTY_LST_NM VARCHAR(30),

      PARTY_LOC VARCHAR(30),

      PARTY_INCOME INTEGER,

      JDATE DATE

)

INSERT INTO PARTY1

VALUES 

(

:ORG,

:PARTY_ID,

:PARTY_FST_NM,

:PARTY_LST_NM,

:PARTY_LOC,

:PARTY_INCOME,

:JDATE

);

.LOGOFF

.QUIT

 *** Error: You must not be logged on to change the SQLFLAG or 

            TRANSACTION settings.

 BTEQ -- Enter your SQL request or BTEQ command: 

.SET FORMAT ON

 BTEQ -- Enter your SQL request or BTEQ command: 

.SET INDICDATA OFF

 BTEQ -- Enter your SQL request or BTEQ command: 

.LOGON 127.0.0.1/dbc,dbc

 *** Error:  Invalid logon!

 BTEQ -- Enter your SQL request or BTEQ command: 

 DATABASE USER_DBC;

 *** New default database accepted. 

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command: 

.IMPORT DATA FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt'

 BTEQ -- Enter your SQL request or BTEQ command: 

.REPEAT *

 BTEQ -- Enter your SQL request or BTEQ command: 

USING

     (

      ORG VARCHAR(30),

      PARTY_ID INTEGER,

      PARTY_FST_NM VARCHAR(30),

      PARTY_LST_NM VARCHAR(30),

      PARTY_LOC VARCHAR(30),

      PARTY_INCOME INTEGER,

      JDATE DATE

)

INSERT INTO PARTY1

VALUES 

(

:ORG,

:PARTY_ID,

:PARTY_FST_NM,

:PARTY_LST_NM,

:PARTY_LOC,

:PARTY_INCOME,

:JDATE

);

 *** Starting Row 0 at Fri Sep 12 22:08:56 2014

 *** Error: The following occurred during an Access Module read:

 Unexpected data format !ERROR! EOF encountered before expected EOR.

 *** Warning: Out of data. 

 *** Finished at Fri Sep 12 22:08:56 2014

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command: 

.LOGOFF

 *** You are now logged off from the DBC.

 Teradata BTEQ 15.00.00.00 for WIN32. Enter your logon or BTEQ command:

.QUIT

 *** Exiting BTEQ...

 *** RC (return code) = 0 

What could be the issue?


11 REPLIES
Junior Contributor

Re: Importing data in data mode is failing in BTEQ

Hint: the data is a comma-delimited text file and you .IMPORT DATA :-)

Enthusiast

Re: Importing data in data mode is failing in BTEQ

Hi Dieter,

i have changed accordingly but now am getting this error

.SET SESSION TRANSACTION ANSI

.SET FORMAT ON

.SET INDICDATA OFF

.LOGON 127.0.0.1/dbc,dbc

 DATABASE USER_DBC;

.IMPORT vartext ',' FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt' SKIP=1;

USING

     (

      ORG VARCHAR(30),

      PARTY_ID INTEGER,

      PARTY_FST_NM VARCHAR(30),

      PARTY_LST_NM VARCHAR(30),

      PARTY_LOC VARCHAR(30),

      PARTY_INCOME INTEGER,

      JDATE DATE

)

INSERT INTO USER_DBC.PARTY1(ORG,PARTY_ID,PARTY_FST_NM,PARTY_LST_NM,PARTY_LOC,PARTY_INCOME,JDATE)      

VALUES 

(

:ORG,

:PARTY_ID,

:PARTY_FST_NM,

:PARTY_LST_NM,

:PARTY_LOC,

:PARTY_INCOME,

:JDATE,

);

.LOGOFF

.QUIT

IMPORT FILE : DATA

ORG,PARTY_ID,PARTY_FST_NM,PARTY_LST_NM,PARTY_LOC,PARTY_INCOME,JDATE

ABC,1,Rakesh,Gopidi,Hyderabad,750000,2014-11-26

TARGET  table in which iam loading the data into

CREATE MULTISET TABLE USER_DBC.PARTY1 ,FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      ORG VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      PARTY_ID INTEGER,

      PARTY_FST_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      PARTY_LST_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      PARTY_LOC VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      PARTY_INCOME INTEGER,

      JDATE DATE FORMAT 'YYYY-MM-DD')

PRIMARY INDEX ( ORG );

Errror



C:\Users\rgopidi>BTEQ

 Teradata BTEQ 15.00.00.00 for WIN32. PID: 7148

 Copyright 1984-2014, Teradata Corporation. ALL RIGHTS RESERVED.

 Enter your logon or BTEQ command:

.LOGON 127.0.0.1/DBC

.LOGON 127.0.0.1/DBC

Password:

 *** Logon successfully completed.

 *** Teradata Database Release is 13.00.00.12

 *** Teradata Database Version is 13.00.00.12

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

.SET SESSION TRANSACTION ANSI

.SET SESSION TRANSACTION ANSI

 *** Error: You must not be logged on to change the SQLFLAG or

            TRANSACTION settings.

 BTEQ -- Enter your SQL request or BTEQ command:

.SET FORMAT ON

.SET FORMAT ON

 BTEQ -- Enter your SQL request or BTEQ command:

.SET INDICDATA OFF

.SET INDICDATA OFF

 BTEQ -- Enter your SQL request or BTEQ command:

.LOGON 127.0.0.1/dbc,dbc

.LOGON 127.0.0.1/dbc,dbc

 *** Error:  Invalid logon!

 BTEQ -- Enter your SQL request or BTEQ command:

 DATABASE USER_DBC;

DATABASE USER_DBC;

 *** New default database accepted.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

.IMPORT vartext ',' FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt' SKIP=1;

.IMPORT vartext ',' FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt' SKIP=1

;

 1 input row skipped.

 BTEQ -- Enter your SQL request or BTEQ command:

USING

     (

      ORG VARCHAR(30),

      PARTY_ID INTEGER,

      PARTY_FST_NM VARCHAR(30),

      PARTY_LST_NM VARCHAR(30),

      PARTY_LOC VARCHAR(30),

      PARTY_INCOME INTEGER,

      JDATE DATE

)

INSERT INTO USER_DBC.PARTY1(ORG,PARTY_ID,PARTY_FST_NM,PARTY_LST_NM,PARTY_LOC,PARTY_INCOME,JDATE)

VALUES

(

:ORG,

:PARTY_ID,

:PARTY_FST_NM,

:PARTY_LST_NM,

:PARTY_LOC,

:PARTY_INCOME,

:JDATE,

);

USING

     (

      ORG VARCHAR(30),

      PARTY_ID INTEGER,

      PARTY_FST_NM VARCHAR(30),

      PARTY_LST_NM VARCHAR(30),

      PARTY_LOC VARCHAR(30),

      PARTY_INCOME INTEGER,

      JDATE DATE

)

INSERT INTO USER_DBC.PARTY1(ORG,PARTY_ID,PARTY_FST_NM,PARTY_LST_NM,PARTY_L

OC,PARTY_INCOME,JDATE)

VALUES

(

:ORG,

:PARTY_ID,

:PARTY_FST_NM,

:PARTY_LST_NM,

:PARTY_LOC,

:PARTY_INCOME,

:JDATE,

);

);

$

 *** Failure 3706 Syntax error: Column name list shorter than value list.

                Statement# 1, Info =397

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

.LOGOFF

.LOGOFF

 *** You are now logged off from the DBC.

 Teradata BTEQ 15.00.00.00 for WIN32. Enter your logon or BTEQ command:

.QUIT

.QUIT

 *** Exiting BTEQ...

 *** RC (return code) = 8

Where iam missing out?????

Enthusiast

Re: Importing data in data mode is failing in BTEQ

oops i did check the issues 

http://forums.teradata.com/forum/general/bteq-import-data-file-error-0

rectified the issues ,however again the script failing.....


C:\Users\rgopidi>BTEQ

 Teradata BTEQ 15.00.00.00 for WIN32. PID: 1480

 Copyright 1984-2014, Teradata Corporation. ALL RIGHTS RESERVED.

 Enter your logon or BTEQ command:

.LOGON 127.0.0.1/DBC

.LOGON 127.0.0.1/DBC

Password:

 *** Logon successfully completed.

 *** Teradata Database Release is 13.00.00.12

 *** Teradata Database Version is 13.00.00.12

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

.SET SESSION TRANSACTION ANSI

.SET SESSION TRANSACTION ANSI

 *** Error: You must not be logged on to change the SQLFLAG or

            TRANSACTION settings.

 BTEQ -- Enter your SQL request or BTEQ command:

.SET FORMAT ON

.SET FORMAT ON

 BTEQ -- Enter your SQL request or BTEQ command:

.SET INDICDATA OFF

.SET INDICDATA OFF

 BTEQ -- Enter your SQL request or BTEQ command:

.LOGON 127.0.0.1/dbc,dbc

.LOGON 127.0.0.1/dbc,dbc

 *** Error:  Invalid logon!

 BTEQ -- Enter your SQL request or BTEQ command:

 DATABASE USER_DBC;

DATABASE USER_DBC;

 *** New default database accepted.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

.IMPORT vartext ',' FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt' SKIP=1;

.IMPORT vartext ',' FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt' SKIP=1

;

 1 input row skipped.

 BTEQ -- Enter your SQL request or BTEQ command:

USING

     (

      ORG VARCHAR(30),

      PARTY_ID INTEGER,

      PARTY_FST_NM VARCHAR(30),

      PARTY_LST_NM VARCHAR(30),

      PARTY_LOC VARCHAR(30),

      PARTY_INCOME INTEGER,

      JDATE DATE

)

INSERT INTO USER_DBC.PARTY1(ORG,PARTY_ID,PARTY_FST_NM,PARTY_LST_NM,PARTY_LOC,PARTY_INCOME,JDATE)

VALUES

(

:ORG,

:PARTY_ID,

:PARTY_FST_NM,

:PARTY_LST_NM,

:PARTY_LOC,

:PARTY_INCOME,

:JDATE

);

USING

     (

      ORG VARCHAR(30),

      PARTY_ID INTEGER,

      PARTY_FST_NM VARCHAR(30),

      PARTY_LST_NM VARCHAR(30),

      PARTY_LOC VARCHAR(30),

      PARTY_INCOME INTEGER,

      JDATE DATE

)

INSERT INTO USER_DBC.PARTY1(ORG,PARTY_ID,PARTY_FST_NM,PARTY_LST_NM,PARTY_L

OC,PARTY_INCOME,JDATE)

VALUES

(

:ORG,

:PARTY_ID,

:PARTY_FST_NM,

:PARTY_LST_NM,

:PARTY_LOC,

:PARTY_INCOME,

:JDATE

);

 *** Failure 2673 The source parcel length does not match data that was defi

 ned.

                Statement# 1, Info =3

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

.LOGOFF

.LOGOFF

 *** You are now logged off from the DBC.

 Teradata BTEQ 15.00.00.00 for WIN32. Enter your logon or BTEQ command:

.QUIT

.QUIT

 *** Exiting BTEQ...

 *** RC (return code) = 8

C:\Users\rgopidi>

Junior Contributor

Re: Importing data in data mode is failing in BTEQ

Hi Rakesh,

when you import using VARTEXT, all fields must be defined as VARCHAR, but you got INT and DATE, too.

Enthusiast

Re: Importing data in data mode is failing in BTEQ

ok i got it now..thanks 

Enthusiast

Re: Importing data in data mode is failing in BTEQ

Rules for all Platforms

• The  o nl y  a c c e pt a bl e  d a t a  t y pe s  f or  VARTEXT r e c or ds  a r e  VARCHAR,  VARBYTE,  a nd

LONG VARCHAR. Undesirable results occur if other data types are used.

Also, you can remove this line : .SET SESSION TRANSACTION ANSI
Enthusiast

Re: Importing data in data mode is failing in BTEQ

Hi All,

i did tried to correct the errors ,however i was not able to figure out the issues...


C:\Users\rgopidi>BTEQ

 Teradata BTEQ 15.00.00.00 for WIN32. PID: 5628

 Copyright 1984-2014, Teradata Corporation. ALL RIGHTS RESERVED.

 Enter your logon or BTEQ command:

.LOGON 127.0.0.1/DBC

.LOGON 127.0.0.1/DBC

Password:

 *** Logon successfully completed.

 *** Teradata Database Release is 13.00.00.12

 *** Teradata Database Version is 13.00.00.12

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

.SET FORMAT ON

.SET FORMAT ON

 BTEQ -- Enter your SQL request or BTEQ command:

.SET INDICDATA OFF

.SET INDICDATA OFF

 BTEQ -- Enter your SQL request or BTEQ command:

.LOGON 127.0.0.1/dbc,dbc

.LOGON 127.0.0.1/dbc,dbc

 *** Error:  Invalid logon!

 BTEQ -- Enter your SQL request or BTEQ command:

 DATABASE USER_DBC;

DATABASE USER_DBC;

 *** New default database accepted.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

.IMPORT VARTEXT ',' FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt' ,skip=1;

.IMPORT VARTEXT ',' FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt' ,skip=

1;

 1 input row skipped.

 BTEQ -- Enter your SQL request or BTEQ command:

.REPEAT *;

.REPEAT *;

 BTEQ -- Enter your SQL request or BTEQ command:

USING

(     ORG,

      CAST(PARTY_ID AS VARCHAR(30)) PARTY_ID,

      PARTY_FST_NM,

      PARTY_LST_NM,

      PARTY_LOC,

     CAST(PARTY_INCOME AS VARCHAR(30)) PARTY_INCOME,

    CAST(CAST(JDATE AS FORMAT 'MMM-DD-YY') AS VARCHAR(15)) JDDATE

)

INSERT INTO PARTY1

VALUES

(

:ORG,

:PARTY_ID,

:PARTY_FST_NM,

:PARTY_LST_NM,

:PARTY_LOC,

:PARTY_INCOME,

:JDATE,

);

USING

(     ORG,

      CAST(PARTY_ID AS VARCHAR(30)) PARTY_ID,

      PARTY_FST_NM,

      PARTY_LST_NM,

      PARTY_LOC,

     CAST(PARTY_INCOME AS VARCHAR(30)) PARTY_INCOME,

    CAST(CAST(JDATE AS FORMAT 'MMM-DD-YY') AS VARCHAR(15)) JDDATE

)

INSERT INTO PARTY1

VALUES

(

:ORG,

:PARTY_ID,

:PARTY_FST_NM,

:PARTY_LST_NM,

:PARTY_LOC,

:PARTY_INCOME,

:JDATE,

);

 *** Starting Row 1 at Sun Sep 14 00:10:29 2014

      CAST(PARTY_ID AS VARCHAR(30)) PARTY_ID,

$

 *** Failure 3706 Syntax error: expected something between the word 'ORG' an

 d ','.

                Statement# 1, Info =18

 *** Total elapsed time was 1 second.

 *** Warning: Repeat is cancelled.

 *** Finished at input row 2 at Sun Sep 14 00:10:29 2014

 *** Total number of statements: 1,  Accepted : 0,  Rejected : 1

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

.LOGOFF

.LOGOFF

 *** You are now logged off from the DBC.

 Teradata BTEQ 15.00.00.00 for WIN32. Enter your logon or BTEQ command:

.QUIT

Note : i have tested the Query against the DB in SQL ASST

SELECT     
ORG,
CAST(PARTY_ID AS VARCHAR(15)) PARTY_ID,
PARTY_FST_NM,
PARTY_LST_NM,
PARTY_LOC,
CAST(PARTY_INCOME AS VARCHAR(15)) PARTY_INCOME,
CAST( CAST(JDATE AS FORMAT 'MMM-DD-YY') AS VARCHAR(15)) JDDATE
FROM USER_DBC.PARTY;

Working fine..but some thing is missing

Junior Contributor

Re: Importing data in data mode is failing in BTEQ

USING
(
ORG VARCHAR(30),
PARTY_ID VARCHAR(11),
PARTY_FST_NM VARCHAR(30),
PARTY_LST_NM VARCHAR(30),
PARTY_LOC VARCHAR(30),
PARTY_INCOME VARCHAR(11),
JDATE VARCHAR(9)
)
Enthusiast

Re: Importing data in data mode is failing in BTEQ

Thanks Dieter...script ran filne data loaded

i am performing lot of mistakes...i know these are Elimentary level errors...thanks a lot for correcting/guiding..

Sucess Result


C:\Users\rgopidi>BTEQ

TERADATA BTEQ 15.00.00.00 FOR WIN32. PID: 7504
Copyright 1984-2014, TERADATA Corporation. ALL RIGHTS RESERVED.
Enter your LOGON OR BTEQ command:
.
LOGON 127.0.0.1/DBC

.
LOGON 127.0.0.1/DBC
PASSWORD:

*** LOGON successfully completed.
*** TERADATA DATABASE RELEASE IS 13.00.00.12
*** TERADATA DATABASE Version IS 13.00.00.12
*** TRANSACTION Semantics are BTET.
*** SESSION CHARACTER SET NAME IS 'ASCII'.

*** Total elapsed TIME was 1 SECOND.

BTEQ -- Enter your SQL request or BTEQ command:
.SET FORMAT ON

.SET FORMAT ON
BTEQ -- Enter your SQL request or BTEQ command:
.SET INDICDATA OFF

.SET INDICDATA OFF
BTEQ -- Enter your SQL request or BTEQ command:
.LOGON 127.0.0.1/dbc,dbc

.
LOGON 127.0.0.1/dbc,dbc

*** ERROR: Invalid LOGON!

BTEQ -- Enter your SQL request or BTEQ command:
DATABASE USER_DBC;

DATABASE USER_DBC;

*** NEW DEFAULT DATABASE accepted.
*** Total elapsed TIME was 1 SECOND.

BTEQ -- Enter your SQL request or BTEQ command:
.IMPORT VARTEXT ',' FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt' ,skip=1;

.
IMPORT VARTEXT ',' FILE = 'C:\Users\rgopidi\IMPORT_DATA_PARTY1.txt' ,skip=
1;
1 INPUT ROW skipped.
BTEQ -- Enter your SQL request or BTEQ command:
USING
(
ORG VARCHAR(30),
PARTY_ID VARCHAR(30),
PARTY_FST_NM VARCHAR(30),
PARTY_LST_NM VARCHAR(30),
PARTY_LOC VARCHAR(30),
PARTY_INCOME VARCHAR(30),
JDATE VARCHAR(30)
)

INSERT INTO PARTY1 (ORG,PARTY_ID,PARTY_FST_NM,PARTY_LST_NM,PARTY_LOC,PARTY_INCOME,JDATE)
VALUES
(
:
ORG,
:
PARTY_ID,
:
PARTY_FST_NM,
:
PARTY_LST_NM,
:
PARTY_LOC,
:
PARTY_INCOME,
:
JDATE
)
;

USING
(
ORG VARCHAR(30),
PARTY_ID VARCHAR(30),
PARTY_FST_NM VARCHAR(30),
PARTY_LST_NM VARCHAR(30),
PARTY_LOC VARCHAR(30),
PARTY_INCOME VARCHAR(30),
JDATE VARCHAR(30)
)

INSERT INTO PARTY1 (ORG,PARTY_ID,PARTY_FST_NM,PARTY_LST_NM,PARTY_LOC,PARTY
_INCOME,JDATE)
VALUES
(
:
ORG,
:
PARTY_ID,
:
PARTY_FST_NM,
:
PARTY_LST_NM,
:
PARTY_LOC,
:
PARTY_INCOME,
:
JDATE
)
;

*** INSERT completed. One ROW added.
*** Total elapsed TIME was 1 SECOND.

BTEQ -- Enter your SQL request or BTEQ command:


.
LOGOFF

.
LOGOFF
*** You are now logged OFF FROM the DBC.
TERADATA BTEQ 15.00.00.00 FOR WIN32. Enter your LOGON OR BTEQ command:
.QUIT