I am new to Teradata. I am trying to load a pipe "|" delimited file into Tera. Below is the script I trying to run but am getting errors. Error message posted below code.
SESSIONS 1;
ERRLIMIT 25;
.logon ${ADP_LOGIN}
SET RECORD VARTEXT ‘|’;
DEFINE
rank_no (integer),
yes_code (char(2)),
eng_label (char(100)),
esp_label (char(100)),
ataglance_eng (char(6)),
summary_eng (char(6)),
transdetail_eng (char(6)),
ataglance_esp (char(6)),
summary_esp char(6)),
transdetail_esp (char(6))
FILENAME=./cat_logo.txt;
SHOW;
BEGIN LOADING PROTO_PSD1.mi_yes16_cat_logo
ERRORFILES PROTO_PSD1.mi_yes16_cat_logo_ERR
CHECKPOINT 1000;
INSERT INTO PROTO_PSD1.mi_yes16_cat_logo (rank_no,yes_code,eng_label,esp_label,ataglance_eng,summary_eng,transdetail_eng,ataglance_esp,summary_esp,transdetail_esp)
VALUES (:rank_no,:yes_code,:eng_label,:esp_label,:ataglance_eng,:summary_eng,:transdetail_eng,:ataglance_esp,:summary_esp,:transdetail_esp);
END LOADING;
LOGOFF;
0004 SET RECORD VARTEXT ‘|’;
**** 10:31:06 Invalid SET RECORD VARTEXT command: ‘|’ is invalid
after VARTEXT
The SET RECORD VARTEXT command syntax:
SET RECORD VARTEXT [[DELIMITER] '|'|'c']
[DISPLAY_ERRORS ['error_file'] [NOSTOP]
[TRIM NONE] | [TRIM LEADING|TRAILING|BOTH ['p']]
[QUOTE NO] | [QUOTE OPTIONAL|YES ['"']|['q' ['r']];
**** 10:31:06 Command not processed
===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================
**** 10:31:06 Logging off all sessions
**** 10:31:06 Total processor time used = '0.1 Seconds'
. Start : Wed May 17 10:31:05 2017
. End : Wed May 17 10:31:06 2017
. Highest return code encountered = '8'.
**** 10:31:06 FDL4818 FastLoad Terminated
Solved! Go to Solution.
Since you want to start over, make sure you drop all of the tables (target table and error tables), recreate the target table, and start over again.
BTW, if this is something new, you should be switching over to using TPT (Teradata Parallel Transporter).
Try single quotes '|' instead of ‘|’ (maybe your editor changed the quotes).
Additionally for VARTEXT all input fields must be defined as VARCHARs.
Thanks for the reply, I made those changes and now I am getting this error:
SESSIONS 1;
ERRLIMIT 25;
.logon {ADP_LOGON}
DROP TABLE PROTO_PSD1.mi_yes16_cat_logo_err;
.SET RECORD VARTEXT "|";
DEFINE
rank_no (varchar(10)),
yes_code (varchar(2)),
eng_label (varchar(100)),
esp_label (varchar(100)),
ataglance_eng (varchar(6)),
summary_eng (varchar(6)),
transdetail_eng (varchar(6)),
ataglance_esp (varchar(6)),
summary_esp (varchar(6)),
transdetail_esp (varchar(6))
FILE=./cat_logo.txt;
SHOW;
BEGIN LOADING PROTO_PSD1.mi_yes16_cat_logo ERRORFILES PROTO_PSD1.mi_yes16_cat_logo_err
INSERT INTO PROTO_PSD1.mi_yes16_cat_logo (rank_no,yes_code,eng_label,esp_label,ataglance_eng,summary_eng,transdetail_eng,ataglance_esp,summary_esp,transdetail_esp)
VALUES (:rank_no,:yes_code,:eng_label,:esp_label,:ataglance_eng,:summary_eng,:transdetail_eng,:ataglance_esp,:summary_esp,:transdetail_esp);
END LOADING;
LOGOFF;
.QUIT;
0004 DROP TABLE PROTO_PSD1.mi_yes16_cat_logo_err;
**** 11:36:55 RDBMS error 3807: Object
'PROTO_PSD1.mi_yes16_cat_logo_err' does not exist.
0005 .SET RECORD VARTEXT "|";
**** 11:36:55 Now set to read 'Variable-Length Text' records
**** 11:36:55 Delimiter character(s) is set to '|'
**** 11:36:55 Command completed successfully
0006 DEFINE
rank_no (varchar(10)),
yes_code (varchar(2)),
eng_label (varchar(100)),
esp_label (varchar(100)),
ataglance_eng (varchar(6)),
summary_eng (varchar(6)),
transdetail_eng (varchar(6)),
ataglance_esp (varchar(6)),
summary_esp (varchar(6)),
transdetail_esp (varchar(6))
FILE=/users/work223/staging/222_yes/yes_req_2016_tera/matrix/data/cat_logo
.txt;
**** 11:36:55 FDL4803 DEFINE statement processed
0007 SHOW;
FILE = /users/work223/staging/222_yes/yes_req_2016_tera/matrix/data/cat_logo.txt
RANK_NO OFFSET = 0 LEN = 10 VARCHAR
YES_CODE OFFSET = 12 LEN = 2 VARCHAR
ENG_LABEL OFFSET = 16 LEN = 100 VARCHAR
ESP_LABEL OFFSET = 118 LEN = 100 VARCHAR
ATAGLANCE_ENG OFFSET = 220 LEN = 6 VARCHAR
SUMMARY_ENG OFFSET = 228 LEN = 6 VARCHAR
TRANSDETAIL_ENG OFFSET = 236 LEN = 6 VARCHAR
ATAGLANCE_ESP OFFSET = 244 LEN = 6 VARCHAR
SUMMARY_ESP OFFSET = 252 LEN = 6 VARCHAR
TRANSDETAIL_ESP OFFSET = 260 LEN = 6 VARCHAR
TOTAL RECORD LENGTH = 268
0008 BEGIN LOADING PROTO_PSD1.mi_yes16_cat_logo ERRORFILES PROTO_PSD1.mi_yes16_c
at_logo_err
INSERT INTO PROTO_PSD1.mi_yes16_cat_logo (rank_no,yes_code,eng_label,esp_l
abel,ataglance_eng,summary_eng,transdetail_eng,ataglance_esp,summary_esp,t
ransdetail_esp)
VALUES (:rank_no,:yes_code,:eng_label,:esp_label,:ataglance_eng,:summary_e
ng,:transdetail_eng,:ataglance_esp,:summary_esp,:transdetail_esp);
**** 11:36:55 Invalid BEGIN LOADING statement
**** 11:36:55 Expected CHECKPOINT or INDICATORS, found: INTO
===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================
**** 11:36:55 Logging off all sessions
**** 11:36:55 Total processor time used = '0.11 Seconds'
. Start : Wed May 17 11:36:54 2017
. End : Wed May 17 11:36:55 2017
. Highest return code encountered = '8'.
**** 11:36:55 FDL4818 FastLoad Terminated
I think the BEGIN LOADING is missing the semi-colon at the end. It also only lists one error table name; you should put 2 names (one for the ET table and one for the UV table).
Steve - thanks, that worked. Still getting an error, not sure if this is a coding or data issue?
Here is the recs from file:
1,SU,Utilities,Servicios pzblicos,ICON51,ICON61,ICTL19,ICON51,ICON61,ICTLSS
2,SH,Health Care,Atencisn de la Salud,ICON52,ICON62,ICTL17,ICON52,ICON62,ICTLSQ
3,DR,Pharmacy,Farmacia,ICON52,ICON62,ICTL06,ICON52,ICON62,ICTLSF
ERROR:
0009 BEGIN LOADING PROTO_PSD1.mi_yes16_cat_logo ERRORFILES PROTO_PSD1.mi_yes16_c
at_logo_err1,PROTO_PSD1.mi_yes16_cat_logo_err2;
**** 12:11:59 Session count 25 returned by the DBS overrides
user-requested session count
**** 12:12:01 Number of FastLoad sessions requested = 25
**** 12:12:01 Number of FastLoad sessions connected = 25
**** 12:12:01 FDL4808 LOGON successful
**** 12:12:03 RDBMS error 2635: Error tables are invalid OR
mi_yes16_cat_logo is in an invalid state for Load -
===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================
**** 12:12:03 Logging off all sessions
**** 12:12:03 Total processor time used = '1.43 Seconds'
. Start : Wed May 17 12:11:56 2017
. End : Wed May 17 12:12:03 2017
. Highest return code encountered = '12'.
**** 12:12:03 FDL4818 FastLoad Terminated
Since you want to start over, make sure you drop all of the tables (target table and error tables), recreate the target table, and start over again.
BTW, if this is something new, you should be switching over to using TPT (Teradata Parallel Transporter).
Also you may want to try just putting
SET RECORD VARTEXT;
You don't have to put the "|" in there because it defaults to "|".