excel to oracle db using VS 2005 C#

Question

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:

1. Read excel sheets(n number)?(Best way)
2. Validate data?
3. Bulk insert into DB?

My concern is performance here. Each sheet can have 200,000+ rows.

PS - please remember I am a complete newbie to oracle.

Answer 1

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

Answer 2

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.

Office Development with Visual Studio

Answer 3

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.

Answer 4

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

Answer 5

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:

Creating Excel spreadsheets .XLS and .XLSX in C#