In a previous post, How to Import from Excel to Oracle with SQL Developer, I covered step-by-step how to import data from Excel to an existing Oracle table. This post shows how to take a spreadsheet and transform it into a new table in your Oracle database.

But first, please permit me just a few seconds as I step up to my bully-pulpit:

Please do not import data to Excel just because you know SQL better than Excel functions and macros.

Everything has a cost. If you can keep your ad-hoc queries local to your machine and your spreadsheet, that’s a win for everyone. However, if you wan to put your data in Oracle anyway, then who am I to argue? So without much further ado, here’s what you need to know to load your data. And I promise – no more preaching, mostly.

Sample Data

For this exercise I’m taking some data from HR.EMPLOYEES and sending it to a new table. I’m purposely querying the data out in a different column order than the original source.

SELECT DEPARTMENT_ID, MANAGER_ID, HIRE_DATE, FIRST_NAME, LAST_NAME, EMAIL, SALARY,
COMMISSION_PCT, PHONE_NUMBER, JOB_ID, EMPLOYEE_ID
from EMPLOYEES

Use the Grid – ‘Export’ option to save data to Excel or CSV

Obviously if your data is already in a table somewhere, it’s always going to be better to load it to a new table via a Create Table As Select (CTAS.) Even going across a DB_LINK may be more efficient than writing to Excel and then loading it back to Oracle. For this tutorial I’m assuming your data ONLY exists in Excel before getting started.

Step One: Connect to Your Database as the New Table Owner

You can only invoke the ‘Import Data…’ wizard from your connection owner schema. Of course if the table already exists, you can simply load your data by mouse-right-clicking on said table.

Step Two: Find the ‘Table’ Node and Access the Context Menu

‘Context menu’ is a fancy way of saying right-mouse-click popup menu.

Step Three: Select ‘Import Data…’

It says ‘Import,’ but we’ll also be creating a new table for the data to be stored in.

Step Four: Step Through the Wizard

You get to answer a few questions. Don’t like wizards? You could always script out this process yourself using SQL*Loader.

Load the file

Point to your XLS, XLSX, or CSV file.

Preview the data

Depending on the size of your spreadsheet, it may take a few moments to load up the preview dialog. This step exactly mirrors the data preview from my earlier post. However, a few things to note:

  • The ‘Header’ tell the tool that there IS a header and not to include the first read row as data
  • Skip rows – how many rows to skip. If your header line doesn’t appear until row 5, you’ll want to skip 4 rows
  • You can load the data from any worksheet, we’ll default to the first one though

‘Header’ and ‘Skip’ controls are independent controls. Skip tells how many rows to go before we get to the header.

Name Your Table

There are a few options here. You can generate a script to create the table and then populate the table via INSERTs. Use this option if you want to preview or approve what’s going to be happening in your database. You can also tell SQL Developer to only load a specific number of rows.

What’s in a name, really?

Choose Columns

This step is important. Your spreadsheet may have 30 columns, but you only want to import 10 of them. Select the 10 columns you want to form the new table. Also, add them to the ‘Selected Columns’ panel in the ORDER you want them created in the new table.

It’s easy to change the column order at creation time. After the fact, not so much.

Column(s) Definition

If there’s a wizard you don’t want to blindly click through, this one definitely qualifies. But if you’re asleep at the wheel, PLEASE make sure you’re paying attention when it comes to defining the columns. Two things to be aware of:

  1. We’re defaulting the columns to strings (VARCHAR2s)
  2. We’re defaulting the size to the max length of the data previewed

For point #1, we’re looking to have a better ‘guessing’ scheme for upcoming versions. But there’s no bigger problem in applications and data quality today than developers storing dates as strings or numbers. Be sure the datatype for each column is appropriate. If in doubt, consult your data model. If you don’t have a data model, consider taking a step back and creating one. I know, I know, I said I wouldn’t be preaching anymore. Sorry.

For point #2, there might be values that exceed this length. Size the columns appropriately based on your data model and application specifications.

Change the datatypes as appropriate!

Verify Your Parameters

This step is voluntary, but I recommend you always use it. It will help you catch date columns defined with missing or invalid date formats.

Finish!

SQL Developer will either create the table and load the data, or will will generate the create and insert scripts.