FB

SQL Developer Import CSV: How To

If you’re using SQL Developer, there may be a time where you want to import a CSV file into your Oracle database.

SQL Developer includes a wizard that lets you import a file. Let’s see how to use it in this guide.

Our Sample Data

We’ll load a CSV (Comma Separated Values) file into our Oracle database using SQL Developer. Here’s the sample CSV we’ll use in this article:

created_date,product_name,category_id,price
2019-04-21,'Large Table',2,120
2020-03-08,'Chair',1,39
2021-02-16,'Couch',1,100
2020-11-04,'Standing Desk',3,240
2015-01-20,'Coffee Table',3,79
2021-06-29,'Dining Table',3,36
2020-04-13,'Ottoman',4,29

If you want to follow along, you can download this CSV file here: sample_csv_data.csv.

Let’s import this into SQL Developer.

 

Import a CSV into SQL Developer

To do this, follow the steps below.

Step 1: Open SQL Developer and connect to your database.

Step 2: In the Connections panel, you have two methods, depending on whether you have a table already:

If you already have a table to import data into, right-click on the table and select Import Data.

sql developer import 01

If you don’t have a table, you can create one as part of the import process. Right-click on the Tables entry and select Import Data.

sql developer import 02

In the steps below, we’ll assume that a table does not exist, and we’ve selected Import Data by right-clicking on the Tables item.

You’ll see the Data Import Wizard screen.

sql developer import 03

Step 3: Browse and select your CSV file.

sql developer import 04

Step 4: Change any of the settings you need for your file if needed.

sql developer import 05

These settings include:

  • Header
  • Skip Rows
  • Format
  • Preview Row Limit
  • Encoding
  • Delimiter
  • Line Terminator
  • Left Enclosure
  • Right Enclosure

The defaults should work in most cases.

You can see what your table would look like with some of the rows from your file in a preview at the bottom.

In the Preview, we can see that the product_name column data has single quotes. We don’t want the single quotes as part of the data, so we can specify that these are our “left enclosure” and “right enclosure” characters.

Change these values from double quotes to single quotes.

sql developer import 06

We can see the quotes no longer appear in the data.

Step 5: Click Next to go to the Import Method screen.

It looks like this:

sql developer import 07

Step 6: Enter a table name to create for the data you are importing.

In this example, we’ve entered a table name of “new_products”.

sql developer import 08

When we do this, we notice that a new step in the process appears: Choose Columns.

Step 7: Leave the defaults and click Next.

You can change a couple of things on this screen if you want to.

For “Import Method”:

  • Insert: this will create and execute an INSERT statement for each row in the table. This means the data is inserted into your table. In
  • Insert Script: this will create an SQL script with a range of Insert statements without running them. This can be helpful if you want to save the file or modify it.
  • External Table: this creates an External Table object for the data to be stored.
  • Staging External Table: this also creates an external table, but can be used as a staging table for inserting into a target table.
  • SQL*Loader Utility: this uses the SQL*Loader feature.

You can also set the Import Row Limit, which is the maximum number of rows that will be imported. This is unchecked by default.

Step 8: On the Choose Columns screen, select any columns you do not want to import and move them to the Available Columns section.

sql developer import 09

This will be useful if your CSV file has more columns than your destination table. You can decide which columns to import and which to ignore.

By default, all columns will be imported. If this is what you want, proceed to the next step.

Step 9: Click Next.

Step 10: On the Column Definition screen, select the data type and format for each of the columns you are importing.

sql developer import 10

SQL Developer makes a pretty good guess based on the data in each column. But you should check that the data types are correct before proceeding.

Click on each of the column names on the left, and review or modify:

  • the target column name
  • the data type
  • the format
  • whether it can be NULL
  • the default value
  • any comments you want to add

You’ll see a sample of the data as well on the right to help with your decision.

In the left panel, the columns change from italics to bold once you have clicked on them, to help you keep track of which ones you’ve seen.

Step 11: Click Next and you’ll see the Finish tab.

This summarises the options you have chosen.

sql developer import 11

Step 12: Click Finish. Your data is imported.

sql developer import 12

 

Check the Imported Data

Now that the data has been imported, let’s quickly check that it’s in the table.

We can run a simple SELECT query:

SELECT *
FROM new_products;

Here are the results:

created_date product_name category_id price
21/APR/19 Large Table 2 120
08/MAR/20 Chair 1 39
16/FEB/21 Couch 1 100
04/NOV/20 Standing Desk 3 240
20/JAN/15 Coffee Table 3 79
29/JUN/21 Dining Table 3 36
13/APR/20 Ottoman 4 29

Here’s what it looks like in SQL Developer:

sql developer import 13

We can see the data has been imported and matches our CSV from earlier.

 

Conclusion

So, that’s how you can import a CSV using Oracle SQL Developer. You follow the Data Import Wizard steps and the defaults are usually OK (but can be changed).

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.