fast load quotes field

Database
Enthusiast

fast load quotes field

Hello friends -

I am trying to run a fast load for the source file which has data in quotes and i am getting the below errror

**** 16:20:37 Expected DISPLAY_ERRORS, NOSTOP or a valid delimiter

              after the VARTEXT keyword

 TD version i am using is 14 i have pressed enter at the last reocrd but still getting same error and below is my script....please help...

SESSIONS 5;

TENACITY 5;

SLEEP 5;

ERRLIMIT 25;

.logon usrid/pwd;

DATABASE dev;

DROP TABLE DUMMY_UV;

DROP TABLE DUMMY_ET;

DROP TABLE DUMMY;

CREATE TABLE DUMMY, NO FALLBACK

( ID INTEGER,

  NAME CHAR(42)

 )PRIMARY INDEX(ID);

SET RECORD VARTEXT DELIMITER ',' QUOTE YES '"';

DEFINE

ID (VARCHAR(10)),

NAME (VARCHAR(42))

FILE=/data/DUMMY.txt;

SHOW;

BEGIN LOADING DUMMY ERRORFILES DUMMY_UV,DUMMY_ET

CHECKPOINT 10000;

INSERT INTO DUMMY

(

ID,

NAME

)

VALUES

(

:ID,

:NAME

);

END LOADING;

.LOGOFF;

source data:

"123","abc"

"456","xyz"

Thanks

john

Tags (1)
6 REPLIES
Enthusiast

Re: fast load quotes field

Usually I feel ENTER is not pressed. You can vi the file  and use ^ and $ or do head -n  or tail the file. I remember in one case where I had a daily job and read from a file. One day the file was created with no ENTER. when I had a look at the file in vi, it looked very much okay. I simulated the job again and again. Half day was spent :). It is resolved by recreating a new file with prioper ENTER :).

Your setting  SET RECORD VARTEXT DELIMITER ',' QUOTE YES '"';  is fine.

Senior Apprentice

Re: fast load quotes field

Hi John,

you said you're using TD14, but did you check your FastLoad version if it supports DELIMITER/QUOTE?

SHOW VERSIONS; should return 14, too.

Enthusiast

Re: fast load quotes field

Hi dnoeth -

When i do show versions i get below...

0006 SHOW VERSION;

FastLoad Version 13.10.00.16 for AIX running Socket TCP/IP

FastLoad : 13.10.00.28

FastCmds : 13.10.00.23

FastIO : 13.10.00.07

FastMBCS : 13.00.00.02

FastNtfy : 13.01.00.00

FastPars : 13.10.00.05

FastSQL : 13.10.00.34

FastUtil : 13.10.00.06

Fdlosdep : 13.01.00.00

Teradata Data Connector : 13.10.00.10

PMPROCS : 13.10.00.15

PMRWFMT : 13.10.00.09

PMTRCE : 13.10.00.02

PMMM : 13.00.00.01

PMHEXDMP : 13.10.00.01

PMUNXDSK : 13.10.00.16

ICUVER : TDICU, 13.10.00.03

CLIV2 : 13.10.00.31

MTDP : 13.10.00.25

MOSIos : 13.10.00.13

MOSIDEP : 13.10.00.12

OSENCRYPT : N/A

OSERR : 13.10.00.01

FastLoad linking date: Jan 9 2013

does this mean i am using version 13 fast load utility but i am sure my database version is below

**** 13:41:56 Teradata Database Release: 14.10.01.02

if this doesnt work could you please let me know the alternate solution for this...Thank you

Senior Apprentice

Re: fast load quotes field

Hi John,

yep, you're using a 13.10 FastLoad against a 14.10 TD, which is definitely causing that problem.

The solution is simple: upgrade FastLoad, simply ask your DBA, he should support this because FastLoad 13.10 is probably not certified to be used against TD14.10.

Enthusiast

Re: fast load quotes field

sure....thank you all for your time....

Enthusiast

Re: fast load quotes field

I tried the same thing above but get error : The length of: NAME in row: 1 was greater than defined.Defined: 67, Received: 30498

Input File:

"NAME","NUM","ADDRESS","ID","CODE"

"A","1","ABC,XYZ","1","1"

Table Defination:

