Import Excel file into oracle db

Posted on 2004-11-30
Last Modified: 2011-08-18

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

Question by:yr_k
    LVL 22

    Expert Comment

    by:Helena Marková
    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.
    LVL 23

    Expert Comment

    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!
    LVL 6

    Expert Comment

    You can use sqlplus worksheet.!
    LVL 6

    Expert Comment

    Sorry about the above post it was a mistake.

    Author Comment

    Hi Henka,

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

    LVL 6

    Accepted Solution


    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.

    LVL 22

    Expert Comment

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

    Expert Comment

    In TOAD click on Import/ Table Data


    Author Comment

    thanks for all

    I would like to accept both makhan & Henka
    LVL 1

    Expert Comment

    download the sql developer, from, it's free and it has an option to import data


    Expert Comment

    not work with unicode data

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    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…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    745 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

    14 Experts available now in Live!

    Get 1:1 Help Now