How to Import from Excel to a New Oracle Table with SQL Developer

thatjeffsmith SQL Developer 78 Comments

Tell Others About This Story:

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.

Related Posts

Tell Others About This Story:

Comments 78

  1. Hi,
    I would like to Load data from Excel to a Oracle Table but the table does not exist in the schema i connected to.but we have a synonym created in the schema connected to.
    So my question
    Is it possible to load data into a Table created in different schema?

    1. thatjeffsmith Post
      Author

      Go to the Other Users part of your connection tree. Find the schema where the table ‘lives’ – do the import from the actual table. This will work assuming you have INSERT privs.

  2. Thanks for your valuable point of excluding header which removes duplicate columns,it save lots of time for me. 🙂

  3. Hi Jeff,
    Greetings of the day,

    Introduction:First of all i am a newbie to oracle and its things, i have installed oracle 12c.

    Task: To import a csv file with a size of 400MB with 5.5 million lines and 35 columns into a table on sql created with exact same column headers.

    Mode of Import : Data Import Wizard

    Status shown after clicking Finish : Time Taken approx. 3 Minutes, import successful

    Problem1: only 2.5 million lines completed
    Problem2: All the cells of the are having NULL values

    could you please help to solve the issues??
    Thank you in advance.

    Renish

    1. thatjeffsmith Post
      Author

      not w/o seeing your data

      you’re new to oracle, but for uploading THAT much data, you should be using the SQL*Loader route – it’s much better adept at handling larger amounts of data.

      since you’re new, you could try this – on the load method on one of the very first few screens, set to INSERT SCRIPT. That will give you a file of 5.5M insert statements for your table. You’ll want to run it in SQL*Plus.

  4. Can we use this step for large amount of data? i’ve tried to import 47k rows of data and the process was stuck in step 1.
    or there is another ways? beside of split the file into smaller piece of rows ofcourse.

    1. thatjeffsmith Post
      Author

      Should be able to. I’ve done a million rows before with no problems.

      Keep the preview window small. Use xlsx format whenever possible. Try to be on v4 or higher. If all that fails, save your excel file to a csv and try that.

Leave a Reply

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