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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wasim Akram ShaikCommented:
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
awking00Information Technology SpecialistCommented:
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..!!

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

There is no need to repost information you previously posted.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.