manoj kumar yadav

• Feb 19th, 2007

 

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"

ddkdhar

 Profile Answers by ddkdhar Questions by ddkdhar

• Feb 19th, 2007

 

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

Kailash Kanojia

• Feb 20th, 2007

 

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

ajaz_11i

 Profile Answers by ajaz_11i 

• Jul 9th, 2008

 

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

ammupriyaa

 Profile Answers by ammupriyaa 

• Jul 10th, 2008

 

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.

ritikaag2002

 Profile Answers by ritikaag2002 

• Sep 29th, 2008

 

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

sudhagargeek

 Profile Answers by sudhagargeek 

• Apr 27th, 2010

 

Try and let me know.. surely it will help you..

1. Create excel sheet and enter column name with data and save sheet as .csv (employee.csv) extension.

2. Open notepad and write theose lines as follows and save it as"filename.ctl"(example load.ctl)
load data
infile employee.csv
into table emp01
fields terminated by ','
optionally enclosed by '"' 
(name,id,salary)

here "emp01" table name that must be created in oracle with same no.of.columns appeared in excel sheet.

3. Run below command in command prompt using sql loader:

sqlldr userid=user_name/password@instants_name  control=load.ctl log="file_name".log  data=employee.csv

                                          or

also follow wat given in the below.. use can easily understand..
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. 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;