Excel to Oracle - OraExcel

When I was searching in the web, to figure out a utility that would help me in pushing the Oracle data to Excel, I came to know about OraExcel. OraExcel is a very neat utility through which you can easily send your data in a Excel sheet to a table in Oracle. It has many more features also, but in this short tutorial I will cover how to insert data into a Oracle table using OraExcel. Before you even start trying it out, make sure you have OraExcel installed and you get a good pull down menu like this when you invoke MSExcel.

For inserting a sample data into a table, the procedure goes like this.

Step 1: Click on Oracle -> Connect. You will get a menu like this.

Enter your User Name, Password and Database and Click “OK”. You will get a confirmation message in the Title Bar, telling that the “User” is connected to the “database”. And all those greyed stuff in the earlier screenshot, will be enabled now. So, we are ready to insert some data.

Step -2 : Now, just note that I have data like this. Single column data to start with.

From the diagram above, we can infer that we need to insert data {1,2,3,4,5} into a table TEMP_TABLE with column COL1.

Step -3 : Choose Oracle -> Insert -> Multiple Rows. A window will get popped up like this

Now in the “Table” area specify the location of TEMP_TABLE cell. For Column(s), specify the location of COL1. And in the Data, specify the Data Range for {1,2,3,4,5}

Click OK and you will see this screen. (if everything is successful).

If you rush to see the data with your SQL tool, you are in for some disappointment. We have not “COMMIT”ted the data yet.

Step -4 : Go to Oracle – Transaction – COMMIT. If you would like to ROLLBACK the transaction, you can do from the same menu.

That is it.! We have successfully inserted data from Excel to Oracle. Please note that you can also insert data into multiple columns with this handy tool. Feel free to experiment with it and get your results. Make sure you give Oracle – Disconnect to come out from OraExcel.

If you would like to download your copy, search for OraExcel in google and you are there already!