Using Excel to work with SQL data (read/write)

views:

answers:

+1 Q:

Using Excel to work with SQL data (read/write)

I have a ton of data in a sql database which I would like to be able to import and display in excel (I can already do this) and additionally modify or append to the dataset within excel and write the changes/additions back to the database.

What is the best way to go about doing something like this?

Please let me know, thanks!

+1 A:

The way to do this is via Sql Server's DTS/SSIS capabilities. Create SSIS packages for Excel import and export and execute them as needed.

However you still have the issue of people having to share this massive spread sheet. You should consider importing the data into the db permanently and providing a winforms interface for the data entry. You'd be surprised how quickly you could whip out an app with a databound grid view control that would give you decent, Excel-like ability to add/edit/delete table data.

Paul Sasik 2010-04-02 20:57:55

Exactly, I'm migrating the data into the database, so the spreadsheet will be just an tool to input the data. So the spreadsheet will essentially not be so massive anymore. Which language and tools are the best to whip up a small app that has a grid control and the excel abilities you speak about above? VBA? .NET? Please describe some as I am not familiar with any that could accomplish this task. I know some C++ and Java and a tiny amount of VBA.

Tyler Brock 2010-04-02 21:44:54

With some Java background i would suggest that .NET and C# would be the path of least resistance. Look around for sample code too. There's plenty of it out there. And i honestly think that if you approach this the right way you won't have to write more than 20 lines of code. Most of what you want to do is achievable through property settings and database connections in the form designer of Visual Studio 20xx

Paul Sasik 2010-04-02 23:42:23

+1 A:

Although Excel is great at displaying/reporting on data stored within a SQL DB, it has no built-in controls for updating the data.

I would recommend investigating using VBA (Visual Basic for Applications) or based on your coding experience/tools available to you, VSTO (Visual Studio Tools for Office).

This method will allow all of your users to share the spreadsheet at the same time and allow incremental updates plus validation of the data being entered by the user at the point they enter it.

All the usual gotchas apply though - mainly GIGO (Garbage In, Garbage Out). Correctly authenticate your users and what they are allowed to update

CResults 2010-04-02 21:04:24

OK nice, thanks for the response, what kind of language is VSTO? That sounds exactly like something I'd want to look at. Is VBA the best language for this approach?

Tyler Brock 2010-04-02 21:46:08

A good place to start with VSTO is here.. http://msdn.microsoft.com/en-gb/vsto/dd162433.aspx ..right from the source. Other than that, yep, I think VBA would be the best language/approach (YMMV). Here http://en.wikipedia.org/wiki/VSTO is a good comparison between the two approaches.

CResults 2010-04-02 22:24:06

ansaurus

tags:

views:

answers:

Using Excel to work with SQL data (read/write)

related questions