Question

Locked

How to load data from excel into Oracle Table using PL/SQL

By Matip.Mpom ·
How to load data from excel into Oracle Table using PL/SQL

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Google it man

by feral In reply to How to load data from exc ...

Im no Oracle guru however I queried what you are trying to do with google and came up with this link for you.
http://orafaq.com/faqloadr.htm
Hope that helps, mind you if you had tried this in the first place you might not have had to wait so long for a solution

Collapse -

Excel 2 Oracle

by hsteeman In reply to Google it man

There are many possibilities to do that, but probably the easiest one is via a databaselink from Oracle to Excel, after configuring ODBC (Open DataBase Connectivity). More details can be found on: http://www.e-ammar.com/Oracle_TIPS/HS/configuring_generic_database_con.htm

Good luck!

Collapse -

hope this helps

by gashpole In reply to Google it man

1. The first thing you need to do is save
the Exel spreadsheet as a .csv file.

2. Then create a table to hold the data
within the Oracle database you are trying
to insert into.

3. Copy the csv file to the local server
where the database is

4. Create a control file similar to the one
below:
LOAD DATA
INFILE '<path and file name of csv file>'
INTO TABLE <your table name>
FIELDS TERMINATED BY ','
(
feature_name CHAR OPTIONALLY ENCLOSED BY '"',
feature_type CHAR,
county CHAR,
latitude CHAR,
longitude CHAR,
elevation INTEGER EXTERNAL,
update_time DATE "YYYYMMDDHH24MI"
)
replace column names with correct column names and data types for your table. Remember that you need to use char instead of varchar 2 and integer for number columns save your control file as name.ctl

5. Once you have created this file
you need to start sql loader from the command line like this:
sqlldr username/password@connect_string control=ctl_file.ctl log=log.log
you should see the rows inserted and commited as they load


I have only briefly outlined the details of how to do this here. for a better overview and training on sql loader have a look at this link

http://examples.oreilly.com/orsqlloader/

like I say hope this helps

Collapse -

Stage Excel to CSV then open file

by ToR24 In reply to How to load data from exc ...

I do not believe there is an easy way to read an Excel file directly into an Oracle database using PL/SQL, especially in its native format.

I use three different methods, depending upon the amount of data, the repetitiveness of the process, and the amount of programming I am willing to invest.

First and easiest for the one-offs. I use the SQL*Loader method via Enterprise Manager (Maintenance, Data Movement, Move Row Data, Load Data from User Files) for the ad-hoc imports. You still need to save a copy of your spreadsheet in a text format like CSV. You will also need to stage the data in a place where the database can read it. I use EM because it walks me through the process. Once I've gone through the EM process, I save the control file, tweak it as needed, and reuse it in SQL*Plus scripts.

Second method, where I have full control of the spreadsheet, but less control of the data because users send me the spreadsheets back with data. I create another worksheet within the same Excel file, which has locked down INSERT statements referring back to the sheet with the data. When I receive the spreadsheet, I copy and paste the INSERT statements directly into SQL*Plus, or indirectly staging them in a SQL script.

The last method, one used rarely, is to use ODBC and Microsoft Query to get the data, then save the sheet of the Excel spreadsheet with changes as a CSV file. Next use procedures and functions in the UTL_FILE package to read each line, parse the line, convert the datatype if needed, validate the data and use the INSERT command to put the data into the appropriate table and column. Your database must allow at least read access to the location specified by the utl_file_dir parameter. Take extra care in saving the Excel data, once for the native format and once for the CSV format if you want to stage your changes with a fallback. I use this method for automated reading of data.

http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref14093

Back to Software Forum
5 total posts (Page 1 of 1)  

Software Forums