Upload Excel file to Oracle using Oracle SQl Developer

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?

Thanks.

Allen Pitts, Dallas Texas
Allen PittsBusiness analystAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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.
0
 
Allen PittsBusiness analystAuthor Commented:
Hello slightwv,

Saved the Excel .xlsx file to and Excel cvs file and it loaded with no problem.
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.