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

thatjeffsmith SQL Developer 76 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.

Tell Others About This Story:

Comments 76

  1. Thanks for your valuable point of excluding header which removes duplicate columns,it save lots of time for me. πŸ™‚

  2. 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.

  3. 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.

  4. Hello Jeff, thanks for the post.

    At first you said “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.”

    My question, how can we do that? I don’t see any way to connect Oracle Sql Developer to Excel, or even create tables without having to connect to a database.

    Thank you

    1. thatjeffsmith Post
      Author

      i meant it costs something to put it in the database. if you’re just using the db to do simple things you could do in Excel, you might not want to do it

      if it’s business data, it probably should ALREADY be in the database

      we don’t offer jdbc connectivity from SQL Developer to Excel documents, for which I’m very thankful

      you can however use ODBC to connect to an Oracle Database from an Excel file (and also Access) – which I’m not a big fan of

      1. So if I understand all this, and I am no expert, then I need to get what is in Excel into a db table to use with other db tables. In my case it would mean running some type of SQL server on my desktop for the data I need to use.
        Can tables on different connections even be joined?

  5. Hi Jeff,

    Tried to create a table and import data from Excel workbook yesterday for the first time. Finally got it to see the data rows and work by copying the spreadsheet (somewhat at random). Now trying it again today and it’s doing the same thing. SQL Developer 4.1.19, Excel 2010, Java 1.8.0_45, and Windows 10.

    On the Import Wizard step 1 to browse for the workbook and select a worksheet, everything is fine … but the preview only sees the column heading row, the first row, and none of the data rows. xls, xlsx doesn’t matter. But some workbooks (the ones I”m not interested in) show data rows.

    Unless the preview sees more than 1 row, the Next button does nothing.

    The workaround is to save the worksheet as a .csv file, but it sure looks like the wizard preview knows about .xls and .xlsx file formats, as evidenced by being able to read one row.

    Are there restrictions on what types of data (plain text, formulas, conditional formatting, whether the workbook is open in Excel, whether Excel is running, etc.) can be seen?

    Any suggestions?

    Thanks,
    Skip

    1. thatjeffsmith Post
      Author

      Forumlas might not make it happy.

      Send me your spreadsheet or one that exhibits the behavior, and I’ll take a look.

      I believe the code is expecting ‘flat data’ on a worksheet, and that’s pretty much it.

  6. You may have already answered this question, but there is no plsql script that I can execute in the worksheet to import data out of a csv file and into a table? I understand how to do it through the GUI, but there is no corresponding sql to do the same thing?

    1. thatjeffsmith Post
      Author

      I’m sure there is, but you’d have to write it. Other people most likely already have. I’ve seen solutions posted online in other communities.

      You can also use the GUI to produce a SQL*Loader script that you can run if that’s more to your likely.

  7. Hey Jeff,

    Let me start off by saying great blog sir! This is helpful. I am pretty new to Oracle development, but have developed in SQL server and DB2 for a few years. If you already commented on this question my apologies, but is there is a way to do this by a script? All my attempts are failing. I can’t find a good explanation of how to do this. I am trying create a script so I can easily re-run the process.

    Thanks so much and keep up the good work!

  8. Hi Jeff,

    THanks for sharing.
    I tried to import from Windows an Excel file having 18K rows (7MB), as suggested in your post. It was not a success – the SQL developper took over all 4 CPUs for several minutes, then announced shortly “… Task failed”.
    I added more memory (4096MB) using conf. Same problem.
    Tried with SQL Developer 4.02 or 3.20 – same results.
    Have you ever experienced such freeze of the tool? Could you suggest a reason for it?

    THanks a lot for your answer.
    Dmitry

    1. thatjeffsmith Post
      Author
      1. With CSV, it works like charm. Difference in speed is remarkable.

        I have had a small problem that import mis-estimates size of varchar2 columns, despite all checks initiated by “Verify” button ended up with “SUCESS”. No big deal, I enlarged columns a little bit, then all went through fine.

        THanks a lot for the solution!
        Dmitry.

        1. thatjeffsmith Post
          Author

          The verify only checks the data in preview window, so by default the first 100 records. When defining the column widths, you really want to fall back on your data model to determine how much space to set aside for your strings. Or when in doubt, go big with VARCHAR2’s.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  9. I am switching from SQL Server to Oracle, and I am trying to rewrite a SQL Server script that performs the following 3 steps in a row. In pseudo code:
    Create Table Stuff (etc…)
    Bulk insert a csv file (sqlldr in Oracle)
    select * from Stuff where field name = ‘1’
    I can’t get sqlldr to work in a script, am I just not finding the correct website? Thanks!

    1. thatjeffsmith Post
      Author
      1. In SQL Developer I am running variations of (including the path to sqlldr) :
        sqlldr [email protected]/mithril control=C:\Users\Penelope\Documents\BulkInsert\loader.ctl.
        The result is “unknown command”. I’d include the contents of the ctl file but I don’t think it’s even getting there.

        1. thatjeffsmith Post
          Author
          1. Bummer! Before I spend a lot of time trying to figure out how to do the following only to find that I can’t, could you tell me if is it possible to run a script from the sqlplus command line that contains the three steps I mention above? Create a table, use sqlldr to populate it, and run queries? Thank you again for your help!

          2. thatjeffsmith Post
            Author
  10. I am facing problem while loading data as one of the data contains ‘&’ instead of ‘and’. This ‘&’ is being treated as an input parameter and hence is not getting executed .

    1. thatjeffsmith Post
      Author

      Have SQL Developer generate the INSERT SCRIPT method instead. And then at the beginning of your script, add a

      SET SCAN OFF

      This will tell the script engine not to prompt for replacement values on occurrences of & in your data.

  11. Hi,
    how can I get the max. size of each column? Or what is the best way to import big csv’s, which I dont know their Length?

    And is there a option to change the max. size of import??

    1. thatjeffsmith Post
      Author

      >>how can I get the max. size of each column?
      A perl script maybe?

      >>what is the best way to import big csv’s, which I dont know their Length?
      You could cheat and import all text fields as VARCHAR2(4000) (or 32k if 12c) or as a CLOB.

      >>And is there a option to change the max. size of import??
      Not sure what you mean…there’s generally no max size of an import.

        1. thatjeffsmith Post
          Author

          VARCHAR2 defined columns only take what they need. A 1 character string would only take a single byte, assuming single-byte.

          It’s lazy design, but I was being lazy.

          If this is a real project, then look at your data model.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

          That’s not that many rows why is the excel file so big? I have an excel file of 100,000 rows, and SQL Developer can import it just fine.

          If you can’t shrink the size of the file down, you may have to save it to a CSV format and then do the import.

          1. I cant Convert it to CSV, because my data’s Language is “Persian” and thay will change (Like this: ???)

          2. I make a file with 528 rows and 1 MB (for test). It import without any problem. But when add 1 row, it dont dose it! (without any error!)

          3. I understood my problem! My file have 180 columns. when I try to import file with 10 column and 65000 row, it did fine.
            How can i import excel file with 180 columns and 65000 rows?

          4. thatjeffsmith Post
            Author
          5. I tried to break my file. but dont import more than 528 rows (with 180 column and 1 mb)!
            is it depended to character?

  12. Hi Jeff,
    You’re a life saver thanks so much for taking the time to teach us. I have a question regarding the import from Exce to Oracle using SQL Dev, which I love: How do you import an Excel table that has calculated fields? Would that work in Oracle? Thanks so much.

    1. thatjeffsmith Post
      Author
  13. Jeff,

    thank you for sharing your knowledge.
    I was trying to import from data a csv file. I didn’t get to import strings containing line breaks.
    When previewing data, it looks ok. When importing, sqldeveloper treats line breaks as record delimiters (although the strings are enclosed by ‘”‘ and i configured this in the settings). Am i missing something or is this a bug?

    BTW: if i save the file as xls it’s the same: preview is ok, import not.

    Thank you

    Regards
    Martin

    1. thatjeffsmith Post
      Author
  14. Hi,

    My Query was ‘Importing data from excel to oracle table through using Oracle Query’.

    Will you please help me for this.

    Thank you,

    Regards,
    Nicky

    1. thatjeffsmith Post
      Author

      I don’t know of a way to do it strictly by query.

      However, if your data was in a delimited text file, such as a CSV – you could create an external table that reads that file, and use SQL to insert the data to another table…

      1. Hi Jeff,

        thanks for the fast response.

        The script shows the same effect: Parts of the string are treated as separate rows.

        I dont understand (yet) why the preview function correctly shows the string including line breaks as one attribute of a row and the import treats parts of the string as separate rows.

        Any hints?

        Thank you
        & Regards
        Martin

        1. thatjeffsmith Post
          Author

          Can you code me up a test case? Send me an excel file that exhibits this behavior – also, confirm that you’re on the latest version, 4.0.2, as that’s what I’ll be using to replicate what your example.

  15. Jeff

    Everything went just as your instructions above showed, no hitches. I wish I could say this was true for so many other procedures given on the Internet. Your tip on Data Model was most helpful for me as this is really my Power Basic ‘DIM TYPE’ keep preaching such things. Thank you for the superb guidance.

    1. thatjeffsmith Post
      Author

      Hey Kevin – thanks! Sorry I didn’t reply sooner, I got caught up in conferences and vacation. Let me know how it’s going and how we can help you as you’re getting started with Oracle Database.

      1. Boy I’m glad that I left some notes here as I am back to refresh my memory on what I done 8 months ago…. Data Model is the key item I think. I’m going to give it a go again to import a whole lot more data. I haven’t done anything with Oracle or SQL in the interim but want to pick up where I left off. At a read through it seems clear enough I let you know how it goes Jeff.

  16. Jeff
    I like the ‘cut of your jib’ a straight forward approach in explaining what needs to be done in transferring data from .csv files into tables in Oracle. I have confidence in your instructions on the first read through, a few more read read overs and I’ll try something that I’ve been planning to do for many many years. I just downloaded Oracle Ex 11 and SQL Developer yesterday and am keen to see my Power Basic programs interfacing with Oracle.

  17. Hey Jeff,
    Thanks so much for such a great blog! I have a query- i have a datasheet containing 7 million rows in CSV format which when opened in excel shows the first one million records due to its limitations…….is there a way to import randomly selected 1 or 2 million rows into oracle developer? (Note: I CAN import the first 1-2 million, but i need to randomly select to get rid off any bias) Any suggestion would be great πŸ™‚ Thanks and cheers for the wonderful job you are doing here!

    1. thatjeffsmith Post
      Author

      7 million you say? That’s…a lot. Don’t even bother with Excel. You want to setup a SQL*Loader session instead.

      Also, ‘randomly’ and database inserts aren’t phrases I usually hear in the same sentence unless someone is reporting a bug πŸ™‚

      1. thatjeffsmith Post
        Author

        My boss reminds me that you could also setup your CSV as an External Table and then use the Create tables as select using the SAMPLE clause

        select * From table (20) — this would give you 20% of the rows, now you feed that in your CTAS off of your external table and you got your uploading 1-2M ‘random’ rows to your table.

        1. ahha…sounds good…thanks πŸ™‚ lemme try that out as soon as I can πŸ™‚

          BTW….”Random” is a favorite word for statistical analysts like us πŸ˜€ Thanks so much!

  18. Hey Jeff I have a problem: I cant seem to get past the column definition step because of my column names. A pop up box with the message “Validation Failed: The following new table columns have invalid names:” Meanwhile, there is no listing of such faulty names under the message. These are the changes I made to the names from my Excel table (with the original names in parentheses):

    :ruoul (REVOLVING UTILIZATION OF UNSECURED LINE)

    age (untouched)

    numoftdpdnw ( Number Of Time 30-59 Days Past Due Not Worse)

    Debt Ratio (lowered the captial letters)

    MonthlyIncome (untouched)

    numofoclal (Number Of Open Credit Lines And Loans)

    numtdl (Number Of Times 90 Days Late)

    numrelol (Number Real Estate Loans Or Lines)

    numoftimedpdnw (Number Of Time 60-89 Days Past Due Not Worse)

    Numofdependents (NumberOfDependents)

    Please help! Thanks!

    1. thatjeffsmith Post
      Author

      Osazee,

      Can you email your spreadsheet – just the headers and one row of data will suffice. Then I can take a look at it for myself.

      Quick glance, the ‘:’ in the first column is probably the issue. Try quoting it if you really want the ‘:’ in your column name. Also, no space either, so Debt Ratio should be either DebtRation or “Debt Ratio”

      You’ll be better off w/o using quoted column headers if you can get away with it.

      Thanks,
      Jeff
      [email protected]

        1. thatjeffsmith Post
          Author
    2. thatjeffsmith Post
      Author
  19. Hi,
    Data is coming in excel file format for every day.
    Example:
    abc_19022013.xls
    abc_20022013.xls
    abc_21022013.xls …. so on.

    So I created table and inserting data by using import option in sql developer every day as per blog.
    I would like to schedule and automate the insertion process by using sql developer.
    Please let me know the solution.

    1. thatjeffsmith Post
      Author

      Check out my post on using SQL Developer to create SQL*Loader runs based off spreadsheets. Once you have that going, consider an OS job that kicks off that script on demand or on schedule.

  20. Jeff, congratulations for your blog, it’s very helpful.
    One thing I consider would be useful for next versions, is the wizard auto-discovering field size. I often receive large excel data from customers, and have no idea what should be field size because the data varies a lot.
    Though I have a workaround using max and length excel functions at the bottom of the columns, nothing is easier than have nothing to do. πŸ™‚
    You mentioned a better “guessing” scheme for identifying data type, so if possible please consider my suggestion also.

    Thank you.

  21. Hi Jeff. Is there any limitation on the size of the excel that i can import? we are facing an issue for files greater than 3 MB in size. We have around 1.6 Million records, so was wondering the best way to upload it in the Oracle tables.

    1. thatjeffsmith Post
      Author

      Yes – you’re probably exhausting the memory available to the JVM. This same limitation exists when writing out the XLS files using SQL Developer. You can tell SQL Developer when it starts to grab more memory using this flag – edit your sqldeveloper.conf file –

      AddVMOption -XX:MaxPermSize=2048M

      This would give you a full 2 GB of memory and might be enough to read in all that data…BUT

      But, there’s a better way.

      Save your XLS file as a CSV file.

      Then use the wizard. And be sure to use the SQL*Loader option vs INSERT. It will be MUCH more efficient in loading the data, and the database will thank you πŸ™‚

      For our next version of SQL Developer we’re looking to upgrade the component we use for the Excel stuff so the memory bottleneck wont’ be there anymore.

  22. I am new to Oracle. Googled the topic of “how to import from excel to new oracle table”. Worked like a charm!! Great instructions and displays. I have passed your site on to a few experienced co-workers who appreciated the tip.
    Thanks!! I have bookmarked you for future use.

    1. thatjeffsmith Post
      Author

      Thanks Bill! Appreciate the note and appreciate even more you sharing this with your colleagues. If anyone in…Albany?…has any questions about SQL Developer, feel free to drop me a line and I’ll see what I can do!

Leave a Reply

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