Solved

Excel Spreadsheet to Oracle Table

Posted on 2001-06-06
10
455 Views
Last Modified: 2007-11-27
How do I import data from an excel spreadsheet into an Oracle table?
0
Comment
Question by:olaikpu
10 Comments
 
LVL 7

Accepted Solution

by:
q2eddie earned 25 total points
ID: 6160553
Hi,

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.

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

http://technet.oracle.com/docs/tech/migration/workbench/doc_library/relnotes.htm#775923

Bye. -e2
0
 

Expert Comment

by:J_Lance
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.

-Jer
0
 

Expert Comment

by:J_Lance
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
LOAD DATA
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)'
APPEND
INTO TABLE tablename
FIELDS TERMINATED BY ","
(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.
0
 

Expert Comment

by:amp072397
ID: 6242735
olaikpu: Please provide feedback.

amp
Community Support Moderator
Experts Exchange
0
 

Expert Comment

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

costello
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 7

Expert Comment

by:q2eddie
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
0
 

Expert Comment

by:J_Lance
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.

-Jer
0
 
LVL 7

Expert Comment

by:q2eddie
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
0
 

Author Comment

by:olaikpu
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
0
 

Expert Comment

by:ComTech
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

q2eddie
J_Lance

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

Thanks to all and Happy Holidays,

ComTech
Community Supprt
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

How many times recently have you prepared a presentation or emailed a document to a client and you have found that they have older versions of MS Office and they can not open the file you have prepared.  Although most visitors to this site are exper…
Outlook Free & Paid Tools
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

19 Experts available now in Live!

Get 1:1 Help Now