Data Access - how to add data from excel sheet to oracle database?

GeekInterview.com > Interview Questions > Oracle > Data Access

Go To First | Previous Question | Next Question

Data Access | Question 20 of 27

how to add data from excel sheet to oracle database?

Total Answers and Comments: 6

Last Update: September 29, 2008 Asked by: ddkdhar

Sponsored Links

Best Rated Answer

—

Submitted by: manoj kumar yadav

though sql loader.....

for example

LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"

Above answer was rated as good by the following members:
ajaz_11i

February 19, 2007 02:36:20

manoj kumar yadav

RE: how to add data from excel sheet to oracle databas...

though sql loader.....

for example

Is this answer useful? Yes | No

1 0

Overall Rating: +1

February 19, 2007 04:38:39

ddkdhar

Member Since: June 2006 Contribution: 48

RE: how to add data from excel sheet to oracle databas...

from text files we can load like this. but i want from excel files to oracle

Is this answer useful? Yes | No

February 20, 2007 05:56:52

Kailash Kanojia

RE: how to add data from excel sheet to oracle databas...

Save your .xls file as .CSV file and use the SQL Loader

Is this answer useful? Yes | No

1 1

Overall Rating: -N/A-

July 09, 2008 00:32:05

ajaz_11i

Member Since: June 2008 Contribution: 4

RE: how to add data from excel sheet to oracle database?

Save the excel data as .cvs file or in notepad file each field is seperated by comma.
then use either SQL Loader, or UTL_FILE Package, & or External Table.....
By Mr. Ajaz Ahmad Kumar

Is this answer useful? Yes | No

July 10, 2008 00:17:08

ammupriyaa

Member Since: July 2008 Contribution: 4

RE: how to add data from excel sheet to oracle database?

Save the excel sheet as test1.csv file with 2 columns empno and ename

create or replace directory data_dir as 'C:DATA';
Place your test1.csv file

create table alpha(EMPNO NUMBER(4),ENAME VARCHAR2(10))
Organization external
(type oracle_loader
default directory data_dir
access parameters (records delimited by newline
fields terminated by ',')
location ('test1.csv'))
PARALLEL
REJECT LIMIT UNLIMITED;

INSERT INTO emp (EMPNO,ENAME) SELECT * FROM alpha;

Please try and tell whether you are getting correct answer or anyone whether mine is correct or not ? if not please correct it.

Is this answer useful? Yes | No

September 29, 2008 06:02:58

ritikaag2002

Member Since: September 2008 Contribution: 1

RE: how to add data from excel sheet to oracle database?

it can be inserted using insert statement :

="insert into R1 values('"&A2&"','"&B2&"','"&C2&"','"&D2&"','"&E2&"','"&F2&"','"&G2&"',"&H2&",'"&I2&"','"&J2&"','"&K2&"','"&L2&"','"&M2&"',"&N2&","&O2&",to_char("&P2&",mm/dd/yy);"
into excel sheet against each row.
It will automaticaly create insert query for the data n a particular table and that insert script can be run on ql prompt.....

Is this answer useful? Yes | No

Go To Top