n this we will explain how to put Excel or text file data into an Oracle table. You can do this easily in SQL Server with a Copy (Ctrl+C) of the data from Excel then open the target table into edit mode and Paste (Ctrl+V). Done.
But this will not work in Oracle.
In an Oracle database you can do it using the import command. I will show you each and every step with snapshots.
Step 1 : Open TOAD.
Step 2 : Write the following query to create a test table:
- Create table TESTTABLE
- (
- NAME varchar2(20),
- Address varchar2(100),
- RollNo integer
- )
Step 3 : Now my table is ready. It’s time to create the data in an Excel file.
Step 4 : Go to Database -> Import -> Table Data.
Step 5 : Select the table you want to import the Excel data to.
Step 6 : Click on show data. Now execute the data button to enable the use. Click on that.
Step 7 : After clicking on Execute Wizard the following screen will open:
Next ->
Step 8 : Now, browse to your Excel file – > Next ->
Next ->
Step 9 : Choose the sheet and click on AutoMap. The AutoMap button will automatically bind your Excel sheet column data with the Oracle table column.
Next ->
Next ->
Here your Excel data has been bound with the table columns. Next ->
Step 10 : Click on Execute to finish the process. It may ask for sheet selection. Click on Okay with the desired sheet.
Here is your uploaded data:
Step 11 : Commit the Task by clicking on the commit button.
Using the preceding procedure you can easily import Excel data. You can also import text flat files data. All steps are nearly the same. The only change is the following step. In this import I need to choose the separator for the flat file. Apart from any separators you can use fixed width. In the attached example I used a comma separator.
I think the procedure is very clear with snapshots. If you have found any mistake in concept, please do comment.
Your comments will make me perfect in the future.
Thanks for reading.
