Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Read data from excel spreadsheet into an Oracle Table

Posted on 2002-04-11
10
Medium Priority
?
71,567 Views
Last Modified: 2012-05-04
How could I write a PLSQL procedure or a simple sql statement so that I could read data in from an Excel spread sheet into an oracle table??? Any examples appreciated!!!

Thanks..................
0
Comment
Question by:luinzi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 4

Accepted Solution

by:
Bhatti earned 360 total points
ID: 6933908
Loading Excel files into Oracle

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;

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


I collect this information, may this will help you.


Best regards


Bhatti
0
 

Author Comment

by:luinzi
ID: 6933949
ok......... The table already exists and has values in it! I'm running SQL plus and this is what I have got so far:

declare
fhandle utl_file.file_type;
fbuffer varchar2(1000);
begin
fhandle := utl_file.fopen (' C:\', 'test.xls', 'w');
while true loop
utl_file.get_line (fhandle, fbuffer);
insert into cntct_prod_desc (PRODUCT_CODE, PRODUCT_DESC)
select (substr(fbuffer,1,instr(fbuffer,','))),
to_number(substr(fbuffer,instr(fbuffer,',')+1))
from dual;
end loop;

end;
/


but it doesn't seem to put anything into the table!!!!

why is that??

Thanks...........
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6933971
The problem maybe in this line:

fhandle := utl_file.fopen (' C:\', 'test.xls', 'w');


You have specified to open the file in write mode with the 'w'.  Try it with 'r', which opens it for read mode.

Andrew
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:luinzi
ID: 6934019
Made the chgange to 'r' still nothing happens and I added some exception handling....... still nothing.....
0
 

Author Comment

by:luinzi
ID: 6934043
Made the chgange to 'r' still nothing happens and I added some exception handling....... still nothing.....
0
 

Author Comment

by:luinzi
ID: 6934048
trying the SQL load but trying the following:

% sqlload userid=usr/pswrd control=<test.ctl>log=<e.log>

returns this ksh error:

ksh: syntax error: `newline or ;' unexpected

why is that???
0
 

Author Comment

by:luinzi
ID: 6934069
trying the SQL load but trying the following:

% sqlload userid=usr/pswrd control=<test.ctl>log=<e.log>

returns this ksh error:

ksh: syntax error: `newline or ;' unexpected

why is that???
0
 

Author Comment

by:luinzi
ID: 6934116
It finally worked!!!!

It was a privledges issue!!! got it sorted now!
0
 

Expert Comment

by:KI108
ID: 7309247
Hi Luinzi

Could you please post your entire code. I have a similar situation where I have to read multiple records of different formats from an excel file into an Oracle table and do some processing in PL/SQL based on those records.

Thanks

KI
0
 

Expert Comment

by:jeyaseelan_ms
ID: 14059253
Any one pls Give me sample codes

I want insert datas from Excel spread sheet without converting CSV.I need pl/sql procedure.
My emailid:jeyaseelan@rediye.net

Thanks for advance
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question