dojjol
asked on
sqlloader load file
Hi ,
I have created a loader file to load the records in the table
Table structure is
create table boolean_table(
id number(10,0), val varchar(4000));
and sql control file
LOAD DATA
INFILE 'BOOLEAN_RECORD.xlsx'
replace
INTO table boolean_table
fields terminated by X'09'
TRAILING NULLCOLS
( "ID",
"VAL"
)
It fails to load data with bad file, which is in unreadable format. and log file saying
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table boolean_table, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
-------------------------- ---- ---------- ----- ---- ---- ---------------------
"ID" FIRST * WHT CHARACTER
"VAL" NEXT * WHT CHARACTER
Record 1: Rejected - Error on table boolean_table, column "ID".
Field in data file exceeds maximum length
Record 3: Rejected - Error on table boolean_table, column "ID".
Multibyte character error.
I have created a loader file to load the records in the table
Table structure is
create table boolean_table(
id number(10,0), val varchar(4000));
and sql control file
LOAD DATA
INFILE 'BOOLEAN_RECORD.xlsx'
replace
INTO table boolean_table
fields terminated by X'09'
TRAILING NULLCOLS
( "ID",
"VAL"
)
It fails to load data with bad file, which is in unreadable format. and log file saying
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table boolean_table, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
--------------------------
"ID" FIRST * WHT CHARACTER
"VAL" NEXT * WHT CHARACTER
Record 1: Rejected - Error on table boolean_table, column "ID".
Field in data file exceeds maximum length
Record 3: Rejected - Error on table boolean_table, column "ID".
Multibyte character error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sql loader can only deal with text files. You need to save the Excel file as some text version.
Whatever you use as a delimiter is specific in the control file: fields terminated by X'09'
In this case a Hex 09 which is a TAB.
An alternative if you want to keep it as Excel is an Excel marco (VBA) or VBScript or Powershell program. This is a lot more complex but it is an alternative.
Whatever you use as a delimiter is specific in the control file: fields terminated by X'09'
In this case a Hex 09 which is a TAB.
An alternative if you want to keep it as Excel is an Excel marco (VBA) or VBScript or Powershell program. This is a lot more complex but it is an alternative.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to make sure that a comma is not in any of the values. If that is possible, you need to take extra measures, like add double quotes then in sql loader use: optionally enclosed by '"'
the thread links which i had posted over had one of such suggestions(saving the excel file as csv) in addition to that it has some other recommendations too..!!
https://forums.oracle.com/forums/thread.jspa?threadID=1084258
https://forums.oracle.com/forums/thread.jspa?threadID=305918&tstart=0
https://forums.oracle.com/forums/thread.jspa?threadID=1084258
https://forums.oracle.com/forums/thread.jspa?threadID=305918&tstart=0
wasimibm,
There is no need to repost information you previously posted.
There is no need to repost information you previously posted.
ASKER
Being newbie to sqlloader, not sure can we load the excel file.
Please let me know.
Thanks,
Ankur