CODEWITHSUNDEEP
c#excelvsto
Steve Brouillard
Steve Brouillard

Reputation: 3266

Excel Data Processing with VSTO?

I find myself in possession of an Excel Spreadsheet containing about 3,000 rows of data that represent either additions or changes to data that I need to make to an SQL Table. As you can imagine that's a bit too much to handle manually. For a number of reasons beyond my control, I can't simply use an SSIS package or other simpler method to get these changes into the database. The only option I have is to create SQL scripts that will make the changes represented in the spreadsheet to MS SQL 2005.

I have absolutely no experience with Office automation or VSTO. I've tried looking online, but most of the tutorials I've seen seem a bit confusing to me.

So, my thought is that I'd use .NET and VSTO to iterate through the rows of data (or use LINQ, whatever makes sense) and determine if the item involved is an insert or an update item. There is color highlighting in the sheet to show the delta, so I suppose I could use that or I could look up some key data to establish if the entry exists. Once I establish what I'm dealing with, I could call methods that generate a SQL statement that will either insert or update the data. Inserts would be extremely easy, and I could use the delta highlights to determine which fields need to be updated for the update items.

I would be fine with either outputting the SQL to a file, or even adding the test of the SQL for a given row in the final cell of that row.

Any direction to some sample code, examples, how-tos or whatever would lead me in the right direction would be most appreciated. I'm not picky. If there's some tool I'm unaware of or a way to use an existing tool that I haven't thought of to accomplish the basic mission of generating SQL to accomplish the task, then I'm all for it.

If you need any other information feel free to ask.

Cheers,

Steve

Upvotes: 1

Views: 1217

Answers (1)

Doc Brown
Doc Brown

Reputation: 20044

I suggest before trying VSTO, keep things simple and get some experience how to solve such a problem with Excel VBA. IMHO that is the easiest way of learning the Excel object model, especially because you have the macro recorder at hand. You can re-use this knowledge later when you think you have to switch to C#, VSTO or Automation or (better !) Excel DNA.

For Excel VBA, there are lots of tutorials out there, here is one:

http://www.excel-vba.com/excel-vba-contents.htm

If you need to know how to execute arbitrary SQL commands like INSERT or UPDATE within a VBA program, look into this SO post:

Excel VBA to SQL Server without SSIS

Here is another SO post showing how to get data from an SQL server into an Excel spreadsheet:

Accessing SQL Database in Excel-VBA

Upvotes: 3

Related Questions