Excel Spreadsheet to Oracle Table

Posted on 2001-06-06
Medium Priority
Last Modified: 2007-11-27
How do I import data from an excel spreadsheet into an Oracle table?
Question by:olaikpu

Accepted Solution

q2eddie earned 100 total points
ID: 6160553

It appears that you should import your Excel spreadsheet into an Access database.  You can use SQL*Loader into import the Access (97 or 2000) database into Oracle.

1. "Oracle Migration Workbench Release Notes"
Release for Windows NT and Windows 95/98/2000
(Requires free signup)


Bye. -e2

Expert Comment

ID: 6165230
The easiest way to import from Oracle to Excel (97) is using the Pivot Table Wizard.

-Under "Data", select "Pivot Table Report..."
-Select "External Data Source" and click "Next>"
-Click "Get Data..."
-In the Databases tab, select the DSN of the database from which you would like to pull the data.
-Select the Tables/Columns you would like to include in your spreadsheet, then click "Next>". (the SELECT and FROM parts of a SQL query)
-Set up the filters you would like to use, then click "Next>". (the WHERE part of the SQL query)
-Choose how to sort your data, then click "Next>". (the ORDER BY part of the SQL query)

AT THIS POINT, you can either just click "Finish" and let the data appear in Excel, or you can select "View data or edit query in Microsoft Query", which will allow you to ensure you have the data you are looking for, view your query in SQL and make changes, and, in general, make sure this is what you want.  After you've either opted to "Return Data to Microsoft Excel" or "View data..."

-Click "Next>"
-Drag the column/row/data fields to where you want them to appear in the table (which data do you want to be rows, columns, and in the middle), then click "Next>"
-Finally, choose where to place the table and click "Finish"

Hope that this helps you out.


Expert Comment

ID: 6165261
Okay,. then I reread the question and saw that I answered the question backwards.  That having been said, here are some steps to do this the other way (Excel to Oracle 7.*)

First, create a .ctl file
-Open notepad
-Enter the following
INFILE 'place the path and name of the file that you will wish to load (i.e. c:\temp\loadfile.csv)'
BADFILE 'place the path and name of the file to which it can write errors (i.e. c:\temp\loadfile.bad)'
DISCARDFILE 'place the path and name of the file to which it can write discards (i.e. c:\temp\loadfile.dis)'
INTO TABLE tablename
(inside this, put the column names in the order they appear in excel, seperated by commas like: col1, col2, col3)

Next, save the Excel file as a .CSV with the name an path you used in INFILE
Finally, open Oracle Enterprise Manager and click on the Data Manager button from the tool bar
Select "Control File", click "Browse" and find your .ctl file
Choose your .ctl file, execute it with Data Manager, and the data will be loaded.

Hope *THIS* one helps.
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.


Expert Comment

ID: 6242735
olaikpu: Please provide feedback.

Community Support Moderator
Experts Exchange

Expert Comment

ID: 6414775
If no objections are made in the next 5 days, I will force accept the contribution by q2eddie.

Community Support Moderator @ Experts-Exchange

P.S. This post is made as part of the general effort to clean up this topic area, meaning, looping through all the open questions with no activity since 21 days.

Expert Comment

ID: 6414817
Hi, costello.

I appreciate your efforts here.

Both J_Lance's and my own suggestions seem reasonable.  If J_Lance returns, then I would recommend giving him the points.  If not, then he probably has abandoned this forum - and his two open questions.

Bye. -e2

Expert Comment

ID: 6414881
Although I haven't abandoned the forum, I would definately be in favor of the points goint to q2eddie.  My suggestion was placed in the event that q2eddie's didn't help, and as there's been no response, I can only assume his did.


Expert Comment

ID: 6414932
To J_Lance:

Cool!  You came back.  I guess the notifs from this Q and your two open ones hit you at about the same time.

Now, if only member olaikpu would...

Bye. -e2

Author Comment

ID: 6416804
Sorry have been away for a while. Well both answers are correct but I did not use your methods.  If you want to share the points then you are most welcome to. thanks for your input. Olaikpu

Expert Comment

ID: 6690997
olaikpu thank you for your kindness, and feeding the Exeperts.

This question will be split between two Experts.

I will reduce the 50 points to 25 and divide between


I will accept g2eddie here, and create a new auestion in theis Topic Area for Expert J-Lance.

Thanks to all and Happy Holidays,

Community Supprt

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

Are you looking to start a business? Do you own and operate a small company? If so, here are some courses you need to take before you hire a full-time IT staff.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

623 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