?
Solved

Import Excel file into oracle db

Posted on 2004-11-30
11
Medium Priority
?
35,103 Views
Last Modified: 2011-08-18
Hi,

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

Thanks
0
Comment
Question by:yr_k
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12714968
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.

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

Fix
Instructions:
==========
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.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 12715473
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!
0
 
LVL 6

Expert Comment

by:makhan
ID: 12724304
You can use sqlplus worksheet.!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Expert Comment

by:makhan
ID: 12724328
Sorry about the above post it was a mistake.
0
 

Author Comment

by:yr_k
ID: 12733365
Hi Henka,

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

Thanks
0
 
LVL 6

Accepted Solution

by:
makhan earned 300 total points
ID: 12733510
Hi!

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
        .MoveFirst
        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
            .MoveNext
        Loop
    End With
   
close_connection:
    dbsOracle.Close
    wrkODBC.Close
   
End Sub

=================================================================
Hope this helps.

0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12733583
I don't know Toad. In Oracle there is SQLloader there ...
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 12796823
In TOAD click on Import/ Table Data

 
0
 

Author Comment

by:yr_k
ID: 12848172
thanks for all

I would like to accept both makhan & Henka
0
 
LVL 1

Expert Comment

by:chazs
ID: 22079411
download the sql developer, from http://www.oracle.com, it's free and it has an option to import data

regards
0
 

Expert Comment

by:FISSOFT
ID: 32693880
not work with unicode data
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month14 days, 4 hours left to enroll

809 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