Solved

VB.net  Excel Add-in SQL Data Cleansing tool

Posted on 2014-10-07
7
388 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Murray Brown
ID: 40365607
I forgot to mention that the table sizes are several million at a time
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 24

Expert Comment

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

Author Comment

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

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:Murray Brown
ID: 40372381
Thanks very much Jim
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

627 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