• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

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?
0
Sam OZ
Asked:
Sam OZ
1 Solution
 
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.
0
 
Sam OZAuthor Commented:
I am using Sql Developer . But it fails for huge number of rows
0
 
NorieVBA ExpertCommented:
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?
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

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

I would save to CSV and use SQL*Loader.
0
 
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))
TRAILING NULLCOLS

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 ","
(APP_ID, RESPONSIBILITY_ID)

Open in new window


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

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.
0
 
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now