Excel Spreadsheet to Oracle Table

How do I import data from an excel spreadsheet into an Oracle table?
olaikpuAsked:
Who is Participating?
 
q2eddieConnect With a Mentor Commented:
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
 
J_LanceCommented:
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
 
J_LanceCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
amp072397Commented:
olaikpu: Please provide feedback.

amp
Community Support Moderator
Experts Exchange
0
 
costelloCommented:
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
 
q2eddieCommented:
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
 
J_LanceCommented:
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
 
q2eddieCommented:
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
 
olaikpuAuthor Commented:
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
 
ComTechCommented:
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
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.

All Courses

From novice to tech pro — start learning today.