OS: WIN Database target: Oracle 12c Source is File. Using : sqlldr
I have a text data in this format. I have pasted the sample data in the description (please copy it to notepad++) i have huge data in this way which i would like to load it into the database table using sqlldr.
let me give you a brief of data TRN is where the transaction starts and TRNEND is where it ends.
The problem is- TRN1111111119134 record line where we have one column missing but that is present in transaction TRN1111111117134 column which is 114115, but my requirement here is to insert null into that column corresponding down to 114115 in first transaction when dealing with the second transaction but everything here is separated by white space(can we convert the white space delimiter to fixed one or other delimited one dynamically ?). Any advice would really be helpful. Thanks a lot.
The table definition for this case
CREATE TABLE "R_F" (
"COL1" VARCHAR2(4000 BYTE),
"COL2" VARCHAR2(4000 BYTE),
"COL3" VARCHAR2(4000 BYTE),
"COL4" VARCHAR2(4000 BYTE),
"COL5" VARCHAR2(4000 BYTE),
"COL6" VARCHAR2(4000 BYTE),
"COL7" VARCHAR2(4000 BYTE),
"COL8" VARCHAR2(4000 BYTE),
"COL9" VARCHAR2(4000 BYTE),
"COL10" VARCHAR2(4000 BYTE),
"COL11" VARCHAR2(4000 BYTE),
"COL12" VARCHAR2(4000 BYTE),
"COL13" VARCHAR2(4000 BYTE),
"COL14" VARCHAR2(4000 BYTE),
"COL15" VARCHAR2(4000 BYTE),
"COL16" VARCHAR2(4000 BYTE),
"COL17" VARCHAR2(4000 BYTE),
"COL18" VARCHAR2(4000 BYTE),
"COL19" VARCHAR2(4000 BYTE),
"COL20" VARCHAR2(4000 BYTE),
"COL21" VARCHAR2(4000 BYTE),
"COL22" VARCHAR2(4000 BYTE),
"COL23" VARCHAR2(4000 BYTE),
"COL24" VARCHAR2(4000 BYTE),
"COL25" VARCHAR2(4000 BYTE),
"COL26" VARCHAR2(4000 BYTE),
"COL27" VARCHAR2(4000 BYTE),
"COL28" VARCHAR2(4000 BYTE),
"COL29" VARCHAR2(4000 BYTE),
"COL30" VARCHAR2(4000 BYTE))
SamplpeData:-
https://ufile.io/td0ty
I am trying to use below sqlldr control file command, do i missing something here ?
control Code :
infile 'D:\Source_files\LOG_07117_2017.DAT'
truncate into table R_F
fields terminated by WHITESPACE optionally enclosed by '#'
TRAILING NULLCOLS
( col1 ,
col2 "nvl(:col2,'')",
col3 "nvl(:col3,'')",
col4 "nvl(:col4,'')",
col5 "nvl(:col5,'')",
col6 "nvl(:col6,'')",
col7 "nvl(:col7,'')",
col8 "nvl(:col8,'')",
col9 "nvl(:col9,'')",
col10 "nvl(:col10,'')",
col11 "nvl(:col11,'')",
col12 "nvl(:col12,'')",
col13 "nvl(:col13,'')",
col14 "nvl(:col14,'')",
col15 "nvl(:col15,'')",
col16 "nvl(:col16,'')",
col17 "nvl(:col17,'')",
col18 "nvl(:col18,'')",
col19 "nvl(:col19,'')",
col20 "nvl(:col20,'')")
Message was edited by: 3408387