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
LVL 1
Allen PittsBusiness analystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Allen PittsBusiness analystAuthor Commented:
Hello slightwv,

Saved the Excel .xlsx file to and Excel cvs file and it loaded with no problem.
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.