Solved

VB.net  Excel Add-in SQL Data Cleansing tool

Posted on 2014-10-07
7
357 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

759 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

24 Experts available now in Live!

Get 1:1 Help Now