7

I am looking for a free tool to load Excel data sheet into an Oracle database. I tried the Oracle SQL developer, but it keeps throwing a NullPointerException. Any ideas?

Marvin
  • 229
  • 1
  • 3
  • 11
whiz
  • 1,063
  • 2
  • 11
  • 19

6 Answers6

13

Excel -> CSV -> Oracle

Save the Excel spreadsheet as file type 'CSV' (Comma-Separated Values).

Transfer the .csv file to the Oracle server.

Create the Oracle table, using the SQL CREATE TABLE statement to define the table's column lengths and types.

Use sqlload to load the .csv file into the Oracle table. Create a sqlload control file like this:

load data
infile theFile.csv
replace
into table theTable
fields terminated by ','
(x,y,z)

Invoke sqlload to read the .csv file into the new table, creating one row in the table for each line in the .csv file. This is done as a Unix command:

% sqlload userid=username/password control=<filename.ctl> log=<filename>.log

OR

If you just want a tool, use QuickLoad

Galwegian
  • 41,475
  • 16
  • 112
  • 158
4

Another way to do Excel -> CSV -> Oracle is using External Tables, first introduced in 9i. External tables let you query a flat file as if it's a table. Behind the scenes Oracle is still using SQL*Loader. There's a solid tutorial here:

http://www.orafaq.com/node/848

jimmyorr
  • 11,178
  • 7
  • 30
  • 21
2

Oracle Application Express, which comes free with Oracle, includes a "Load Spreadsheet Data" utility under:

Utilities > Data Load/Unload > Load > Load Spreadsheet Data

You need to save the spreadsheet as a CSV file first.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • Link is no longer valid. Check https://docs.oracle.com/cd/E14373_01/user.32/e13370/sql_utl.htm#BCEIDADH for csv lodating details, https://docs.oracle.com/cd/E14373_01/user.32/e13370/sql_utl.htm#AEUTL243 for ApEx Utilities docs and http://www.oracle.com/technetwork/developer-tools/apex/documentation/index.html for more info about apex – alko Dec 23 '14 at 16:06
  • Thanks, it's valid again now. – Tony Andrews Dec 23 '14 at 16:58
0

If this is a one time process you may just want to copy and paste the data into a Microsoft access table and do an append query to the oracle table that you have setup through your odbc manager.

0


There are different ways to load excel/csv to oracle database. I am giving those below:
1. Use Toad. Toad gives very fexible option to upload excel. It gives column mapping window as well. Normally from Tools -> Import, you will find the option. For details I can provide full instruction manual.
2. Load to Microsoft Access first and then pass it to Oracle from there.
Step1: There is a tab in access named "External Data" which gives excel upload to access database.
Step2: Once table is created, just click write mouse on the table and choose Export to ODBC DATABASE. It will ask for oracle database connection details. It's is free.
3. Use Oracle SQL Loader. It's a service which have datafile, control file. You need to write configuration. It is used as text/any file load which maintain one pattern.

Hope it helps. If required, I can share more details.

smshafiqulislam
  • 564
  • 6
  • 12
-2

As you mention you are looking for a tool - you might like to check out this Oracle specific video - you can load data from any source -

http://youtu.be/shYiN2pnPbA

Richard Briggs
  • 423
  • 4
  • 14