VB.net Excel Add-in SQL Data Cleansing tool

Hi

I have been asked to build an Excel Add-in where users can clean up data that resides in a SQL database using Excel.
I need some advice on the best way to go around this. I have already looked at ways to pull a limited set of records through. Now I want to develop ways to use Excel and SQL to alter data effectively
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Sounds like you either need the Master Data Services Add in for Excel.

Have a look at http://msdn.microsoft.com/en-us/library/hh231024.aspx for more information.

Alternatively, full information is also available in the official Microsoft 70-463 exam guide, chapters 14-17 and 20.
0
CodeCruiserCommented:
Not a great way of editing data but you can load the data in a sheet including the primary key column (ID). Make the ID column read only and let the user make other changes. Then you can loop through rows, if ID exists, update the record, if ID is empty, insert the row, if ID is missing, delete the row.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
I forgot to mention that the table sizes are several million at a time
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
My answer still stands.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks but I am specifically looking to build this myself
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I have an article out there called Microsoft Excel & SQL Server:  Self service BI to give users the data they want that is a demo of how to connect Excel to a SQL Server Stored Procedure.  

In your case, if 'clean up data' means basic editing without a lot of moving parts, then the same process using a view, or maybe even the base table, would work.

Please spell out in detail what you mean by 'clean up data', as there are a number of other solutions that can be offered, MDS being much more 'industrial' as it can handle cross-database data integrity in a manner that's relatively easy for the users/business SME's to use, but comes at a very high price tag.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.