Home » Developer & Programmer » Precompilers, OCI & OCCI » loading data from excel to oracle table
loading data from excel to oracle table [message #93924] Sat, 05 April 2003 02:18 Go to next message
Afrose
Messages: 1
Registered: April 2003
Junior Member
Can you help me for loading data from excel sheet to oracle table

regards
Afrose
Re: loading data from excel to oracle table [message #94015 is a reply to message #93924] Sun, 31 August 2003 00:46 Go to previous messageGo to next message
usman muzaffar
Messages: 1
Registered: August 2003
Junior Member
save the file as CSV comma delimated in text format and then by using SQL Loader u can easily tranfer the data. if any confusion then mail me at my e-mail.

Ok Boy.
Re: loading data from excel to oracle table [message #94316 is a reply to message #93924] Fri, 23 July 2004 04:13 Go to previous messageGo to next message
mohan
Messages: 16
Registered: August 2001
Junior Member
There are two ways to load Excel into Oracle:

1.Through a Comma-Separated Values file. This method gives more control over the Oracle table created.
2.Through a dBASE file. This method is a bit quicker, IF your data is very orderly.

Excel -> CSV -> Oracle

1.Save the Excel spreadsheet as file type 'CSV' (Comma-Separated Values).
2.Transfer the .csv file to the Oracle server (usually HP Unix).
3.Create the Oracle table, using the SQL CREATE TABLE statement to define the table's column lengths and types. Here's an example of an sqlplus 'CREATE
TABLE' statement:

CREATE TABLE SPECIES_RATINGS
(SPECIES VARCHAR2(10),
COUNT NUMBER,
RATING VARCHARC2(1));

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

load data
infile spec_rat.csv
replace
into table species_ratings
fields terminated by ','
(species,count,rating)

5.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

This will create a log file <filename>.log. Check it for loading errors.
6.Use these sqlplus commands to check the Oracle table:

DESCRIBE SPECIES_RATINGS;
SELECT COUNT(*) FROM SPECIES_RATINGS;
SELECT * FROM SPECIES_RATINGS WHERE ROWNUM < 6;
Re: loading data from excel to oracle table [message #94472 is a reply to message #94015] Fri, 10 December 2004 02:52 Go to previous message
raman
Messages: 66
Registered: February 2000
Member
Transferring Excel and dBASE files to/from Oracle
There are a number of methods for transferring data between Excel, dBASE, and Oracle.
Loading Oracle tables into Excel
Oracle tables can be loaded into Oracle in two ways:
Through a Comma-Separated Values file.
Through Oracle GLUE. This is an Excel add-in that can read Oracle tables.
Oracle -> CSV -> Excel
Export the Oracle table as a .csv file, using sqlplus commands. Here's an example:
spool species.csv
set termout off
set pagesize 0
set heading off
set feedback off
select species||','||count||','|| rating from species_ratings;
quit

Transfer the .csv file from the Oracle server (usually HP Unix) to your Excel platform (PC or Mac).
Open the CSV file as an Excel spreadsheet.
Loading dBASE files into Oracle
Jerry Horel has written a Unix utility 'dbf2ora', which converts any old dBASE .dbf file into an Oracle table. It creates an Oracle table with the same name as the .dbf file, and the same column names and definitions. This is simple for rote replication of a dBASE table into Oracle.
If you type 'dbf2ora' at the Unix command prompt, you should get the syntax in return. The syntax is :

nanux1:> dbf2ora oracle_username oracle_password filename.dbf

you will get an Oracle table named "filename". Check the field types and widths of the new table in sql*plus with the 'DESCRIBE tablename' command after it has run.

Loading Excel files into Oracle
There are two ways to load Excel into Oracle:
Through a Comma-Separated Values file. This method gives more control over the Oracle table created.
Through a dBASE file. This method is a bit quicker, IF your data is very orderly.
Excel -> CSV -> Oracle
Save the Excel spreadsheet as file type 'CSV' (Comma-Separated Values).
Transfer the .csv file to the Oracle server (usually HP Unix).
Create the Oracle table, using the SQL CREATE TABLE statement to define the table's column lengths and types. Here's an example of an sqlplus 'CREATE TABLE' statement:
CREATE TABLE SPECIES_RATINGS
(SPECIES VARCHAR2(10),
COUNT NUMBER,
RATING VARCHARC2(1));

Use sqlload to load the .csv file into the Oracle table. Create a sqlload control file like this:
load data
infile spec_rat.csv
replace
into table species_ratings
fields terminated by ','
(species,count,rating)

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

This will create a log file <filename>.log. Check it for loading errors.
Use these sqlplus commands to check the Oracle table:
DESCRIBE SPECIES_RATINGS;
SELECT COUNT(*) FROM SPECIES_RATINGS;
SELECT * FROM SPECIES_RATINGS WHERE ROWNUM < 6;

You're done.
Excel -> dBASE -> Oracle
dbf2ora can also be used to load an Excel spreadsheet into Oracle, by getting Excel to export it as a .dbf (dBASE) file first. This procedure is tricky, as Excel does not carry explicit data type (eg char/numeric) or format (eg column width) information on its columns. If you don't do it just right, the dBASE file will not have the appropriate column definitions, and neither will the Oracle table.
Therefore, to create a dBASE file, Excel must use some rules of thumb to make up the column definitions in dBASE :

The first row of the spreadsheet must contain names for the column (field) names. Avoid unusual punctuation.
The second row must start the data. DO NOT leave a blank row.
The cells in the second row should have the character type you want. (eg, don't put alpha strings in a numeric field). If a cell in the second row contains an alpha character, Excel will create that column in dBASE as alphanumeric.
Do not use the string "NULL" in numeric fields. "NULL" is an alpha string, so if it was in the second row of the table, it would force the column to alphanumeric. If it was in another row, it would generate an error.
If a cell in a numeric field is empty in the second row (that is, the first data row), enter an "impossible" value , say -9999, to force the column to numeric. I would assume you would remove this -9999 value later on in Oracle. If you leave the cell blank, it will come across as character data type.
(I am not 100% sure you need to do the following column manipulations -- you could skip at first and then start over if you don't get the right field widths and number of decimal places)

Save each column to the desired width. This will define the field width later in Oracle. Use the "Format - column width" button.
Set the numeric columns to the desired number of decimal places with the "Format -- number -- 0.00" button.
Set the column allignment to general with the "Format -alignment" button. Choose general (avoid centered, left or right).
After these manipulations, save the file as DBF3 (Dbase 3). The file is now ready to use in 'dbf2ora' in UNIX.
Previous Topic: Oracle 8i on Tru64 generates "WARNING libaio" - Urgent Help needed
Next Topic: double variable problem
Goto Forum:
  


Current Time: Thu Mar 28 05:14:09 CDT 2024