Solved

sqlloader load file

Posted on 2012-04-02
8
1,770 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:dojjol
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
ID: 37798082
>>'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
 

Author Comment

by:dojjol
ID: 37798102
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37798125
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 167 total points
ID: 37798167
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 166 total points
ID: 37802023
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37802198
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37803010
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37803028
wasimibm,

There is no need to repost information you previously posted.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question