i have excel file which have following column NAME RICE SUGAR TEA.i want to import all data into my oracle database please see image below.
2 Answers
Assuming your data is in the range A2:D5 then you can put the formula below into E3 and copy it across the range E3:G5:
="INSERT INTO table_name ( name, item, quantity ) VALUES ( '" & $A3 & "', '" & B$2 & "', " & B3 & ");"
Then just copy the generated SQL statements and run them in Oracle.
You could also import the data using SQL Developer and then use a query with UNPIVOT to transform columns to rows but for this small a volume of data I would not bother.
Something like:
SELECT name, item, quantity
FROM your_imported_table
UNPIVOT ( quantity FOR item IN ( RICE, TEA, SUGAR ) )
Comments
Why you want to import data from excel to Oracle. Create external table using excel and use it. It is best way to use new feature of Oracle. You can join both table for running report or queries. If you change excel (or dump more data in excel sheet) then no need to create again external table because it automatically takes effect.
3 Comments
Littlefoot
Could you, please, post an example which shows how to do that? I'm not sure that external table is capable of working with native Excel file (XLS(X)). No problem when saved as a CSV, but XLS? I'd like to see it, if it's not a problem. As of a new feature: what is a new feature? External tables? If so, well, they were introduced in Oracle 9i in 2003 so - that's not really a new feature.
doc123
How can you create external table using excel spreadsheet. You can find out from following link...blogs.oracle.com/warehousebuilder/…
Littlefoot
Thank you, I read it. It proves you wrong. In order to use native Excel files, you need Oracle Warehouse Builder using Heterogeneous Services. In order to use external tables, those XLS(X) files have to be saved as a CSV.
Excelquestion thanOracle question...unless you import the first table 'as is' and then transform it.