how to copy data from excel to oracle forms

hi all
i want to copy the data of excel sheet into my oracle form
regardsExcel -> 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;
You're done Hakuna mattata.
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;
You're done Hakuna mattata.
Try this and reply soon
hi
I am using Windows XP operating system. I want to copy data from excel to oracle. Is there any way...
with regards,
chaithanya
you can try this utility from sourceforge
http://sourceforge.net/projects/quickload
If the oracle is on unix server and the excell file is on my desktop which runs on windows, where will the location of parfile, logs be and how will i specify the path for excel file on unix from my windows.
hakuna Matata rafiki
hello
you can use ODBC to configure an Excel sheet as if it is an Oracle table using Database link. Then you just create a view or run a normal SQL query to show the data on your form
It is tested and working
to know how to go about doing the configuration of this technique, i have on my website a step by step help
http://www.e-ammar.com/Oracle_TIPS/HS/configuring_generic_database_con.htm
regardsa
Ammar Sajdi
www.e-ammar.com
oracle consultant
PROCEDURE get_from_xls IS
CONVID PLS_INTEGER;
APPID PLS_INTEGER;
i number;
x number;
v_name VARCHAR2(100);
v_BRN varchar2(10);
v_NO varchar2(10);
OUT_FILR TEXT_IO.FILE_TYPE;
BEGIN
synchronize;
-- Appid := dde.app_begin('C:\Program Files\Microsoft Office\Office\excel.exe C:\ora_xls\creadit.xls',dde.app_mode_minimized);
Appid := dde.app_begin('E:\Program Files\Microsoft Office\Office11\excel.exe C:\oracle_excel\EMP_ALL.xls',dde.app_mode_minimized);
dde.app_focus(appid);
convid := dde.initiate('EXCEL',/*:BLOCK2.SHEET_NAME*/'Sheet1' );
x := 0;
FOR I IN 2..100000 loop
dde.request(convid,'R' || to_char(i) ||'C1',v_brn,dde.cf_text,100000);
dde.request(convid,'R' || to_char(i) ||'C2',v_no,dde.cf_text,100000);
dde.request(convid,'R' || to_char(i) ||'C3',v_name,dde.cf_text,100000);
if substr(v_no,1,length(v_no)-2) is null then exit;
end if;
insert into EXCEL_TBL(
CODE ,
NAME )
VALUES(
substr(v_brn,1,length(v_brn)-2),
substr(v_no,1,length(v_no)-2));
x:= x + 1;
end loop;
COMMIT;
dde.terminate(convid);
dde.app_end(appid);
END;
--
Message was edited by:
user575536
hai chaithanya,
am using Windows XP operating system. I want to copy data from excel to oracle. Is there any way...
regard's
polaiah
please!
what is dde?
(dde.app_focus(appid), dde.initiate,dde.request,....)
thanks!
____________________________________
>PROCEDURE get_from_xls IS
>CONVID PLS_INTEGER;
>APPID PLS_INTEGER;
>i number;
>x number;
>v_name VARCHAR2(100);
>v_BRN varchar2(10);
>v_NO varchar2(10);
>OUT_FILR TEXT_IO.FILE_TYPE;
>BEGIN
>synchronize;
>-- Appid := dde.app_begin('C:\Program Files\Microsoft Office\Office\excel.exe >C:\ora_xls\creadit.xls',dde.app_mode_minimized);
>Appid := dde.app_begin('E:\Program Files\Microsoft Office\Office11\excel.exe >C:\oracle_excel\EMP_ALL.xls',dde.app_mode_minimized);
>dde.app_focus(appid);
>convid := dde.initiate('EXCEL',/*:BLOCK2.SHEET_NAME*/'Sheet1' );
>x := 0;
>FOR I IN 2..100000 loop
>dde.request(convid,'R' || to_char(i) ||'C1',v_brn,dde.cf_text,100000);
>dde.request(convid,'R' || to_char(i) ||'C2',v_no,dde.cf_text,100000);
>dde.request(convid,'R' || to_char(i) ||'C3',v_name,dde.cf_text,100000);
>if substr(v_no,1,length(v_no)-2) is null then exit;
>end if;
>insert into EXCEL_TBL(
>CODE ,
>NAME )
>VALUES(
>substr(v_brn,1,length(v_brn)-2),
>substr(v_no,1,length(v_no)-2));
>x:= x + 1;
>end loop;
>COMMIT;
>dde.terminate(convid);
>dde.app_end(appid);
>END;
--
Message was edited by:
user575536
any one explains above Procedure please!
I want to copy data from excel sheets files to Oracle 10g. but the problem is all the columns are not same in excel and my oracle table.
that means, i have to copy 8 out of 10 columns of excel to the oracle table and the rest of 3 columns in the table i have to set it ,may be by update statemnet.
anyone has any idea to copy this kind of excel files to oracle?
Thanks
i have a similar issue that I convert oracle application server to unix platform instead of windows platform, and i'm using dde package to exchange data between oracle and microsoft application,now i cant exchange data
anybody could tell me how to use dde with unix plat form please