Excel to oracle db using VS 2005 C #

Question

I want to create a utility that can import data from an excel sheet (columns are fixed, but the sheets can be any number) for oracle db. Can you suggest how I can do this:

• Read excel sheets (n number)? (The best way)
• Confirm data?
• Bulk insert in database?

I'm worried about performance here. Each sheet can contain 200,000 lines.

PS - please remember that I am a complete beginner in the oracle.

Answer 1

You can use Microsoft Integration Services and upload files with it

Another way is to convert excel sheets to cvs and download them through Oracle Loader

http://www.orafaq.com/wiki/SQL * Loader_FAQ

Answer 2

SpreadsheetGear for .NET is an Excel compatible component that is easy to use with C # and allows you to get raw raw data from cells or formatted text from each cell.

You can see live examples here and download a free trial here .

Disclaimer: I have SpreadsheetGear LLC

Answer 3

You can directly connect to an Oracle database from Excel using the ODBC driver.

Depending on how complex your data manipulation is, you can use either VBA or .Net Office Interops.

Office Development with Visual Studio

Answer 4

If you just want to quickly download data. You may consider exporting the table to CSV. Then install it in your database as an external table.

You can then use PL / SQL to query and validate the data before inserting it into the main tables.

Answer 5

Take a look at this question in StackOverflow:

Create Excel file (.XLS and .XLSX) with C #

I think you can open your book and process its data, perform verification, etc. You can then use standard SQL inserts to insert data into an Oracle database. It becomes easier when using NPOI.

Performance may not be a problem if you have a fast processor and RAM.

The following article shows how to open a workbook and process data using an ASP.NET MVC application:

Creating Excel.XLS and .XLSX Spreadsheets in C #