I want to build a utility that can import data from excel sheet(columns are fixed but sheets can be any number) to oracle db. Can you suggest how should I:
My concern is performance here. Each sheet can have 200,000+ rows.
PS - please remember I am a complete newbie to oracle.
You can use Microsoft Integration Services and bulkload the files with it
Another way is to convert the excel sheets into cvs and load them via Oracle Loader
http://www.orafaq.com/wiki/SQL*Loader_FAQ
SpreadsheetGear for .NET is an Excel compatible spreadsheet component which is easy to use from C# and will allow you to get the raw unformatted data from cells, or the formatted text of each cell.
You can see live samples here and download the free trial here.
Disclaimer: I own SpreadsheetGear LLC
It is possible to connect directly to an Oracle database from within Excel using an ODBC driver.
Depending on how complex you data manipulations are you can then use either VBA or the .Net Office Interops.
If you just want to load data very quickly. You could consider exporting the spreadsheet to a CSV. Then mounting it in your database as an External Table.
You could then use PL/SQL to query and validate the data before inserting into your main tables.
Take a look at this question in StackOverflow:
Create Excel (.XLS and .XLSX) file from C#
I think you can open your workbook and process its data, perform validation, etc. You can then use standard SQL inserts to insert the data in Oracle database. This gets easy when you use NPOI.
Performance may not be the problem if you have a fast processor and RAM available.
The following post shows you how to open the workbook and process data using an ASP.NET MVC application:
