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:
Navigate to the schema’s connection and expand the Tables node. In the demonstration, I will use the pre-defined HR schema.
Right-click on the table to be populated and from the list of table options, choose Import Data.
Select the source file through the file browser, and click Open.
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.
Choose the columns to be used by moving them from the Available Columns pane to the Selected Columns pane.
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.
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.