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

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.
0
 
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.

Thanks,
Ankur
0
 
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.
0
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...


https://forums.oracle.com/forums/thread.jspa?threadID=1084258
https://forums.oracle.com/forums/thread.jspa?threadID=305918&tstart=0
0
 
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 ','.
0
 
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 '"'
0
 
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..!!

https://forums.oracle.com/forums/thread.jspa?threadID=1084258
https://forums.oracle.com/forums/thread.jspa?threadID=305918&tstart=0
0
 
slightwv (䄆 Netminder) Commented:
wasimibm,

There is no need to repost information you previously posted.
0
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.