Trainers discussing SQL Server, Oracle and MySQL

There are many methods one can use to import data from operating system files into Oracle tables. These methods include using any of the following: the SQL*Loader utility, the utl_file package and the import option in Oracle SQL Developer. In this article, I discuss how to easily import data using SQL Developer.

Assume that we have employee data in a CSV file called employees.csv and we wish to import the file’s contents into an Oracle table named employee.

How to Import Data Using SQL Developer

To perform importation, do the following:

  1. Navigate to the schema’s connection and expand the Tables node. In the demonstration, I will use the pre-defined HR schema.

  2. Right-click on the table to be populated and from the list of table options, choose Import Data.

  3. Select the source file through the file browser, and click Open.

  4. The Data Import Wizard will be launched, the first dialogue of which presents a Data Preview. After verifying that the selected data are correct, click Next.

  5. Choose the columns to be used by moving them from the Available Columns pane to the Selected Columns pane.

  6. In step 3 of the wizard, map the source column to the destination column. For example, to import column1 to the table column named dept, click on column1 within the Source Data Columns pane and then choose dept within the Target Table Columns list:

    Likewise, to import column2 to the table column named lastname, click on column2 within the Source Data Columns pane and then choose lastname within the Target Table Columns list:

    Assign the rest of the file columns to the appropriate table columns.

  7. The last step in the wizard asks the user to confirm the action. There is also the option to Send to SQL Worksheet which will show the SQL INSERT statements that will be used by the import utility to populate the target table. In any case, click Finish to continue with the operation.

    Verify that the data were imported by running the following statement:

     SELECT * FROM employee;

    The new records should now be displayed by the database.

    You can learn more about working with Oracle databases and Oracle database training by signing up for our newsletter. Click here to sign up

No TweetBacks yet. (Be the first to Tweet this post)

4 Responses to “Importing Data Using Oracle SQL Developer”

  1. ­Great post. Here’s how you can convert Microsoft Access to web in minutes http://www.caspio.com/extend/platform-extensions/ms-access-database-online/convert-ms-access-to-web.aspx

  2. Found this through Google. Very helpful, but I ran into a problem which may indicate that I did something incorrect. Ran the import pointing to an excel file. The Table was created, and the “messages-log” displaied x-number of “1 row inserted” message, and I could look at the table through SQL Developer and see the data, no select statement would ever bring back any data. For some reason those inserts were not getting committed. The only way I was able to get the data loaded was to check off the “Send to SQL Worksheet” and then run the insert statements manually and, afterwards, issue a commit.

    Any thoughts what I might have done wrong?

    Thanks,
    Lou

  3. When i’m importing data my one row is getting increased by one.
    The source table contain 653 rows but after importing its converted into 654 i don’t know how this is happening.?

  4. whenever i am trying to import data in Oracle sql developer its giving an error.
    the error is (there are no readers registered for the sql type)

    please revert me.

Tweetbacks

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

© Webucator, Inc. All rights reserved. | Toll Free: 877-932-8228 | UK: 0808-101-3484 | From outside the USA: 315-849-2724 | Fax: 315-849-2723