• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

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.
  • 5
  • 4
1 Solution
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.

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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:

http://technet.oracle.com/docs/products/oracle8i/doc_index.htm and choose Oracle8i Server and JServer Documentation sets, Release 8.1.5, then Oracle8I Utilities, Chapters 3 to 7.

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


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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now