How to read, edit and write xls files, and then export to SQL Server

Question

I have an excel file that have the list of contacts( about 10 k of them) that I need to push into my SQL Server database. So, I am writing an .net windows program using visual studio 2008 to read the files, generate random password for each contact, and then push these information in to my SQL Server database. It was easy to handle excel file in 2003 but now my computer have office 2007 in it and things seem to changed. I am digging on Microsoft.Office.Interop.Excel but it is seem to be a lot more complicated than before.

Answer 1

I would take an ETL approach to this:

1. Create an SSIS package to import the data into a staging table in SQL Server
2. Validate and Clean up all imported data
3. Write a SQL Script to randomize passwords for each imported row and call that script / stored proc from the SSIS package as a SQL Task
4. Insert to actual data table from the staging table

If this is something that needs to be done over and over again, I would save the package into SQL Server, and call it from my C# program.

Answer 2

I'm not sure if this will help you, but maybe SSIS (SQL Server Integration Services) would help you.

Appart from that you could try recording a macro in Excel while performing your desired manipulations in Excel and when you are done with your manipulations you could take a look at the recorded macro (ALT + F8) and 'translate' it into your .NET code.

Just a suggestion.

robert.oh.

Answer 3

If it is a one shot kind of deal, you could just format the excel file a little, import that into and access database and then push that back to any database you want.

If you must do it in .net, you can open a excel file through ODBC using SQL...It is a little fragile, but I've used it with success in the past.

Use something like this for a connection string in excel :
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

The worksheet and named section are the table names..HDR=Yes mean that the first row contains the name of the fields. I'll admit it is a little odd. :)