Import Excel file into oracle db


How to import Excel file (table) into oracle (9i) database (otherthan SQL loader)?

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.

Helena Markováprogrammer-analystCommented:
I have found this Note:272138.1 - Transfering Data from Excel into Oracle using Data Transformation Services and Oracle ODBC Driver - you can try it.

The information in this article applies to:
Oracle ODBC Driver - Version: 9.2
This problem can occur on any platform.

To transfer data from an Excel spreadsheet into the Oracle database using the Oracle ODBC Driver and Data Transformation Services.

1. Open SQL Server Enterprise Manager

2. Click on the + to expand the tree for the database server.
You will need to expand it three times.
Note: If the SQL Server does not appear here, you will need to right click and go through the Wizard for SQL Server Registration.
- The first level of the tree is called Microsoft Sql Server,The second level of the tree is called SQL Server Group,and the third level of the tree is your SQL Server Database.
For these instructions we will call our SQL Server Database, 'local'.

3. Under the local Database, click on the + to expand the tree control for 'Data Transformation Services'

4. Under Data Transformation Services, right click and select "New Package"

5. A DTS Package screen will appear. Drop a 'Other Connection' onto the workspace.
a. Select the Oracle in ODBC Driver from the Data Source list.
b. Select your DSN in the User/System DSN pull down list.
c. In the Username field type in the userid.
d. In the Password filed type in the password.
e. Click on the OK button.

6. In the DTS Package screen. Drop an Excel 8.0 Connection onto the workspace.
a. In the File Name click on the ... button and navigate to an excel file. Click Open to open the file.Then click on the OK button in the Connection Properties screen.

7.In the DTS Package screen, create a Transform between the Other Connection and Excel by clicking on the Excel icon, hold down the control key and click on the Other Connection icon. Right click and select Transform Data Task.

8. Right-click on the Transform Data Task Link and click properties. Select the table you are trying to insert the excel data into. Then click on the transformations tab and make sure that the data is being mapped correctly to each column. Once that is correct click on the OK button.

9. Now do a right click on the Transform Data Task icon (it's the arrow) and click on Execute. This will now take the data from the Excel spreadsheet and insert it into the Oracle database.
Using Access

Open a blank or an existing Access Database
 Go to file/ Get external Data / Import
In the "Files of type:" drop down list, choose Microsoft Excel, find your file and click "Import", follow the Wizard clicking next, and click finish to import the Excel's table into Access.

In Windows go to control Panel/ Administrative Tools / Data Sources ODBC / System DSN tab
click add
Scroll down if necessary to select the Oracle Driver installed in your machine
click OK, it will take you to Oracle ODBC Driver Configuration dialog box
    In Data Source Name: type the database name
    Description: Up to you
    TNS Service Name: Select the database you wish to access from the drop down list
    User ID: Your user ID
    On your right, click button "Test Connection" to make sure you can connect.
    Click OK, you're done here.

Now, go to your Access database
  Go to file/ Get external Data / Link Table
  In the Link window "bottom left corner" select ODBC databases from "Files of Type:" drop down list
  In "Select Data Source" window select the tab Machine Data Source and then select the data source name you created,
  click OK, the dialog box for connecting to the database will appear, enter your password, click OK, then you'll see a list of tables in the database, select the one you wish to update or if you are selecting more than one, just click on the name -you don't need to use Shift - , click OK and you have linked the Oracle table to Access.
Go to Queries and create a new query using your local Access Table ( the one you imported from Excel ), make it an Append query using the "Query Type" button. When the Append dialog box is displayed, select the ORACLE table from the drop down list "Table Name", click OK
Now, in the datasheet view drag the columns you wish to append from your local table to the empty fields right below, then update "Append to:  " to go with your ORACLE table's corresponding fields
Click the run " ! " button, enter your password and your're done.
You may need to update the ODBC timeout if running for too long: Go to View / Properties and change the value of ODBC timeout to 3600.

Hope this helps!
You can use sqlplus worksheet.!
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sorry about the above post it was a mistake.
yr_kAuthor Commented:
Hi Henka,

thanks for steps through SQL Server.
However, I dont have SQL Server. is there any way with Oracle tools or Toad


Here is a vb script that I wrote sometime back to get oracle data into excell.
You can modify the same to update the data back (If u are conversant with vb this would be very easy).

you need to install odbc and get it working before u can use this.

Sub OpenDb()
    Dim wrkODBC As Workspace
    Dim dbsOracle As Database
    Dim strpubmst As String
    Dim rstYourTable As Recordset
    On Error GoTo close_connection

    Set wrkODBC = CreateWorkspace("ODBCWorkspace", "system", "", dbUseODBC)
    Set dbsOracle = wrkODBC.OpenConnection("YOUR_DATABASE_SID", dbDriverComplete, True, "ODBC;DSN=;UID=YOUR_USER_ID;PWD=YOUR_PASSWORD;")
    strpubmst = "Select * from YOUR_USER.YOUR_TABLE "

    ' Set rstYourTable = dbsOracle.OpenRecordset(strpubmst, dbOpenDynaset, dbSQLPassThrough, dbOptimistic)
    Set rstYourTable = dbsOracle.OpenRecordset(strpubmst, dbOpenDynaset)
    MsgBox "sucess " & rstYourTable.RecordCount
    With rstYourTable
        rw = 1
        cl = 1
        Do While Not rstYourTable.EOF
            Cells(rw, cl).Value     = !t1_code
            Cells(rw, cl + 1).Value = !t1_name
            Cells(rw, cl + 2).Value = !t1_short_name
            Cells(rw, cl + 3).Value = !t1_type
            Cells(rw, cl + 4).Value = !t1_no_prts
            rw = rw + 1
    End With
End Sub

Hope this helps.


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
Helena Markováprogrammer-analystCommented:
I don't know Toad. In Oracle there is SQLloader there ...
In TOAD click on Import/ Table Data

yr_kAuthor Commented:
thanks for all

I would like to accept both makhan & Henka
download the sql developer, from, it's free and it has an option to import data

not work with unicode data
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.

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.