I am not sure about this. Could you share a test perl script, ddl and mload script?
My first guess would be that all lines need to end with a \n.
But your last line doesn't.
So might be worth to write always "line\n"
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
Hi Ulrich,
yes, that is why I am writing the first line as "line" and then all the lines as "\nline".In my file there is no new line after last line.No special character.
Its a big problem for me.I got this issue at very wrong time. Please help.
Thanks.
can you share the mload log?
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
In my file there are 6 lines.
MLOAD log is saying:
considered recoreds: 7
satisfied: 6
out of range "first field" in 7th record.
I can post the exact log in some time. till then, i hope this may give you some idea.
Thanks.
can you share the perl code as well?
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
And pease just try once to add a \n at the last line as well...
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
Please find the MLOAD log:
**** 20:20:31 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 20:20:31 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 20:20:31 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 20:20:32 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 20:20:33 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT DTZTAW.LOGTABLE_CMP_PARM_MLOAD (Logtype,
Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,
MiscInt1,MiscInt2,MiscInt3,MiscInt4,
MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,MLoadCkpt) VALUES (110, 1,
0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, :Ckpt);
**** 20:20:33 UTY4203 Attempted to access out of range input data in field
'IN_TACTIC_ID', file '/work/scripts/cmpbypass/temp/finerec.txt',record
number '7'.
**** 20:20:33 UTY1803 Import processing statistics
. IMPORT 1 Total thus far
. ========= ==============
Candidate records considered:........ 7....... 7
Apply conditions satisfied:.......... 6....... 6
Candidate records not applied:....... 0....... 0
Candidate records rejected:.......... 0....... 0
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 20:20:40 UTY6212 A successful disconnect was made from the RDBMS.
**** 20:20:40 UTY2410 Total processor time used = '0.398518 Seconds'
. Start : 20:20:20 - TUE JUL 24, 2012
. End : 20:20:40 - TUE JUL 24, 2012
. Highest return code encountered = '12'.
If I will use line\n for the last line then there willl be an empty line at the end which will create issue. that is why I kept it as /nline.
"Attempted to access out of range input data in field 'IN_TACTIC_ID'"
Is IN_TACTIC_ID the first column in the file?
Do you have unicode chars in your file? or additional \n within the fields?
So, without sharing the DDL, the file and mload script it remains a lot of guessing.
And:
did you ever try it with a \n at the end of each line?
I attached a very simple test.
test1 loads a file with 6 rows - each has a \n at the end.
test1 succeed.
test2 loads a file with 6 rows - the last one has no \n at the end
test2 is loading only 5 rows.
check the logs...
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
How would I know that this is the last record?so that I can write it as "line" in place of line\n?
The MLOAD script is:
.LOGTABLE logtable;
.RUN FILE /work/user/user1;
DROP TABLE tab_WT;
DROP TABLE tab_ET;
DROP TABLE tab_UV;
.BEGIN MLOAD
TABLES tab WORKTABLES tab_WT
ERRORTABLES tab_ET
tab_UV
;
.LAYOUT INPUTLAYOUT;
.FIELD IN_STRTGY_SRC_CD 1 CHAR(2) ;
.FIELD IN_TACTIC_ID 3 CHAR(50) ;
.FIELD IN_TRGT_TYP_CD 53 CHAR(3) NULLIF IN_TRGT_TYP_CD=' ';
.FIELD IN_EVNT_ACTVY_TYP_CD 56 CHAR(2) NULLIF IN_EVNT_ACTVY_TYP_CD=' ';
.FIELD IN_EVNT_ACTVY_TYP_CD_VRSN 58 CHAR(2) NULLIF IN_EVNT_ACTVY_TYP_CD_VRSN=' ';
.FIELD IN_ENCODING_IND 60 CHAR(1) NULLIF IN_ENCODING_IND=' ';
.FIELD IN_FIELD_DESC 61 CHAR(17) NULLIF IN_FIELD_DESC=' ';
.FIELD IN_FILENAME 78 CHAR(50) NULLIF IN_FILENAME=' ';
.FIELD IN_FILENAME_SUFFIX 128 CHAR(8) ;
.FIELD IN_VNDR_TRANSMISSION_CD 136 CHAR(2) ;
.FIELD IN_EMAIL_NOTIF_ADDR 138 CHAR(30) ;
.FIELD IN_PROCESS_TYP 168 CHAR(1) ;
.FIELD IN_USRID 169 CHAR(8) ;
.FIELD IN_FILLER 177 CHAR(1) NULLIF IN_FILLER=' ';
.FIELD IN_ATTEMPT 178 CHAR(2) NULLIF IN_ATTEMPT=' ';
.FIELD IN_ERRREC_IND 180 CHAR(1) NULLIF IN_ERRREC_IND=' ';
.FIELD IN_TIMESTAMP 181 CHAR(26) NULLIF IN_TIMESTAMP=' ';
.DML LABEL INSERT_FINEREC;
INSERT INTO tab (
STRTGY_SRC_CD
,TACTIC_ID
,TRGT_TYP_CD
,EVNT_ACTVY_TYP_CD
,EVNT_ACTVY_TYP_CD_VRSN
,ENCODING_IND
,FIELD_DESC
,FILENAME
,FILENAME_SUFFIX
,VNDR_TRANSMISSION_CD
,EMAIL_NOTIF_ADDR
,PROCESS_TYP
,USRID
,CNTRL_ID
,EMAIL_IND
,ITERATION_ERR_REC
,VALIDATION_ERR_REC
,ATTEMPT_NO
,LOAD_TIMESTAMP
,CNTRL_DTL_MOVED_IND
)
VALUES (
:IN_STRTGY_SRC_CD,
:IN_TACTIC_ID,
:IN_TRGT_TYP_CD,
:IN_EVNT_ACTVY_TYP_CD,
:IN_EVNT_ACTVY_TYP_CD_VRSN,
:IN_ENCODING_IND,
:IN_FIELD_DESC,
:IN_FILENAME,
:IN_FILENAME_SUFFIX,
:IN_VNDR_TRANSMISSION_CD,
:IN_EMAIL_NOTIF_ADDR,
:IN_PROCESS_TYP,
:IN_USRID,
NULL,
NULL,
NULL,
:IN_ERRREC_IND,
:IN_ATTEMPT,
:IN_TIMESTAMP,
NULL
);
.IMPORT INFILE /work/scripts/cmpbypass/temp/finerec1.txt
FORMAT UNFORMAT
LAYOUT INPUTLAYOUT
APPLY INSERT_FINEREC;
.END MLOAD;
.LOGOFF;
unformat is likely to create the issue.
check
http://forums.teradata.com/forum/tools/mload-vartext-format
Freds last statement
"UNFORMAT expects no record delimiter; if there is one you can define it as FILLER so it is ignored."
So you can either add a filler with char(1) - at the end or you switch to TEXT
And don't forget to have a \n IN EACH ROW!
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
I have used FORMAT TEXT.
The error I am getting is:
**** 10:32:59 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 10:32:59 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 10:32:59 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 10:33:00 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 10:33:01 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT DTZTAW.LOGTABLE_CMP_PARM_MLOAD (Logtype,
Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,
MiscInt1,MiscInt2,MiscInt3,MiscInt4,
MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,MLoadCkpt) VALUES (110, 1,
0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, :Ckpt);
**** 10:33:01 UTY4015 Access module error '35' received during 'read' operation
on record number '5': 'EOF encountered before end of record'
**** 10:33:01 UTY1803 Import processing statistics
. IMPORT 1 Total thus far
. ========= ==============
Candidate records considered:........ 5....... 5
Apply conditions satisfied:.......... 5....... 5
Candidate records not applied:....... 0....... 0
Candidate records rejected:.......... 0....... 0
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 10:33:07 UTY6212 A successful disconnect was made from the RDBMS.
**** 10:33:07 UTY2410 Total processor time used = '0.399718 Seconds'
. Start : 10:32:47 - WED JUL 25, 2012
. End : 10:33:07 - WED JUL 25, 2012
. Highest return code encountered = '12'.
And If I will use UNFORMAT, then what size should I give for FILLER at the end?
Option 1:
remove the \n from all rows and use UNFORMAT
Option 2:
add the \n to ALL rows - in your last LOG you can see the impact of Once missing \n - and use TEXT
Option 3:
add the \n to ALL rows - in your last LOG you can see the impact of Once missing \n - and use UNFORMAT and add a Fille CHAR(1) at the end of the layout definition.
In all three options you need to change your load file!
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
Thanks a lot to you Ulrich..its working now. I used \n with each record and then FORMAT TEXT.
One more query i have :-).
I have few fields defined as Integer or SmallInt. If there is Alphabet for these fields in File then how can I handle that.Is there anything like NULLIF field = " ".
Thanks again.
Hi All,
I am creating a text file using perl script and then using that txt file for MLOAD.
While writing the first record, I am writing the line as it is. Aftre that I am writing as"\nline" (so that the new record will start from new line).
During MLOAD, it is assuming one record extra and giving error that Field not found.
I am not finding any special character (new line,EOF etc) at the end of the records.
Can you please let me know how to handle this situation.
Thanks,
Terankit