How to Import from Excel to Oracle with SQL Developer

thatjeffsmith SQL Developer 285 Comments

Tell Others About This Story:

This question comes up about as frequently as the ‘how do I export data to XLS?’ question. It’s pretty simple once you’ve ran through the process a few times. But you may be here because you’ve never ran through the process before.

This post will step you from beginning to end. You should be prepared to import data to an existing table from Excel with confidence after reading this post. You can use SQL Developer to create a new table for your Excel data, but that will be covered in a subsequent post.

Warning: This post has a LOT of pictures.

For our example I’ll be using HR.EMPLOYEES to build the XLS file. I have created a blank copy of the table in another schema and want to import the data from my excel file over.

Note: We’ve updated this feature for version 4.1!

Step 0: The Empty Oracle Table and your Excel File

You have an Oracle table and you have one or more Excel files.

Data here but not there!?!

You do know how to view multiple objects at once in SQL Developer, right?

Step 1: Mouse-right click – Import Data

Yes, it's that easy.

Step 2: Select your input (XLS) file

Yes, we also support XLSX, CSV, etc

Step 3: Verify the data being read

Mind the headers!

Does your Excel file have column headers? Do we want to treat those as a row to the table? Probably not. The default options take care of this. You can also choose to preview more than 100 rows.

Here’s what it looks like if you uncheck the ‘Header’ box

Sometimes you may want the column headers as a row in the table?

Sometimes your Excel file has multiple headers, or you may need to only import a certain subset of the spreadsheet. Use the ‘Skip Rows’ option to get the right data.

Step 4: Create a script or import automatically

Script or do it for me?

For this exercise the ‘Insert’ method will be used.

Step 5:

Choose the Excel columns to be imported

You may have an Excel file with 100 columns but your table only has 30. This is where you tell SQL Developer what columns are to be used for the import. You can also change up the column order, which may make the next step a bit easier.

Step 6:

If you’re not paying attention and just letting the wizard guide you home, then now is the time to wake up. There’s a good chance the column order of the Excel file won’t match the definition of your table. This is where you will tell SQL Developer what columns in the spreadsheet match up to what columns in the Oracle table.

Step 7: Verify your settings

Hit the ‘verify’ button. Fix any mistakes.

Ruh roh raggy!

SQL Developer is telling you it doesn’t know how to reconcile the data for this DATE column. We need to know what the DATE FORMAT is.

So we need to go back to the Column definition wizard and inspect the HIRE_DATE column settings.

You need to look at how the dates are stored in the spreadsheet and write them in terms that Oracle can understand. This will be used on the INSERTs via a TO_DATE() function that will turn your Excel string into an actual DATE value.

After correcting this, go back to the Verification screen and see if that fixes the problem.

Step 8:

Everything looks right!

Click on the ‘Finish’ button.

Step 9: Verify the import look at your new table data

The data is there and the dates look right!

Note the ‘Log’ panel. SQL Developer is processing the records in batches of 50. No errors and the data is there!

Just a few more pointers when it comes to Excel

  • Storing data in Excel can be…dangerous
  • If you have a lot of data or if this will be a repeating process, consider External Tables or SQL*Loader instead
  • If you like to put data in Oracle because you understand SQL better than Excel macros, then welcome to the club ๐Ÿ˜‰
Tell Others About This Story:

