Luv u all ,
After long time im blogging ,,,:)
To insert data from excel sheet to oracle there are number of way to do it
1) Using SQL loader ( for huge records more than 1lkh , requires good knowledge about loaders)
2) Creating Insert Script ( simplest and easiest if you no MS Excel upto enough good extent , this will screw the performance if you forget COMMIT ing Transaction after certain count of Insert statements)
3) And Third one is which most easiest i found (Dhan ta Dhyaaaannnnnnn ......)
Using Oraxcel
OraExcel is nothing but an VB Application which executes some MACROS in excel and connects to oracle database and does insert update operations okkkk....
1) download oracle excel extract it in folder
2) Open Excel Sheet of which data needs to be inserted check
3) create a table in oracle with exact same column name as per ur excel sheet .also write that table name in any cell of ur excel.(this will be required in later stage )
4) open oraexcel it will promt you to enable Macros , click on Enable Macros
5) Go to Excel Menu Bar -->Add Ins --> Oracle --> Connect
Add database credentials (uname/password/sid) as per ur TNS file to connect database.
6) Go to Excel Menu Bar -->Add Ins --> Oracle---> Insert ---> Multiple Rows
Click on this and and Add the details of Cells in excel .such as Table Name , Column Names , Data
Please Make the column names same as per ur Oracle Table Column Names. (This is Mendatory).
Check Mark on Promt on Successfull Insert.
Click OK and wait for 3-4 Minutes .
U will receive successful insertion prompt
7) Commit the transaction from Menu Bar -->Add Ins --> Oracle--->Transaction---> COMMIT.
8) Check DB table , u ll find ur data.
Please Refer Below Images , for example.
After long time im blogging ,,,:)
To insert data from excel sheet to oracle there are number of way to do it
1) Using SQL loader ( for huge records more than 1lkh , requires good knowledge about loaders)
2) Creating Insert Script ( simplest and easiest if you no MS Excel upto enough good extent , this will screw the performance if you forget COMMIT ing Transaction after certain count of Insert statements)
3) And Third one is which most easiest i found (Dhan ta Dhyaaaannnnnnn ......)
Using Oraxcel
OraExcel is nothing but an VB Application which executes some MACROS in excel and connects to oracle database and does insert update operations okkkk....
1) download oracle excel extract it in folder
2) Open Excel Sheet of which data needs to be inserted check
3) create a table in oracle with exact same column name as per ur excel sheet .also write that table name in any cell of ur excel.(this will be required in later stage )
4) open oraexcel it will promt you to enable Macros , click on Enable Macros
5) Go to Excel Menu Bar -->Add Ins --> Oracle --> Connect
Add database credentials (uname/password/sid) as per ur TNS file to connect database.
6) Go to Excel Menu Bar -->Add Ins --> Oracle---> Insert ---> Multiple Rows
Click on this and and Add the details of Cells in excel .such as Table Name , Column Names , Data
Please Make the column names same as per ur Oracle Table Column Names. (This is Mendatory).
Check Mark on Promt on Successfull Insert.
Click OK and wait for 3-4 Minutes .
U will receive successful insertion prompt
7) Commit the transaction from Menu Bar -->Add Ins --> Oracle--->Transaction---> COMMIT.
8) Check DB table , u ll find ur data.
Please Refer Below Images , for example.
~~~~~~~~~~~~~~~~~~~~~~~~~THE END ~~~~~~~~~~~~~~~~~~~~~~~~~~
Want Oraxcel ??? mark me a mail please vaibhav2khavare@gmail.com
Thanks ,
Vaibhav Khavare
"NO PAIN , NO GAIN "
1 comment:
Very helpful!! Keep it up buddy :)
Post a Comment