Solved

VB.net  Excel Add-in SQL Data Cleansing tool

Posted on 2014-10-07
7
363 Views
Last Modified: 2014-10-10
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
0
Comment
Question by:murbro
7 Comments
 
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.
0
 
LVL 83

Expert Comment

by:CodeCruiser
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.
0
 

Author Comment

by:murbro
ID: 40365607
I forgot to mention that the table sizes are several million at a time
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 24

Expert Comment

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

Author Comment

by:murbro
ID: 40365679
Thanks but I am specifically looking to build this myself
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 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.
0
 

Author Closing Comment

by:murbro
ID: 40372381
Thanks very much Jim
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now