How to insert large amounts of data from excel spreadsheet into Oracle SQL Developer

Question

I have been able to load smaller amounts of data from Excel sheets into Tables in Oracle SQL Developer.

I am having a tough time loading the data from this one excel sheet into the tables. The Excel Spreadsheet has 20 columns and 205,000 rows.

It does not let me import data into the table, complains about java heap space being too large or the error message below

So I make an insert statement in the excel sheet, when I copy and paste it in the SQL Developer, it says the selection is too large to paste.

How do folks in big corporations handle this situation, I am sure they have Millions of rows? There has to be some kind of technique to load this data into the tables.

I tried Youtube and Google, almost every one of them shows videos of Importing data from Excel into a table in Oracle SQL developer with 20-100 rows and 3 columns. Which is pretty straightforward.

Any suggestions, links, videos will help.

Answer 1

You've got two options, @eaolson has already discussed the first.

Second option, save your Excel file as a CSV. It'll rip through the CSV MUCH faster than Excel.

Why is is faster? An Excel file is actually an archive of multiple XML files. Parsing, reading those are a pain.

That being said, I've imported 1,000,000 rows from Excel to Oracle using SQLDev w/o making any changes to the JVM before.

Having lots of columns and lots of rows, will add up quickly. If your'e going to be doing this multiple times, you can automate this with SQLcl and the LOAD command (assuming you save your Excel to CSV/delimited text files).