Menu
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?
403
You can use Microsoft Excel to access data from a Oracle database using ODBC connector. With ODBC Driver, you can import the data directly into an Excel Spreadsheet and present it as a table.
In Oracle sql developer go to Tables --> select import data ---> select your excel or csv file --> it will display the column --> import the data into a table.
On the Setup tab, under Integration Setup, select Excel Interface. From Excel Interface, and then Upload from Excel, in File, enter the name of the Excel file to upload. You can also select a file by clicking Select, navigating to the file on the Select screen, and then clicking OK. Click Upload.
1) Creating a Table using Create Command2) Import the data from Excel from a specific location3) The new table created in step 1 need to join with some other table4) Export the Data to an Excel file. 1. Issuing any DDL from a pl/sql procedure is a red flag.
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
98
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
33
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.
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
