SQL Developer won't attempt to import an .xlsx file because it's too large

Question

I have two .xlsx files that total 1.6 million rows, and I'm trying to import these things into SQL Developer.

I right click the table name and select "Import Data..." and then select my file and nothing happens. It logs my attempt to open this file in the "File - Log" output

This is two separate attempts to import the same file logged here. When I click one of them, I get the following message:

However, I know that this warning is not true, because my attempts with importing a smaller .xlsx file are successful. So I figured the problem was just the file size is too large, and tried to change the memory available. I went into "C:\Users\User\Documents\sqldeveloper\sqldeveloper\bin" and changed sqldeveloper.conf to change one existing value to

AddVMOption -XX:MaxPermSize=2048M

and added another value

AddVMOption -Xmx2048M

Which helps the Java VM according to this source: http://codechief.wordpress.com/2008/07/30/configuring-oracle-sql-developer-for-large-files-fix-out-of-memory-errors/

But this did nothing for me, and I still receive the same errors. I am using SQL Developer version 3.2.20.09 but I have also tried this on 4.0.1.14 to the same effect.

Many thanks!

Solution 1

I tried looking into SQL*Loader. Apparently you should be able to right click a table > Import Data > next and there should be an option to generate SQL*Loader files.

Unfortunately, not only did the import wizard not open with my large .xlsx files, the SQL*Loader option was not even present for smaller .xlsx files, or even .xls for that matter.

In the end, I decided to convert my .xlsx files into .csv and import those instead. This worked for all but 4 rows of my 1.6 million, and gave me the insert statement for those 4, of which 2 worked when run with no additional modifications.

Solution 2

In case of large volume of data, add a line "AddVMOption -Xmx4096M" to the sqldeveloper.conf file and even if it is not working change your file to *.csv format instead of *.xls. It will work fine.