VB.net  Excel Add-in SQL Data Cleansing tool

Posted on 2014-10-07
Medium Priority
Last Modified: 2014-10-10

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
Question by:Murray Brown
LVL 24

Expert Comment

by:Phillip Burton
ID: 40365601
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.
LVL 83

Expert Comment

ID: 40365604
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.

Author Comment

by:Murray Brown
ID: 40365607
I forgot to mention that the table sizes are several million at a time
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

LVL 24

Expert Comment

by:Phillip Burton
ID: 40365609
My answer still stands.

Author Comment

by:Murray Brown
ID: 40365679
Thanks but I am specifically looking to build this myself
LVL 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 40365789
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.

Author Closing Comment

by:Murray Brown
ID: 40372381
Thanks very much Jim

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question