Import 1 million row Excel to Oracle

I have an excel sheet (office 2016) with about 1 million rows  with just two fileds DocumentNo and Location ( Document No is about 10 character and  Location is about 10-15 character and many locations are blank)
The excel file is about 20MB in size.  I am trying to import to an Oracle database ( Server has 32GB RAM)  But it is not successful .      
Is there a way I can import it?
Sam OZAsked:
Who is Participating?

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

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:
You "can" link Excel directly to Oracle using ODBC but I would save it to a CSV and use SQL*Loader.

If you have SQL Developer or Toad, I've read they can go straight from Excel to Oracle using their import features but I've never used them.
Sam OZAuthor Commented:
I am using Sql Developer . But it fails for huge number of rows
NorieAnalyst Assistant Commented:
How exactly are you importing the data and how is it not successful?

Are the rows that aren't imported those where the Location field/column is empty?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
Yes, please explain "fails".

I would save to CSV and use SQL*Loader.
Mark GeerlingsDatabase AdministratorCommented:
I agree, convert your Excel file to either a fixed-length or a comma-delimited ASCII file, and upload that file to your database server (if you have permission to do that) then create a small control file for SQL Loader and run that on the database server to process your file.

Here are samples of a few SQL Loader control files that we have used in the past:

load data 
infile 'H:\programs\oracle\box_head.txt'
into table vax_box_head
BH_VEN    position (1:10),
BH_NAME position (11:30))

Open in new window

In this example, you put the SQL Loader commands at the top of the data file:
load data infile *
into table xxgen_submit_conc_req_dtl
fields terminated by ","

Open in new window

load data
infile '/g01/working/record_stats.txt'
into table table_rows_upgrade
(OWNER         POSITION( 01:30)

Open in new window

If you don't have permission to run SQL Loader on your database server, you can run this from a client PC *IF* you have the same (or higher) version of the Oracle Client installed as your database server version *AND* your client install includes the "database utilities".  Depending on your network connection, this will run a bit slower than on the server, but for a one-time job, the performance would likely be acceptable.

SQL Loader will create a log file in the directory you run it from.  Review the log file for any errors, and correct the syntax in the controlfile (if that is incorrect) or correct the "bad" data in the "badfile" (that SQL Loader will also create in the directory you run it from), then rename your original data file to something else, and rename the "badfile" to the name of your data file, and re-run SQL Loader to process the corrected records that failed the first time.

Repeat these steps as needed until you have all of the data loaded.

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
Mark GeerlingsDatabase AdministratorCommented:
Have you tried SQL Loader?  Did that work, or give you errors?  Or, did you solve this problem some other way?  Or, are you still looking for other help?
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

From novice to tech pro — start learning today.