Upload Excel file to Oracle using Oracle SQl Developer

Posted on 2018-03-13
Medium Priority
Last Modified: 2018-03-14
Hello expert,

Using Oracle SQL Developer to import large data set from Excel Office 365.
The way it has been done before is to right click on the Tables icon in Connections
navigator, then Import Data. This goes to dialogue box titled 'Open' where a browse
thru the file structure leads to the Excel spreadsheet. Then the import dialogue
goes away and comes back in a few seconds (directly proportional to the number
of records in the Excel file). Then it goes thru a five step process.

The problem is the Excel file is 76K rows  with 13 columns.
The process described above is followed but when it goes to the import dialogue
after choosing the file at the Open screen it never comes back.

But smaller files have been imported many times. So it is surmised that the size
of the file is the issue. So the file is cut in half to 38k. Same result.
File halved again to 19k. No luck. Files halved again to 9k. The
table was created but the SQL Developer says the data failed to load
and the INSERT statement was rolled back. Finally got a file of 4.7k rows to load.

This means after the initial load of 4.7k, fifteen additional files would be
appended. Not an optimum operation.

Is there some way of doing this without 16 loads? Will the number of rows
loadable in a single pass increase by using CSV or other file type?

Is there another process that can be used to import data?


Allen Pitts, Dallas Texas
Question by:Allen Pitts
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 42498361
I've never used SQL Developer to load an Excel file but 76,000 rows doesn't seem all that bad.

How large is the Excel file?
What version of SQL Developer are you using?  If not the latest, can you try the latest?
Can you try from a non-365 version of Excel?

>>in a single pass increase by using CSV or other file type?

Yes, same import procedure but select the CSV file instead of Excel file.

>>Is there another process that can be used to import data?

Yes.  Same CSV file and SQL Loader from the CMD prompt.

If all you have is SQL Developer then you likely don't have SQL Loader installed.

If you can get the CSV file to the database server then you can run it from there.

If not, you will need to download and configure the Instant Client.

Once you get SQL Loader running, it is a pretty simple parameter file to load the data.  There are MANY examples on the web.

If you choose the SQL Loader path and cannot get it working, pose a few rows of sample data and the table definition and we can provide a working parameter file.

Author Closing Comment

by:Allen Pitts
ID: 42498968
Hello slightwv,

Saved the Excel .xlsx file to and Excel cvs file and it loaded with no problem.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question