Upload Excel file to Oracle using Oracle SQl Developer
Posted on 2018-03-13
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