Solved

sqlloader load file

Posted on 2012-04-02
8
1,679 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
8 Comments
 
LVL 76

Accepted Solution

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

Expert Comment

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

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 167 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Assisted Solution

by:awking00
awking00 earned 166 total points
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
wasimibm,

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now