Load Excel data sheet to Oracle database

Question

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?

Solution 1

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

Solution 2

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

Solution 3

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.

Comments

[alko]

Link is no longer valid. Check docs.oracle.com/cd/E14373_01/user.32/e13370/… for csv lodating details, docs.oracle.com/cd/E14373_01/user.32/e13370/… for ApEx Utilities docs and oracle.com/technetwork/developer-tools/apex/documentation/… for more info about apex

[Tony Andrews]

Thanks, it's valid again now.

[Sreenikethan I]

By the way, this answer's ID is 123456! stackoverflow.com/a/123456

[santosh kumar]

codecollab.io/@proj/LetterSortClass