CREATE MULTISET TABLE L_CSV_TEST ,NO FALLBACK ,

     NO BEFORE JOURNAL,     NO AFTER JOURNAL,     CHECKSUM = DEFAULT,     DEFAULT MERGEBLOCKRATIO

     (

      NAME VARCHAR(64) ,

      NUM VARCHAR(64) ,

      ADDRESS VARCHAR(64) ,

      ID VARCHAR(64),

      CODE VARCHAR(64)

NO PRIMARY INDEX ;

.LOGON SERVER/ID/PWD; 

SESSIONS 2;

ERRLIMIT 25;

SET RECORD VARTEXT DELIMITER ',' QUOTE YES '"';

DROP TABLE PRODIGE_STG.L_CSV_TEST_e1;

DROP TABLE PRODIGE_STG.L_CSV_TEST_e2;

SET RECORD UNFORMATTED;

RECORD 2;

DEFINE

  NAME (VARCHAR(67))

, NUM (VARCHAR(67))

, ADDRESS (VARCHAR(67))

, ID (VARCHAR(67))

, CODE (VARCHAR(67))

file=C:\TEMP\CSV_TEST.csv;

SHOW;

BEGIN LOADING PRODIGE_STG.L_CSV_TEST ERRORFILES PRODIGE_STG.L_CSV_TEST_e1, PRODIGE_STG.L_CSV_TEST_e2;

INSERT INTO PRODIGE_STG.L_CSV_TEST

( NAME

, NUM

, ADDRESS

, ID

, CODE

)

VALUES

( :NAME

, :NUM

, :ADDRESS

, :ID

, :CODE

);

END LOADING;

LOGOFF;

***********************************************

ERROR IN AUDIT LOG: The length of: NAME in row: 1 was greater than defined.

              Defined: 67, Received: 30498

AUDIT LOG:

     ===================================================================

     =                                                                 =

     =          FASTLOAD UTILITY     VERSION 14.00.00.06               =

     =          PLATFORM WIN32                                         =

     =                                                                 =

     ===================================================================

     ===================================================================

     =                                                                 =

     =          Copyright 1984-2012, Teradata Corporation.             =

     =          ALL RIGHTS RESERVED.                                   =

     =                                                                 =

     ===================================================================

**** 12:30:10 Processing starting at: Mon Nov 16 12:30:10 2015

**** 12:30:10 Character set has been set to: ASCII

     ===================================================================

     =                                                                 =

     =          Logon/Connection                                       =

     =                                                                 =

     ===================================================================

0001 .LOGON srv/"id","pwd"

**** 12:30:11 Teradata Database Release: 15.00.03.03

**** 12:30:11 Teradata Database Version: 15.00.03.03

**** 12:30:11 Number of AMPs available: 48

**** 12:30:11 Current CLI or RDBMS allows maximum row size: 64K

**** 12:30:11 Character set for this job: ASCII

0002 SESSIONS 2;

**** 12:30:11 FDL4865 Sessions can only be set before logon. Request

              ignored.

**** 12:30:11 Number of sessions currently set to 32767

0003 ERRLIMIT 25;

**** 12:30:11 Error limit set to: 25

0004 SET RECORD VARTEXT DELIMITER ',' QUOTE YES '"';

**** 12:30:11 Now set to read 'Variable-Length Text' records

**** 12:30:11 Delimiter character(s) is set to ','

**** 12:30:11 Field values must be quoted with '"'

**** 12:30:11 Command completed successfully

0005 DROP TABLE PRODIGE_STG.L_CSV_TEST_e1;

**** 12:30:11 Command completed successfully

0006 DROP TABLE PRODIGE_STG.L_CSV_TEST_e2;

**** 12:30:11 Command completed successfully

0007 SET RECORD UNFORMATTED;

**** 12:30:11 Now set to read 'UNFORMATTED' records

**** 12:30:11 Command completed successfully

0008 RECORD 2;

**** 12:30:11 Starting record number set to  : 2

0009 DEFINE

       NAME (VARCHAR(67))

     , NUM (VARCHAR(67))

     , ADDRESS (VARCHAR(67))

     , ID (VARCHAR(67))

     , CODE (VARCHAR(67))

     file=C:\TEMP\CSV_TEST.csv;

**** 12:30:11 FDL4803 DEFINE statement processed

0010 SHOW;

     FILE = C:\TEMP\CSV_TEST.csv

     NAME                             OFFSET =      0 LEN =    67 VARCHAR

     NUM                              OFFSET =     69 LEN =    67 VARCHAR

     ADDRESS                          OFFSET =    138 LEN =    67 VARCHAR

     ID                               OFFSET =    207 LEN =    67 VARCHAR

     CODE                             OFFSET =    276 LEN =    67 VARCHAR

     TOTAL RECORD LENGTH = 345

0011 BEGIN LOADING PRODIGE_STG.L_CSV_TEST ERRORFILES PRODIGE_STG.L_CSV_TEST_e1, 

     PRODIGE_STG.L_CSV_TEST_e2;

**** 12:30:19 Number of FastLoad sessions connected = 48

**** 12:30:19 FDL4808 LOGON successful

**** 12:30:20 Number of AMPs available: 48

**** 12:30:20 BEGIN LOADING COMPLETE

     ===================================================================

     =                                                                 =

     =          Insert Phase                                           =

     =                                                                 =

     ===================================================================

0012 INSERT INTO PRODIGE_STG.L_CSV_TEST

     ( NAME

     , NUM

     , ADDRESS

     , ID

     , CODE

     )

     VALUES

     ( :NAME

     , :NUM

     , :ADDRESS

     , :ID

     , :CODE

     );

**** 12:30:20 The length of: NAME in row: 1 was greater than defined.

              Defined: 67, Received: 30498

**** 12:30:20 Error at record number 0

     ===================================================================

     =                                                                 =

     =          Logoff/Disconnect                                      =

     =                                                                 =

     ===================================================================

**** 12:30:20 Logging off all sessions

**** 12:30:24 Total processor time used = '3.35402 Seconds'

     .        Start : Mon Nov 16 12:30:10 2015

     .        End   : Mon Nov 16 12:30:24 2015

     .        Highest return code encountered = '12'.

**** 12:30:24 FastLoad Paused