Comments 285

  1. When populating an Oracle table with fields that are ‘non nullable’. what value do you put in the Excel cell as a blank character?

    1. thatjeffsmith Post
      Author
  2. I have a csv file that I want to load using import data, one column is a DATE data type. I cannot format the csv input file correctly for nulls. I have tried using the word null,null with quotes ‘null’, nothing or two quotes together and the to_date() function. The import data tool does not like the null values in the csv. I get an error stating the Data is not compatible with column definition or is not available for a not nullable column. The column is a DATE column nulls are allowed all the other dates work but the nulls do not.

    1. thatjeffsmith Post
      Author

      You need to remove the ‘null’ text from the column. Search and replace in your text editor of choice, or in SQL Dev when creating your CSV exports, make sure the preferences are set to have NOTHING printed for NULLs

      1. Hi there,
        Thanks for the prompt reply. The data looks like so: columnA,,columnC,columnD, Where the date field columnB is blank for some rows. The importer still does not like the empty place holder.

        1. thatjeffsmith Post
          Author

          Works for me – but you haven’t shown me your data or what exactly isn’t working. Just b/c the importer shows a funny pic on your NULL val, doesn’t mean it won’t happily import the data.

          1. Hi there,
            Yes in fact it does work fine despite the warning.
            I assumed it would not given the warning on the importer.
            I simply did not try and assumed it would not work.

            Thanks Again

  3. I need urgent solution to this error. I am a beginner in Oracle SQL developer, I am having issues importing to Oracle SQL developer. I created a table and save on my desktop as “test.xlsx”, and I also create empty table on Sql developer database. This is the error message i am having ” c:\users\desktop\test.xlsx cannot be opened due to the following error:org/dom4j/Namespace. Kindly assist me while this has been giving me concern. Thank you.

  4. Hi Sir,

    I would like to export data from table only 1 month data,How can I export using sql developer, I can export data,but all.

    Thanks.
    Best Regards,
    Theingi.

    1. thatjeffsmith Post
      Author
  5. @thatjeffsmith…I have sql developer version 3.1 and want to Import data from Excel(xls) file to an oracle table.the file has 65k records.able to go till last screen and verified parameter after that when loading process started sql developer get hanged and I waited for 30 min it still hanged and no data imported into the table.
    Please help me

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
  6. Hi, I have many excel sheets which all have certain alphanumeric / numeric value which can be distinguished. I want to only import those values/keys from excel to Oracle or any db. Please note that the value/keys to be imported from multiple excel sheets and have varied rows and columns but the defined value/key will remain same. These values imported from each of the excel sheet is a unique record thus would be parsed in db for further analysis.
    I sincerely request for your help on it.

    1. thatjeffsmith Post
      Author
  7. While importing data in .csv or .tsv format in Oracle SQL Developer 3.0.04, I am getting the bellow error:

    o.d.r.data.writers.ImportGenerator$SchemaTaskListener Task rollback.

    Please help to resolve it.

    1. thatjeffsmith Post
      Author
  8. Hello Jeff,
    I appreciate your follow up comments!

    Here, i need your help. I’m uploading a CSV file which runs a job and insert the entries from CSV file into DB. Later give me the required details.

    But it’s not inserting the complete row given in CSV.

    For.e.g.
    1. I gave CSV with 76 K rows and got data for only 15K rows
    2. I gave CSV with 144 K rows and got data for only 28K rows
    3. I gave CSV with 6K records, here it worked fine.

    Looking forward for update on this.

    Thanks!

    1. thatjeffsmith Post
      Author

      do the import again, but set the method to Script.

      take the script that’s generated – and run it in the worksheet

      observe why the rows are not being inserted – and address the issue(s)

      1. I’m sorry but i have no idea how can i change the method to script here. Actually as you saw third condition worked fine, because count was 6K. If it will be more than 6K, a batch job will run.

        Then it only takes first few rows and insert in DB. Please help.

        Thanks!

        1. thatjeffsmith Post
          Author
          1. I’m sorry, i think, issue explanation was not enough earlier. It’s already developed application and we are not doing the manual import/export from sql developer.

            We have an option in app to take input csv file, then its insert the data automatically (by code) after that in DB and generate a output CSV with required details.

            I.e. Here when i give input csv file, its only read first some row and also while exporting, it doesn’t give complete required details.

            Please help.

          2. thatjeffsmith Post
            Author
          3. No, no help in code.. I Just need possibilities which can cause to get only some first data..anyhow we have analyzed the issue & will be fixed soon..Thanks for your help & time.

          4. thatjeffsmith Post
            Author
    2. Hello,
      I am getting the following error in oracle sql developer after clicking the finish button in the data import wizard.

      Task canceled and import rolled back. Task Canceled

      Can you please help me

      1. thatjeffsmith Post
        Author

        did it open a log file with details after the import?

        set the import method to INSERT SCRIPT – and it will build just that, and you can run it yourself and see if and when there are any problems

        1. Thank you, I used Insert script option, but the problem was with the Xls file. I converted the file to csv format. then it worked

  9. Dear Jeffsmith,

    I am having data around 108 lakhs records, I am not able to uploading into tables using SQL Developer, could you please suggest me, My sqldeveloper is getting hanged. i need to do ASAP.

  10. Dear Jeffsmith,
    I am having data around 50 lakhs records, I am not able to uploading into tables using SQL Developer, could you please suggest me. My SQLDeveloper is getting Hanged.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

          I’ve done a million records before, with no problems.

          If you’re doing 10M+ records, suggest you save your Excel file as CSV and use SQL*Loader or an External Table to load your data.

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        What does status started means.
        For one test “Checking data against column size” I am constantly getting status as “STARTED”.
        Please help me.

  11. You saved me from having to write the insert statements between the columns of the spreadsheets, export the text, verify it and only then execute.

    My xlsx files were created by SQL Developer itself, so this task became even easier. Thank you very much.

  12. While importing sdo_geometry data from excel to oracle db using SQL developer tool getting below error
    ORA-00932: inconsistent datatypes: expected MDSYS.SDO_GEOMETRY got CHAR.

    Please help anyone on this.

    1. thatjeffsmith Post
      Author
  13. Hi,

    I’m trying to import an excel spreadsheet into a table. The spreadsheet contains string values of “TRUE” or “FALSE” in few columns. When generating insert statement SQL Developer converts them to lower case. Is there any way to disable this “feature” and import as is? The version I use is 4.1.3

  14. I am trying to use sql developer 4.0 to import excel which has a large text field, on the first popup page of the Data Import Wizard (step 1 of 5), it brings up the preview of 100 rows with no problem, but when I try to click ‘Next’, nothing happens, it won’t go to the step 2 page.
    Based on my research, I have changed the conf file by adding the
    AddVMOption -Xmx1024M

    But no luck… Any suggestions? Thanks.. Rick

    1. thatjeffsmith Post
      Author
      1. Version 4.1.2.20…
        40k records, one field contains around 3000 – 4000 characters… the others are small items (like first name, date…)
        For now, it’s working only if the excel file contains around 100 records..

        Thanks, Rick

        1. thatjeffsmith Post
          Author

          that’s a big file, or a lot of data to process

          suggestions:
          add 3-4 GB of RAM for the JVM – make sure you’re making this config change in the product.conf file OR save your Excel file as a CSV and try again

          1. Thanks for the help.
            After changing the conf file (sqldeveloper.conf under bin folder), I had tried with xlsx with 2000 records, still not working.

            Tried with CSV (with | as the delimiter), but the large text item contains multi-lines of text for one record. The sql developer cannot read it well… Any solutions for situation like this?

            Thanks, Rick

  15. I have an existing table with millions of records. Recently we added two fields to the table. Now we have a million of records which we need to insert into those two new fields using Employee ID as key constraint. I data for those two columns is in excel. How can we utilize Oracle SQL Developer to import the data into those columns using Employee Id as Key constraint.

    1. thatjeffsmith Post
      Author
  16. Hi, I am experiencing the same error mentioned by Isingh.

    When I tried to import data using excel to sql server using import data option, in the final step the status shows as ‘STARTED’. I couldn’t complete the import. thanks for your help

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
  17. When I do Import Data from SQL Developer, all it works fine, but getting an issue when a cell in csv has the data in multiple lines. Error shows as below
    “Line contains invalid enclosed character data or delimiter at position ”
    Same issue when in either of the cases “Insert” or “Insert Scripts”
    Is there anyway to handle this issue in SQL Developer.
    It would be a great help answering this…:)

    1. Hi,

      I encountered an odd problem during import of data from excel. I work with PL/SQL Developer 10.* on my laptop, accessing remote oracle server. So far I haven’t faced a problem during import from excel, till yesterday. I tried to import 40K IDs from a single column to a temporary table in the base, made for this reason, with the same header name , only with one column and so on Which I have done it many times. But when I started the import an error message appeared, which was like this one – “You tried to insert a “NULL value into a column that does not accept NULL values”

      I think it was this error: http://www.techonthenet.com/oracle/errors/ora01400.php

      In this moment I thought that there are blank rows in the excel, because I removed duplicates with the function in excel, which deletes the data only from the cells, not the rows. So I checked again, deleted the blank rows below the data, clear everything blank, but still nothing. In the error message box was a button “skip” (during the import), every time when I was able to press the button, I could see different indications – 66 records successfuly imported, 54 records …, 32 … and so on (on portions)

      I didn’t understand what was happening. I tried to move the data to another excel file with paste special, but nothing changed when I tried to import the data from the new file.

      The data was in text format, the last thing what I tried, was to convert the data in to a number. And after this move the import was successful without any problems. I cannot explain to myself why this happened, where is the reason?

      And one more thing, strange again – the records in the file were 29444, after import I saw indication that I have 29444 successfully imported records. But when I checked the table in the database, there were 30560. Which extra confused me. Before the import I intentionally deleted the old data in this table, and it was blank.

      Regards’
      Emil

      1. thatjeffsmith Post
        Author
  18. Hi, I want to import excel sheet in already exist table using oracle SQL, but i cannot see my table under connections tab. As i have very limited access to DB, but the table in which i want to insert excel sheet, for that table i have read, insert, update , delete access. Can u please help me, to insert excel in this already exist table, as i don’t want to create any new table in DB. Looking forward for your reply.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  19. hello brother iam using latest oracle sql developer when i want to try to import my excel sheet its not working .i mean when i right click on my oracle tables it ‘s not enabling i cont get the import opctions what type of settings i want to change plz tell me

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author
  20. Hi,
    I am trying to import data from an excel file to a table in oracle database. During Verify stage I am getting error saying “Source columns , do not have data types assigned”. Though I have assigned datatypes for all the columns in table. Here source columns means our excel or table?. Please help. I am using sql developer 1.5.5 version and oracle 11.5g

    1. thatjeffsmith Post
      Author
      1. Hi Smith,
        It might going to take time for me to upgrade to higher versions. Is import option has any issues in 1.5.5 version or could you let me know why I am getting that error if you have any idea. I understand that version is too old for you to help with. But if you could do any favour in this regard will be much appreciated.
        I stuck with this issue for a long while. After clicking on Verify I am getting error in of the check points saying โ€œSource columns , do not have data types assignedโ€.

        1. thatjeffsmith Post
          Author

          takes 5 minutes to install sqldev, I timed it

          download 4.1.1 with the embedded JDK

          unzip it

          run it – you’re done ๐Ÿ™‚

          >>Source columns , do not have data types assigned
          So you’re creating a new table based on a spreadsheet. The error is telling you to that one of the columns doesn’t have a data type assigned to it. If you think you DO have this take care of, in the earlier part of the wizard, look for an option to ‘create script’ – this will generate code for the process and you can use that and skip the verify bit.

          1. Hi Smith,

            Thank you so much for the help. After using ‘create script’, I realized my errors (There was an extra null column I was trying to import and many blank rows of data for which my table data types does not match hence giving the error).

            Thanks once again for your valuable inputs.

  21. Hiii.Thanks a lot for such good information.I tried inserting data from .xlsx file to sql tools table.The only problem that i seem to get is that my .xlsx file has 1580 rows and this method just imports 50 rows. When i tried your method , the values in preview row limit and import row limit were 1580 only.Still i get just 50rows imported.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  22. I am trying to import data from a csv file to a table using Oracle SQL developer. However, in doing so, an additional single quote gets added whenever there’s an apostrophe in the string. For example, a string *He’s* in the csv gets loaded as *He”s* in the table. The column type is varchar2(200). I am using version 1.3. Any idea what i can do differently to resolve this? Thank you.

    1. thatjeffsmith Post
      Author
  23. i am unable to load data through this process in sql developer.It is not showing me any kind of FAILED verify parameter, all process are ok. but still data not coming in my table.. i have one table in sql developer containing 52 column and exel sheet containing data with 52 column. please help meโ€ฆ

    thanks in advance.

    1. thatjeffsmith Post
      Author

      set the wizard to generate an INSERT SCRIPT instead – see if that gives you any code or hint as to what is going wrong. Or try the 4.1 Early Adopter and see if we handle your data any better there

  24. Jeff:
    I’m using SQL Developer to import an Excel 2003 formatted spreadsheet into an Oracle 11g database table. Unfortunately it’s giving me a FAILED verify parameter and doesn’t tell me which column it’s referring to.
    How can I handle the following verify parameters FAILED message in SQL Developer:
    Data Types BINARY_DOUBLE, not supported for import

    There is only one spreadsheet column formatted as Number, two columns that are formatted as General, and three columns that are formatted as Text which contain numerical data. Thus, I assume the FAILED message is referring to the column formatted as Number.

    Thank you.

    1. thatjeffsmith Post
      Author
  25. Hallo Jeff,

    i try to Import Data from an CSV-File or excel and have some problems with my implementation ( on an window-xp PC, Version 4.0.3.16, Java(TM)-Plattform 1.7.0_71, German Menu’s).

    1.) I can not select the “Begrenzungszeichen” / Separator. The Comma is fix (this means the Selector is gray and can not be used).

    2.) If i convert my csv-File to an excel-Spreadsheet, there is an empty Screen after i select the Excel-File to import.

    Is this effekt known and how can i handle this.

    Regards

    Volker Klรถs

    PS.: I have another effekt, starting Packages with Constant-Parameter, which i can show by Screenshots.

  26. Jeff ,
    i have table where one column is generating using sequence i.e its primary key .
    i want to import data from excel file to this table so how can the auto generated data will come in this primary key column ?

  27. Jeff,
    I have tried to import 679K records via SQL Developer. I exported XLSX file into CSV format. Then, I created a new file with 100K records.
    SQL Developer froze and stopped working.
    When I decreased to a number of records to 10k , it worked.
    Should I increase a memory ?
    I was able to load a file via Toad Data Point from a CSV file but I still would like to discover a problem the prevented me from using SQL Developer.
    I can email you the dataset.
    SLQ Developer Version: 4.0.3
    Thanks
    Chris

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

          I’m guessing for most the Excel won’t work for very large files like that – our library used to read in Excel files doesn’t do a great job of releasing memory as records are written out. But switching to CSV should pretty much just always work.

          You could try bumping the JVM memory space up. And feel free to send me your CSV at [email protected]

  28. Hey Jeff,

    Thanks for the tutorial!

    It doesn’t appear that the import takes records in the order they exist on the Excel spreadsheet. For instance, we have a spreadsheet where entries are added to the bottom as they occur, but there is not a time stamp associated with their addition. In order to see if Person B was added before person A, you simply look to see if person A is on the list before or after person B. Once I’ve imported the data into SQL Developer, the records are out of order. Is there a way to import them exactly in the order they are in, or will I need to just include a column in my spreadsheet that is numbered and sort that way with SQL Developer?

    Thanks for your help! ๐Ÿ™‚

    1. thatjeffsmith Post
      Author

      I’m betting they are inserted ‘in order’ – you can check the log to see the INSERTs that are run and confirm/deny that. However, row order isn’t guaranteed for tables in an Oracle Database. You’ll need to add a sequence or date field to preserve order if that’s important to you.

      1. You’re right, they are inserting in order, but when I view the records using the Data tab, they appear out of order. I’m assuming there isn’t a method of viewing them in the order they were inserted as I only see a Sort option for A-Z and Z-A.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  29. Hi Jeff, when i try to import excel file (.xlsx) more than 30.000 data into one table, only 1500 data successfully imported, any miss step that i don’t do???

    1. thatjeffsmith Post
      Author
  30. Hi,
    In SQL Developer v4.0.3, jdk1.7.0_71, and I get an empty list of Import Methods (mentioned on this blog) when importing a CSV file (did not try with other).
    I also have sqldeveloper-3.0.04.34 on the same box.
    I launched SQL Developer from the bin directory.
    In the console, I get a java.lang.NullPointerException:
    java.lang.NullPointerException
    at oracle.dbtools.raptor.data.core.GenericTable.populateTable(GenericTa
    le.java:43)
    at oracle.dbtools.raptor.data.core.GenericTable.(GenericTable.jav
    :29)
    at oracle.dbtools.raptor.data.ui.ImportMethodPanel.populateDBO(ImportMe
    hodPanel.java:488)
    at oracle.dbtools.raptor.data.ui.ImportMethodPanel.onEntry(ImportMethod
    anel.java:301)
    at oracle.ide.wizard.FSMWizard.gotoPanel(FSMWizard.java:765)
    at oracle.ide.wizard.FSMWizard.setSelectedPage(FSMWizard.java:481)
    at oracle.bali.ewt.wizard.BaseWizard.selectPage(BaseWizard.java:1966)
    at oracle.ide.wizard.FSMWizard.selectPage(FSMWizard.java:465)
    at oracle.ide.wizard.FSMWizard.doNext(FSMWizard.java:340)
    at oracle.bali.ewt.wizard.BaseWizard$Action$1.run(BaseWizard.java:4033)
    at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:312)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:733)
    at java.awt.EventQueue.access$200(EventQueue.java:103)
    at java.awt.EventQueue$3.run(EventQueue.java:694)
    at java.awt.EventQueue$3.run(EventQueue.java:692)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionD
    main.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:703)
    at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQ
    eueWrapper.java:169)
    at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQu
    ueWrapper.java:151)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThr
    ad.java:242)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread
    java:161)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread
    java:154)
    at java.awt.WaitDispatchSupport$2.run(WaitDispatchSupport.java:182)
    at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:221)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.awt.WaitDispatchSupport.enter(WaitDispatchSupport.java:219)
    at java.awt.Dialog.show(Dialog.java:1082)
    at java.awt.Component.show(Component.java:1655)
    at java.awt.Component.setVisible(Component.java:1607)
    at java.awt.Window.setVisible(Window.java:1014)
    at java.awt.Dialog.setVisible(Dialog.java:1005)
    at oracle.bali.ewt.wizard.WizardDialog.runDialog(WizardDialog.java:382)
    at oracle.bali.ewt.wizard.WizardDialog.runDialog(WizardDialog.java:298)
    at oracle.ide.dialogs.WizardLauncher.runDialog(WizardLauncher.java:51)
    at oracle.dbtools.raptor.data.DataWizard.launch(DataWizard.java:311)
    at oracle.dbtools.raptor.controls.sqldialog.ObjectActionController.invo
    eClassAction(ObjectActionController.java:220)
    at oracle.dbtools.raptor.controls.sqldialog.ObjectActionController.hand
    eEvent(ObjectActionController.java:200)
    at oracle.ide.controller.IdeAction$ControllerDelegatingController.handl
    Event(IdeAction.java:1482)
    at oracle.ide.controller.IdeAction.performAction(IdeAction.java:663)
    at oracle.ide.controller.IdeAction.actionPerformedImpl(IdeAction.java:1
    53)
    at oracle.ide.controller.IdeAction.actionPerformed(IdeAction.java:618)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2
    18)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.ja
    a:2341)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonMode
    .java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:25
    )
    at javax.swing.AbstractButton.doClick(AbstractButton.java:376)
    at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:
    33)
    at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMe
    uItemUI.java:877)
    at java.awt.Component.processMouseEvent(Component.java:6516)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3320)
    at java.awt.Component.processEvent(Component.java:6281)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4872)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4698)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:483
    )
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492

    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2719)
    at java.awt.Component.dispatchEvent(Component.java:4698)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:735)
    at java.awt.EventQueue.access$200(EventQueue.java:103)
    at java.awt.EventQueue$3.run(EventQueue.java:694)
    at java.awt.EventQueue$3.run(EventQueue.java:692)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionD
    main.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionD
    main.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:708)
    at java.awt.EventQueue$4.run(EventQueue.java:706)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionD
    main.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:705)
    at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQ
    eueWrapper.java:169)
    at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQu
    ueWrapper.java:151)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThr
    ad.java:242)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread
    java:161)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThr
    ad.java:150)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146

    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138

    at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)

    1. thatjeffsmith Post
      Author
  31. Hi Jeff
    i have question.When i try to import data from excel to oracle db using sql developer. My excel has one column in which each cell has three divisions. So each row contains a column with 3 divisions.So how to i import data into the table in which i need to get for each row in excel 3 different rows in Oracle db table.
    Can you please help?

    Thanks in advance

    1. thatjeffsmith Post
      Author
  32. Hi Jeff, I am getting error โ€œORA-01722: invalid numberโ€ during Import of csv file having Numeric values with group separator, for example “2,308,000”.
    On preview step it looks ok and Verification is ok.

    I am doing this with SQL Developer Version 3.2.20.09 and tried with Version 4.0.0.13

    Could you advise how to resolve this issue?

    There is test case :
    CREATE TABLE TEST_NUM(test_val NUMBER)
    /
    file test.csv

    test_val
    “2,308,000”
    “-2,308,000”

    After run Import there is error

    –Insert failed for rows 1 through 2
    –ORA-01722: invalid number
    –Row 1
    INSERT INTO TEST_NUM (TEST_VAL) VALUES (2308000.0);
    –Row 2
    INSERT INTO TEST_NUM (TEST_VAL) VALUES (-2308000.0);

    1. thatjeffsmith Post
      Author
      1. as a workaround we are opening the csv in Excel and changing Format of Number columns to not use group separator. Do you think it may be fixed as our PROD support often is using this tool to load user’s data ?

        1. thatjeffsmith Post
          Author
        2. thatjeffsmith Post
          Author
  33. thanks for the resolution of date issue,which most of the developers including myself at the same time are facing at the time of data upload from excle to DB.

  34. Format of date is not changing according to my requirement and Column position is not changing according to desired position. Pls suggest any method

    1. thatjeffsmith Post
      Author

      I don’t know what that means. Format of date is not changing? You have to manually specify the date format as it’s stored in EXCEL so we can successfully import it from text to a DATE in the database when we do the INSERT.

    1. thatjeffsmith Post
      Author
  35. I installed SQL Developer but it does not have an Import wizard. Is this normal?

    I need the import capabilities.

    Can anyone assist?

    Version 4.0.2.15
    Build 15.21

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,
        I’m having the same problem. There is no “import” option when you right click on a table. I can only see “open”, “export” and “copy to oracle”.

        I’m running the same version of Oracle Sql deverloper as Joseph on OSX 10.9.4

        1. thatjeffsmith Post
          Author
          1. Yes, it’s my own table and I do have INSERT privs. I forgot to say that I’m hooked up to a mssql db using the jTDS driver, if that makes any difference.

          2. thatjeffsmith Post
            Author
    1. thatjeffsmith Post
      Author
  36. Due to the nature of the data I can’t share it.
    However it is just an integer column, a date columns (yes, I successfully entered the correct date format mm/dd/yyyy), and some text columns.

    I saved the data as an xls and a csv file, no joy in 4.0.2.
    However, 4.0.0 read the xlsx file, no problem.

    I will uninstall 4.0.2 get a fresh download and reinstall it.

    I tried googling the error message in the log but found nothing helpful, I just thought it might mean something to you.

  37. This process worked fine on my Windows 7 x64 machine in version 4.0.0. Since I upgraded to version 4.0.2 it let’s me get through to step 5, but when I hit finish nothing happens. The process just hangs.

    I get the following line of information in the Logging Page:
    SEVERE, 99, 0, oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1, org/antlr/stringtemplate/CommonGroupLoader

    Any suggestions?

    1. thatjeffsmith Post
      Author

      Go back to version 4.0. In the meantime, send me your input file for me to test – or have you already tried different files/types?

      The other thing you could try is putting 4.0.2 down again…

      1. Sure enough, I deleted the install directory of 4.0.2 and did a fresh download of the 4.0.2 install files.

        The new installation works like a champ.

        Thanks for the direction.

  38. I need a table to which I load icons. Tge SQL*Loader documentation states I would use the following in an csv file.

    DISABLEDICON FILLER CHAR(100),
    DISABLEDSTATE BFILE(CONSTANT “scott_dir1”, DISABLEDICON)),

    In short, can I use this SQLD feature to load BLOBs and is the above method or something like it available?

    1. thatjeffsmith Post
      Author
  39. Yes, I have been importing .csv file which contains 11 million rows and it is not going beyond ” Data Import Wizard Step 1 of 4 ” , ie; Data Preview window is not showing anything. Please suggest.

    1. thatjeffsmith Post
      Author

      If you think it’s a file size issue, try taking only the first 100,000 rows or so to a file, and see if that works. If it does, you may need to increase the amount of memory available to the JVM.

    1. thatjeffsmith Post
      Author
  40. Greetings.

    Thanks for you help here. I’ve followed your steps and all is well. However i am importing 250k 10 character values into a single column table and it has so far taken 45 mins and we are only up to 75K rows. Is this speed normal. i can nearly type them in at that speed

    Cheers

    1. thatjeffsmith Post
      Author

      I wouldn’t think that’s normal. But I don’t know what’s happening on the database. And I don’t have your table or data to play with. But you could try using the sqlldr or external table route instead and that will always be faster.

  41. To begin with, great post…glad for the help. on to the question:

    What if I don’t have the table structure built out in SQL Developer? I have a bunch of csv files with upwards of 200 columns on some, and I don’t want to create each column in the table. I have no issues with every column being varchar – is there anyway to import the csv with column headers, such that the columns from the csv become the columns in the db table?

    Thanks,
    Bassel

    1. thatjeffsmith Post
      Author

      Yes actually. There should be a link up near top of this story, where you can see how to create a new table and load it via the CSV or Excel file. The column headers will get interpreted as the new column names…

  42. Hi,

    I am not able to view the tables in sql developer under the schema…how can I import and run large data inserts?

    Thank You!

    1. thatjeffsmith Post
      Author

      Vandy,

      The person you are logging in as probably doesn’t actually own any tables. Read this post for insight on how to find what you’re looking for.

      You can also scroll down the tree to ‘Other Users’ and expand that. Your tables are probably in another schema.

      View > Find DB Object will also let you search for your tables.

  43. it show msg

    Import Data into table STU_DETAIL from file student1.xls . Task successful and import committed.

    and in log

    Task Cancelled:

  44. it show in msg box import succusful
    bt in log show task cancle and data not import
    i am use office 2007 excle file import in oracle 11g database
    through sql developer

  45. Hi Jeff,

    My requirement is to insert the data from excel/csv file into a table, The excel file that i recieve contains 30 sheets and only specific columns from all the 30 sheets needs to be inserted into a table. All the sheets has data at the same place.
    i tried sql loader but for that i have to manually copy the data from all the sheets into one sheet and then upload it which is very hectic, is there a way to write a pl/sql procedure to insert the data .

    Thanks & Regards,
    Bhavin

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  46. The sql developer is not going past the verification. i get stuck on ‘Checking Data against Column size’ step and it stops working. It is displaying as ‘started’ but the process does not end. I can see no activity in the task manager so not sure if any thing is being done in the background to complete the step. I am using the latest 4.0.2 i believe. I tried to limit the rows to 10 as well with no success. Any help will be really appreciated.

    1. thatjeffsmith Post
      Author

      can you make a copy of scott.emp, e.g. create table emp_copy as select * from scott.emp where 1=2…then take an export of scott.emp and see if you can import it to emp_copy?

      Otherwise, feel free to send me your table ddl and input file to [email protected]

      1. Cant do that in the test environment as the schema do not exit. I will send the ddl and the file with dummy data though. I can also see the sql developer is version 4.0.1.14 .

  47. it’s my controlfile

    options(rows=50000)
    load data infile ‘eventlink.csv’ append into table eventlink fields terminated by ‘,’ trailing nullcols(
    date_heure,a_party_number,b_party_number,event_duration,designation,
    incoming_route,outgoing_route,ic_partner_id,
    price,billed_duration,cod_agrupint )

    and it’s my csv file

    05/04/2014 00:09:35|0632540054|0033620803839|7|O|10023
    |00023|00023|.004317|7|006021

    05/04/2014 00:09:37|0664081474|0033651227794|294|O|10023 |00023|00023|.1813|294|006027

    and when i make this in cmd

    sqlldr USERID=scott/tiger CONTROL=imran.ctl

    SQL*Loader: Release 11.2.0.1.0 – Production on Mer. Avr. 30 10:03:35 2014

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

    Point de validation (COMMIT) atteint – nombre d’enregis. logiques 64

    but i want to have 200 000 lines in the table eventlink but i found any line

    helps meeeeee,plzzz

  48. Good moorning,

    I have rather unusual input data file in CSV format. I tried to import to oracle table using SQL developer version 3.2. Itโ€™s pretty straight forward import. However, Iโ€™m not sure why not all records imported to the table. What is the best way for me to send you the sample data for you to review? Please advice. Thanks

    1. thatjeffsmith Post
      Author

      Have you reviewed it? What makes it ‘unusual?’ What rows don’t go in?

      Use the ‘script’ output method. Then run your script. You’ll see the errors there and can make changes as necessary yourself.

  49. good moorning,i want to import a large data(1500 000records) from file.txt into oracle, but i need how to use sqlloader (the correctly methode).thanks for you

    1. thatjeffsmith Post
      Author

      Do you have access to the database server? If you do, moving the file there and creating an External Table will be faster. If not, then SQL*Loader is perfectly fine. If your text file is delimited, it shouldn’t be too bad to setup.

      You can actually use SQL Developer to do this.

  50. i want to import a large data(two millions records) from .txt into oracle but i havaen’t any idea about sqlloader(how to use it? i have oracle 10g express edition )

  51. Is there any way to incorporate a sequence, and static fields into a data import from an Excel file.
    For instance, if I have table X with fields A,B,C,D,E and have and excel file with fields B and C only. I want to put a sequence.nextval into field A, sysdate into Field D, a constant into field E (a name, or an id of the operator, etc..).

    It’s fairly straight forward to just insert data, but is there any way to add these extra fields?

    thanks.

    1. thatjeffsmith Post
      Author
      1. That wont work. The “defaults” are generally NOT the same, they will be unique for each load. No triggers allowed on tables (for many reasons).

        I will just continue creating insert statements. Just was hoping for something a little easier.

        Thanks.

        1. thatjeffsmith Post
          Author
  52. Thanks.

    I converted the excel file to CSV and imported all 250000 rows successfully.

    I have used SQL Developer Version 3.2.20.09 to import the excel (MS Excel 2010) into oraclce DB 11g R2.

    Is there any other way to import from excel without using SQL Loader/External Table?

    1. thatjeffsmith Post
      Author

      I took SH.SALES, queried out ROWNUM < 70000 to CSV Imported to a new table, HR.SALES_COPY. All 69,999 rows imported successfully. You're going to have to give me much more detail to reproduce this. It's possible that you're exhausting the memory in the JVM with that many rows if it's an Excel import. You can add more, but I need to know which version of SQL Developer you're running first so I can tell you the right file name.

  53. I think I’ve hit a problem that a couple of other replies mentioned. Numbers (formatted NUMBER(9,0)) and with input values like 1234 are being imported as 1234.0. This is a problem because those numbers are foreign keys. An example row in my input CSV looks like this:

    “2383”,”2414″,”PARTY_ASSOCIATION_TYPE_SYS”,”HAS_MEMBER”

    but after it’s been through the import wizard this row is being inserted like this:

    INSERT INTO ESP_PARTY_ASSOCIATIONS (PAS_FROM, PAS_TO, PAS_ASSOCIATION_GROUP_ID, PAS_ASSOCIATION_CODE_ID) VALUES (2383.0,2414.0,’PARTY_ASSOCIATION_TYPE_SYS’,’HAS_MEMBER’);

    You can see it’s appended “.0”. It’s quite strange because some of the earlier tables have NUMBER(9,0) columns with input data that looks the same and which processes correctly.

    Any ideas?

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      The easiest way would be to save your text file in a delimited format, say CSV. So separate each row with a line break, and each column value with a comma.

      Then right-click on the table (or if it doesn’t exist yet the Tables tree node) and say ‘import table data.’

  54. This is an approach that is often applicable. In the past I’ve employed the following three other approaches:

    * Cheap but effective for small volumes: add a column, put a formula in it : =”insert into TABLE(list) values (“‘ & a1 & ‘”, /* number */ ” & b1 & “);” and then fill down that formula and copy it in to TOAD or SQL*Plus. Possibly enclosed by begin/end.
    * Use Invantive Control (note I work there). It allows you to download from Oracle into Excel, add rows according to business rules and upload in the originating view or table using synchronize. Fit for larger volumes upto 20 M rows.
    * Use Pentaho Kettle: can repetively load many tables from one or more worksheets. Fit for very large volumes upto (I guess) 1000 M and more rows. It is free.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  55. Jeff,
    Is it possible, when you do an import, to have the target table created for you by sql dev? In other words in your example above you have the table created in oracle already and you import to it which is great – appreciate the great post!. A nice feature of other products is the ability to have the tool build the target table and then load it with the imported file in one fell swoop. (I realize Oracle dba’s will cringe at this but for testing, productivity and reality – it is a great benefit).

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  56. I created new tables for my csv files and loaded 6 files into 1 table that consists of 84k records. Everything was fine. As for my second table, after loading the first file, the second file doesn’t seems to load into the oracle database. :/ tried multiple times still to no avail.

  57. step-1 and step-2 are successful but at step-3 import method option is missing. what to do. Is anything missing or must be missed in installing. sometimes at the step-1 after selecting the file to open i am not getting the data of the file displayed as in step-3. what should i do. please reply.

    1. thatjeffsmith Post
      Author

      Can you send me your file for me to test with?

      Can you use the feature with ANY Excel file, or are you just having problems with one in particular. For example, can you export SCOTT.EMP and use that as a test? If that doesn’t work, then there’s something wrong or off with your environment or install.

      1. can u give ur mail id so that i can send the excel file. no other excel files are also imported.
        while importing i am not getting the import method option between data preview and columns. i didn’t get the insert option which u showed in step-4 . But still i tried and all the columns are chosen and in column definition verify is also done and shown success. i then clicked the finish option but no values are inserted into my table. please help me. i need it. as i have a lot of excel files to import data into oracle database.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      Something’s going on, but I’m not sure what. I’m on vacation this week. Can you post this issue to our OTN Forums?

      Be sure to include all the details of your scenario, version of SQL Developer, Java, Client OS, Oracle Database, etc.

  58. Hi,
    I have an issue at step 4. In printscreen appears 2 import methods: “Insert” and “Insert Script”,
    These options are not available for me. In fact there is no option available and I can pass to the next step
    SQL developer 3.2.20.09

    1. One more thing. I have setup two different database.
      For one (test environment) it works.For the other (production environment) I have this issue. Which make me think is about giving certain set of rights/permissions to be granted by my DBA?

      1. thatjeffsmith Post
        Author
  59. Hi Jeff,
    Thanks for the post.
    Though I knew about this feature but I did not have to use it until today. And now I cannot get it to work. Things work fine until I click the ‘Finish’ button after which nothing seems to happen. The data does not get imported, nor do I get any error message. Tried with different tables and different Import Methods too, but it does not seem to work.

    SQL Developer: 3.2.20_09
    OS: Windows 7

    Will be sending you a mail with the csv file. Though I doubt it is an issue with the file since it contains only a single column with three rows.

    1. thatjeffsmith Post
      Author

      So got your email, with your table DDL and CSV file. It imported just fine for me. I ended up with 3 rows, the last one being a null;

      I did notice on the preview screen, I needed to change the file encoding from Unicode to ASCII to get the data to display correctly…did you also do this?

      1. Thanks Jeff,
        I did not have to do that since the data was displayed correctly. Though I later did follow this step also but it did not help.
        I have already mailed you the debug log as you suggested. Let’s see if anything comes up.

        1. thatjeffsmith Post
          Author
  60. Thanks a lot. Excel files with huge row count (280K) in my case, this works fine with .csv. I tried with .xlsx file and had issue of slw developer going into hung status.

  61. ” is NULL, i know that. But I am not able to verify where to rectify ?

    Can you please look into the scenario I have explained above and help me out?

  62. Hi

    It was a real valuable imformation. I have one query. If we want to upload SYSDATE in the date field , then how to proceed? Can you help me out here?

    1. thatjeffsmith Post
      Author

      What I normally do in that case is define the DEFAULT value for that column as SYSDATE, then make sure that col value in the spreadsheet is a NULL, then when the load happens, the DB takes care of that for me.

      1. I have done the same thing but its not working out for me… It inserts NULL value in the table. What I found is that, when the internal import query is prepared (INSERT INTO XYZ VALUES (to_date(”,’DD-MON-YY)) it is inserting the NULL value.

        NOTE: I have also defined DEFAULT value as SYSDATE for that column and haven’t passed any value in the excel column for that.

        1. thatjeffsmith Post
          Author

          Run a sample INSERT to your table, passing nothing for your date field. Then query the table. Do you see the SYSDATE time from when you ran the insert in your new record? If not, you haven’t set the DEFAULT property of your column definition for the table correctly.

          1. Thanks.. But I have tried that also…Not working.
            The problem is the internal query which is generated. It takes the null value inside single qoutes(‘ ‘)(to_date(”,’DD-MON-YY’)). There is the problem! If that qoutes (‘ ‘) can be removed it will work.

            Try this. Let the date field in the Excel have some manual date (put some random date) and leave some NULL. Then try to insert. Hope I am able to make you understand my problem… When you do that the data where you have specified the date gets inserted into the table. But the one you left blank will insert NULL value in the table, not the SYSDATE (which is required)….

          2. thatjeffsmith Post
            Author
    1. thatjeffsmith Post
      Author
  63. Hi thatjeffsmith,
    Thank you so much for ur response.Can you pls explain me the steps to setup a sql*plus script to run via OS scheduler and use the spool command to capture the output.As i am beginner to the technology,i am not able to understand the tech terms clearly.

  64. i want to run a sql query everyday at 5.30 pm.Cn u pls help me to do this automatically and place the output in a folder.i use only sql developer to connect to database.But my database does not have scheduler option ?Thanks in advance.

    1. thatjeffsmith Post
      Author

      Every Oracle database has a scheduling option. DBMS_SCHEDULER or DBMS_JOBS. You could also setup a sql*plus script to run via OS scheduler and use the spool command to capture the output.

  65. Thanks Jeff for the walk through.
    I walked along successfully till the end ( with date format issues ) and upon “Finish”

    Problem
    SQL Developer freezes.

    Environment
    I’m on Mac 10.7.5 with SQL Developer 3.0.04

    Is this the JVM settings thing ? I’m importing less than 10 rows (12 columns)

    Your help is much appreciated

    1. thatjeffsmith Post
      Author

      Question #1: Why are you using SQL Developer 3.0.04? Can you upgrade to version 3.2.20_09?

      Something you could try: have SQL Developer generate an INSERT script to the worksheet instead – that’s an option in the wizard. Then run that script to move your data into your table. If it hangs there too, then there’s something funny with your spreadsheet. Feel free to send me your XLS file if you want me to investigate further, [email protected].

      1. First, i upgraded my SQL Developer to 3.2.20.09 Looks good.

        I got the INSERT generated earlier but to analyze or copy the IDE used to freeze immediately. Now i got the INSERT script generated and says

        “ORA-01722: invalid number”

        My XLS is having numbers in couple of columns like, 1, 610, 66945
        While importing during the Data Preview phase i noticed that the values are coming as floating points like 1.0, 610.0, 66945.0

        Then i made the corresponding columns formatted as Text and the Data Preview was all good.

        But the INSERT failed because those floating points came up again. Not sure if this is some very simple setting in my MS Excel 2008 for Mac

        Thank you very much for your response and your help in this regard.

        1. Phani – Did Jeff ever answer this. We ran into this last night when trying to import about 200 records into a payroll table. Same exact thing. I would really appreciate an answer if possible.

          Thank you. Great post.

          1. thatjeffsmith Post
            Author

            Cyleste,

            If you have the wizard create a script instead of run directly against the table/database, what does the INSERT look like for one of the rows?

            Also, what is the data type definition for the table column in question?

  66. Great tutorial,

    But no joy for me.
    I get a error message at step 8

    Any thoughts?

    –Insert failed for rows 1 through 50
    –ORA-04091: Tabel CRP.ITEM wordt gemuteerd en mag niet door trigger/functie worden benaderd.
    ORA-06512: in “CRP.RECORD_ID”, regel 33
    ORA-06512: in “CRP.RECORD_ID_TRIG”, regel 4
    ORA-04088: Fout bij uitvoering van trigger ‘CRP.RECORD_ID_TRIG’.

    1. thatjeffsmith Post
      Author

      The easiest solution would be to disable any INSERT triggers you have on the table. The problem is with the triggers, not with SQL Developer. I’m guessing if you used the SCRIPT vs INSERT option, and ran the script via SQL*Plus you’d see the exact same errors. Is there a DBA you can consult?

    1. thatjeffsmith Post
      Author
  67. Hi Smith,

    I have a samll query, is there any row limit when we try to import the data from excel file. Cause I’m trying to insert 10k records but the process is rolling back.

    1. thatjeffsmith Post
      Author

      What does the log show Waheed? Is SQL Developer freezing/becoming unresponsive during the wizard, or are you getting an error message? Please tell me what you are seeing.

      When importing large amounts of data via Excel, you may need to increase the amount of memory available to SQL Developer via the JVM – that would be the solution if the application becomes non-responsive. Let me know and I’ll send you the directions if necessary.

      1. the message displayed is “Import Data into table TABLE_NAME from the file.xlsx. Task failed and import rolled back.”, I see that the issue is cause because of the erroneous data in the file now it’s resolved

        Thank you very much for your response. ๐Ÿ™‚

  68. Very nice explanation in this post. I have a question, I am using excel 2007 and created a small file with few rows. Each row has data which is linked with other worksheet in same files. During import in SQL Developer, I could not see any row in the table which has linked data with other sheets. I used past link special option here. But if I am using simple data row (without paste link special option) then Import is fine. Any idea how can I fix this problem in file with paste link special option? I will appreciate.

    1. thatjeffsmith Post
      Author

      I’m guessing that’s a failing of the POI Apache library we’re using…which might get updated soon.

      In the meantime, you could work around this by saving your XLS file to a CSV file and do the import from there – which should run even faster.

  69. Thank you for this webiste!
    I would like to know if I can change the defualt path on the “Import Data” menu option after right clicking on a Table.
    I am using version 3.1.07 or Oracle SQL Developer.

    Thanks for your time.

      1. thatjeffsmith Post
        Author
        1. Thanks for the quick reply!
          Right after I submitted the question, I noticed the list of folders on the left that pops up after clicking on the Import Data option and one of them is the folder that I wanted to be my default…so I may not even need to change the default.

          I appreciate your response.

          Mark

  70. I have run into an issue at times when the Data Import Wizard will not deploy. The File-Log section will show that a file is open but the wizard is not displayed. Any ideas as to why this occurs? Thanks!

    1. thatjeffsmith Post
      Author

      Are you working with an XLS or XLSX file? If so, how big is it?

      Could be we’re running out of memory – an issue we know about and are working on.

      You can launch sqldveloper from the bin directory and get a command console to see any error messages that are occuring when you get to that step…or you could just try to increase the amount of memory for SQL Developer in the sqldeveloper.conf file by modifying this line
      AddVMOption -XX:MaxPermSize=256M

      Or you could try saving your xls file as a CSV and trying that – no memory issues with CSV files.

      Or it could be something completely different ๐Ÿ™‚

  71. Thanks! This was great! The only other thing I ran into is that my numbers had commas in them in the csv from when I converted from xls, so I had to modify the csv accordingly. Once I changed the format, it worked great. You saved me a ton of time. Thanks again

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  72. I have inner joy at finding your blog, I’m a big fan of sql developer, I use a Mac at home. I am quite new to Oracle so your posts are very useful. I used to be an avid Excel person but after learning just the basics of sql I know I won’t be using Excel in the same way ever again.

    I’ve just spent nearly two days trying to get 11g2 working on ora6 via virtual box. So your post on getting sql developer working on the Mac was very welcome for my fried brain.

    Keep up the good work!

  73. In trying to follow your example, I cannot proceed past step 4. The “Import Method” list is empty, so I can’t choose Insert or Insert Script. When looking at Tools->Preferences, the Database->Utilities->Import->Import Methods setting shows “Insert” as the default value for both Existing Table Import Method and New Table Import Method.

    Any idea why the list would be empty, and how I might correct this?

    1. thatjeffsmith Post
      Author

      I’m sorry to hear you’re having problems Alex.

      A few questions:

      • What version of SQL Developer are you running?
      • What OS are you running on?
      • What version JDK (java) are you using for SQL Dev?
      • What kind of file is your data being read from? Excel, Delimited?

      If you’d like, you can email me your data file and I can try to reproduce the problem here locally – [email protected]

      1. SQLDeveloper 3.2.20.09.87
        Windows 7
        JDK 1.6.0_37
        Excel file — but it also happens with a regular plain text CSV file.

        I don’t think it has anything to do with the particular data I’m using. I was able to successfully import the same XLS file with dbVisualizer. I also created a dummy csv file with 2 rows of data in it — and that yields the same results (nothing in the import method list) as trying to use the Excel file.

        I’ve even tried importing the data into a different table (in the same schema), and the list is still empty.

        Do I need a certain set of permissions on the Oracle database for this to work in SQLDeveloper? I have a limited-access user id where I can query/insert/delete data but not perform any schema-relatated activities (add/drop tables or columns, etc.). I wouldn’t expect this to be the case, because dbVisualizer works correctly…

        1. I’m having the same issue with SQL Developer 4 on Windows 8.1 64bit. I have two Oracle11g instances. One of them is fine when using the import wizard. The other one has the issue as Alex mentioned. The two instances are basically the same. One is lower level, the other is production. Do you think this is related to permission? Thank you!

          1. thatjeffsmith Post
            Author

            I’d be surprised if it was a privilege issue, but that it works on one and not the other makes me wonder…if you run SQL Developer in debug mode and open the log panel, you can see the queries run across the wire as you run the wizard.

          2. Thanks for your reply, Jeff!
            The problem instance gave me an error “Could not get schema Object:java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist”.
            Instead of digging into this error, I downloaded SQL Developer 3 and it worked. I know it’s not the best solution but it works for now. Thanks so much for your help!

    2. thatjeffsmith Post
      Author

      Some notes from the developer –

      Reading through the code, the only thing I can think of is that maybe his preferences are messed up somehow.

      If there are no errors in the console, the first thing to try is blowing away the preferences.

      Depends on platform, but basically they are in “.sqldeveloper” in the ‘home’ directory.

      check Help->About->Properties to find out where they are
      ide.pref.dir C:\Documents and Settings\user\Application Data\SQL Developer

      Easiest is to delete the directory (export connections somewhere safe 1st)

      BUT if they have multiple versions installed, need to find directory under .sqldeveloper that corresponds to version (e.g., system3.0.03.01)
      then delete at least settings.xml from the 0.ide. directory

      1. Dear Jeff,
        the “import method dropdown empty” problem exists in the 4.0.2.15, build 15.21 too. No multiple versions.
        I did the preferences cleanup, it does not help.

        However I could find the way to reproduce and get rid of it:
        if You do the import with plain user rights, the list is empty, even on your own table.
        If You do that with sysdba rights, the dropdown is working fine.

        Hope it will help.

        Best regards,
        Barna

        1. thatjeffsmith Post
          Author
          1. Hi, I had exactly the same problem. I tried sqldeveloper v 4.1 (4.1.0.18.37) with java JDK 1.8.0_45 and now the dropdown is finally not empty. ๐Ÿ™‚
            Thank you

          2. Dear Jeff,

            Sorry for the delay, had a lot of travel to clients abroad.

            Thank You for Your help, with version 4.1 I can confirm that the problem is solved.

            Best regards,
            Barna

  74. Hello, I am trying to import Data from Excel file to Oracle table. I am using import available in sql developer, but i need to do on daily basis. any ideas, suggestions. please help. Also, pls guide how to use the SQL Loader ? Many thanks, Nilam

    1. thatjeffsmith Post
      Author

      Get it into a SQL*Loader friendly format, like CSV. Then you’ll want to set it up to run in SQL*Loader. That’s an option in the Import wizard. After you’ve stepped through it you’ll be left with a set of files including a script. You’ll want to setup an OS job to run that script on a daily basis, making sure that you also have scripted the daily input file to be placed where the job is going to look for it. I have a post on how to use SQL*Loader with SQL Developer – it’s on my ‘Popular Posts’ widget to the right of the blog content.

      You’ll want to read up on SQL*Loader in our Oracle Docs as well.

  75. That did the trick! I saved it as a CSV and loaded it without additional problems..I only had 260 rows on the spreadsheet to insert.
    Thanks for your help,
    sandy

    1. thatjeffsmith Post
      Author
  76. I’m on sqldeveloper 3.1.07 and using Office 10. I successfully complete the process through the verificaton step (with all successes), click finish and then sqldeveloper either freezes up or I get a popup box that says: Import data into table SWBDEPT from file CampusDirectory.xls . Task failed and import rolled back. It doesn’t give me any more information than that. I’ve tried using both .xlsx and .xls formats with the same results. We are on oracle 11g. Any Ideas?

    1. thatjeffsmith Post
      Author

      My first idea is that the JVM memory is being exhausted. Reading in the data from Excel is kind of expensive today in SQL Developer. How big is your spreadsheet, number of rows?

      As a test, save your XLS as a CSV and try the import. If it works right away, that’s the issue. The temp fix is to increase the memory in our sqldeveloper.conf setup/config file. The long term fix involves an update internally on how we work with Excel – which is coming!

  77. Hi, I hope you can help me. I’ve just started a new job and they are using the Oracle system. Blanket purchase orders are sent out to the supplier with required delivery dates. On a daily basis requests for quicker delivery dates are generated by Oracle. The requests can be downloaded from Oracle into excel and sent to the supplier.
    I need to know if the supplier replies with a new improved delivery date in excel is it possible to upload the new delivery date directly into oracle from excel ? I have asked the people in my department and they have informed me it’s not possible and I have to change the date manually within Oracle. However this is a long process and I would have thought an upload would be possible.

    1. thatjeffsmith Post
      Author

      Of course it’s possible ๐Ÿ™‚ The question is SHOULD you do it this way. Here’s an example of hooking Excel up directly to the database using ODBC
      http://hoopercharles.wordpress.com/2010/01/12/select-from-or-update-a-database-table-based-on-the-contents-of-an-excel-spreadsheet/

      I’m not advocating that you do this, but you can see what Charles is doing and decide if you want to go in that direction.

      You could turn the problem on it’s ear and build a web application using APEX and have the suppliers update their dates directly – even to a staging database where you could then use SQL to update your production system. Or even have a job that does that for you auto-magically.

      But yes, it’s possible.

  78. Hey,

    So I was following this. I’ve just been coming to grips with Oracle for an internship.

    I get a ‘ORA 00928: missing SELECT keyword’ error for every record and nothing gets inserted into the database. I cleared the date values and matched columns.

    According to Oracle Help, this happens when I’m inserting into a view without selecting. But here, quite obviously, I’m inserting into a table.

    Any ideas?

    1. thatjeffsmith Post
      Author
      1. Turns out I was running an older version of SQL Developer. Switching to a newer version magically fixed that.
        Also, is there any way of directly importing DBF files in a similar manner?
        The only way I’ve figured out till now is opening each file in excel, saving as xls/xlsx and then doing this. I have multiple files and was wondering if you could point me in the right direction. Writing a script maybe?

  79. I need to convert some legacy VFP6 data files. I know we can use Excel to convert it to *.csv files. But, how we convert data from Excel *.csv files into brand new Oracle tables (from scratch)? I am new to Oracle, so be kind with me. I need a very basic, step by step sample.
    Thanks

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  80. This is very informative.
    I have a question though. How to do a weekly import with out going through all the steps? Automatic importing , I should say.

    1. thatjeffsmith Post
      Author

      First we walk, then we run.

      Something we’re kicking the tires on is a more powerful command-line interface. This would allow you to call SQLDev features via your scripts, which you could then schedule via your native OS scheduling interface.

      If it’s a regular activity and you want to automate it today, something you may want to look at is using SQL*Loader. Until we have what you want in SQL Developer, you could define your ctl file for your Excel data – which would need to be converted to a CSV, then you could achieve what you want.

      Thanks for the feedback Scott!

      1. Thanks for the reply!
        I hope this is not a lot to ask but would you mind walking me into the process?
        What should I do first? I’m not well learned with oracle/sql stuff.

    2. thatjeffsmith Post
      Author

      It may require a bit more coding than you’re comfortable at this time, but if you’re willing to to a bit of research, it WILL work. Try reading this FAQ first to get an idea of what’s involved. http://www.orafaq.com/wiki/SQL*Loader_FAQ

      Once you define what the data stream looks like, you can use windows or *NIX scheduling to run your script on demand. And it will be FAST.

      There are MANY utilities out there for loading Excel data to Oracle. They are not free.

      This process will be free, but it will require more work.

      And the process will continue to evolve and get better in SQL Developer of course!

      1. Thanks a lot! I’ll read and study more and hopefully get a sufficient knowledge to tackle this.

        * Programming is fun when you know what the heck your doing! lol.

      1. thatjeffsmith Post
        Author

Leave a Reply

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