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

INTO table  boolean_table
fields terminated by X'09'
  (      "ID",

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.
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:

That file extension is a native Excel format.  Looks like you are expecting a tab delimited file.  Are you sure you input file has bee saved as a tab delimited file?

If so, can you upload a couple of records from that file?  Make sure you scrub the data before you upload it.
dojjolAuthor Commented:
its not tab delimited file, its just the standard excel file.

Being newbie to sqlloader, not sure can we load the excel file.

Please let me know.

slightwv (䄆 Netminder) Commented:
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.
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Wasim Akram ShaikConnect With a Mentor Commented:
Author as slightwv suggested, you need to have a delimiter file(with an ASCII text)

There is a thread/extract in oracle forums(link given below) which say that...

Oracle has no ability to directly "read" a file that is in "native" Excel format.

For Oracle to ingest the external data, it needs to be in a form that Oracle can decipher; specifically ASCII text.

These links gave alternatives of loading the xlsx data to oracle table, have a look at below links for complete details...

awking00Connect With a Mentor Commented:
Simplest way is to just to save your excel file as a comma delimited file by selecting Save As and in that screen choose under the Save As type: CSV (Comma Delimited) (*.csv) and hit save. You just then need to modify your control file to show the filename you gave the .csv file and change the fields terminated by to ','.
slightwv (䄆 Netminder) Commented:
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 '"'
Wasim Akram ShaikCommented:
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..!!

slightwv (䄆 Netminder) Commented:

There is no need to repost information you previously posted.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.