How to Import An Excel, Access, etc. Using SQL+??


I've created example tables using SQL+ (Oracle8i) and I wish
to populate them with data created by my classmate. She's put
this data into an Excel 97 file (.XLS).

Is there an IMPORT reserved/key word in SQL+ which would allow this task to be done? Does Oracle call "importing" some thing else (getFile?) ?

SQL>select * INTO databaseFile From dataFile.xls ;    ????

I have a TRIAL version of Oracle and I am unable to find any
worthwhile documentation on ANY of the 17 CDs which Oracle
sent to me.
Who is Participating?
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.

SQL*Plus can not be used to upload data to the database.  You might want to use sql loader for that. There is some work involved in it. Create text file from excel use it as data file for loader. Check the sql*loader documentation on the CD.  If you have MS Access then make a access database from excel which can be done easily.  Oracle provides MS access database porting tool. You can install that tool from oracle server CD. This tool is very easy to use just follow the instructions on the screen. Have I answered your question?
jnowlinAuthor Commented:
Hello manand,

No, I'm still having difficulty with much of this stuff, especially finding all of this documentation many experts here a E.E. have told me exists on the CDs. I ordered Personal Oracle as a trial and I received 17, count 'em, 17 CDs. I've gone through most of them and they all do a great job on installation and the latest release notes.

After that, the documentation falls off rapidly in the usefullness department.

I will try to "search the world over" again. But, this time, I'll focus on your suggestion of the MS Access porting
tool on the Oracle Server CD. Hopefully, yours truly will find it!

jnowlinAuthor Commented:
I have 3 Oracle Application Server CDs and 1 Oracle Developer Server CD. I'll begin with that one.

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.

When you installed Personal Oracle, you would have installed on-lime HTML documentation, either on your hard drive or at least to access it from a CD.  Go to your Oracle program group (Oracle for Windows 95 or NT) and choose Oracle8 Documentation.  It should open your Web browser - if you did not install the doc on your hard drive, then you'll get an error if you don't have the right CD in your drive.  The proper CD would be the Oracle Server CD.  After selecting Text or Java version, choose Personal Oracle8, then Server Utilities.  Under the Preface, there should be a link for Part II: SQL*Loader.

Hope this helps, let me know.  The Oracle doc is actually quite good once you get familiar with how to use it.
jnowlinAuthor Commented:
OK bkowalski, I believe you! I'm just incredibly frustrated with Oracle in general, especially their customer service.

I installed Oracle Developer Server version 6.0 yesterday. Is that what you refer to above? It has much more docs than the Quick Suite (Enterprise) I installed originally.

I'm in a Client/Server grad certificate
program. Although, I've planted myself at my PC for most of the last 5 years, C, C++, Data Structures, UNIX,etc., this is my first exposure to Oracle and SQL+. I *know* there are ways of working with files in C and C++ and UNIX and so on. So, my brain *knows* what's going on. I just need to learn how to do it in the Oracle world.


I would think the doc would be on almost any CD.  But maybe you could try a custom install and choose only the documentation.  You'll need about 75MB of disk space.  

Let me know if I can help once you get started with SQL*Loader.

jnowlinAuthor Commented:
I need a bit more clarification, so I've increased the points. Oracle is pretty confusing for me still. It's probably just me.

Here's a simple example of how to get started with SQL*Loader.  Then use the documentation to expand this example to suit your needs.

Take the dept table in the scott/tiger schema.  (You can create the scott schema by running %ORACLE_HOME\dbs\demobld.sql as scott.)  It is defined as

SQL> desc dept
 Name                            Null?    Type
 ------------------------------- -------- ----
 DEPTNO                          NOT NULL NUMBER(2)
 DNAME                                    VARCHAR2(14)
 LOC                                      VARCHAR2(13)

I want to add rows to this table and let's say I have the data in an Excel spreadsheet saved as a CSV file called dept.csv:

60,Sales,Los Angeles

The table already has some rows in it:

SQL> select * from dept;

   DEPTNO DNAME          LOC
--------- -------------- -------------
       20 RESEARCH       DALLAS
       30 SALES          CHICAGO

So now I need to create a control file for SQL*Loader, call it dept.ctl:

INFILE 'c:\orawin95\rdbms80\loader\DEPT.CSV'

And then I need to invoke SQL*Loader and specify the control file to use.  From a command line (on Windows, the call is slightly different for Unix):

sqlldr80 userid=scott/tiger control=c:\orawin95\rdbms80\loader\dept.ctl

Here's what happens:

SQL*Loader: Release Production on Tue Nov 30 23:20:23 1999

© Copyright 1997 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4

And here's the new data in the table:

SQL> select * from dept;

   DEPTNO DNAME          LOC
--------- -------------- -------------
       20 RESEARCH       DALLAS
       30 SALES          CHICAGO
       50 Accounting     Seattle
       60 Sales          Los Angeles
       70 Operations     Detroit
       80 Research       Miami

8 rows selected.

And here's the contents of dept.log:

SQL*Loader: Release - Production on Tue Nov 30 23:20:23 1999

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Control File:   c:\orawin95\rdbms80\loader\dept.ctl
Data File:      c:\orawin95\rdbms80\loader\DEPT.CSV
  Bad File:     c:\orawin95\rdbms80\loader\DEPT.bad
  Discard File:  none specified
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table DEPT, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER            
DNAME                                NEXT     *   ,       CHARACTER            
LOC                                  NEXT     *   ,       CHARACTER            

Table DEPT:
  4 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Space allocated for bind array:                  18688 bytes(64 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:             4
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Tue Nov 30 23:20:23 1999
Run ended on Tue Nov 30 23:20:28 1999

Elapsed time was:     00:00:04.49
CPU time was:         00:00:00.00    

I hope this helps you get started.  Check the Oracle doc under Server Utilities for SQL*Loader, or check out the following link: and choose Oracle8i Server and JServer Documentation sets, Release 8.1.5, then Oracle8I Utilities, Chapters 3 to 7.


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
jnowlinAuthor Commented:
Thanks Brad!

This is just the sort of starting place I've been looking for. Super!
Now, I'm off to ask my next Oracle question: NULLS: Where to have them, where NOT to have them.

I'm glad that helps.  I discovered a typo though.  The command line call should be

sqlldr80 userid=scott/tiger control=c:\orawin95\rdbms80\loader\dept.ctl

and not

sqlldr80 userid=scott/tiger control=c:\orawin95\rdbms80\loader\dept.ctl


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.