Validating Excel Data via SQL Server 2005

  • I have a complex historical psuedo-application written in Excel (currently Excel 2003). It is to be replaced within the next twelve months (yippee!!). However, in the interim, there are some significant productivity gains to be made if I can validate some of the Excel data against the 'master' data, which resides in an SQL 2005 database.

    Essentially what I have in the Excel application is a purchase order containing a column of product codes. ON average, there are 7 codes per order, but it may be as few as 1 and I have seen the odd one that would have more than 20. I want to both:

    a) validate the product codes entered - codes entered should exist in the SQL database and their status in the SQL database should be 'Current', and

    b) return some further details about the product from the SQL database to the Excel application eg. stock on hand.

    Can anyone help please with:

    a) the recommended approach - should I return the whole product database (600,000 products) to another sheet in the Excel application and then utilise VLOOKUP; should I query the database for the codes therein (ie. Prod1 OR Prod2 OR ....) and then utilise VLOOKUP; or should I write some VB that will loop through the Excel product codes line by line specifically querying the database for just that product.

    b) the VB connection code to utilise in Excel to access the SQL database.

    c) the VB code to return, for example, SOH from the SQL database to another column on the same row in the Excel application.

    Assistance/Guidance on any or all of the above would be greatly appreciated.

  • John,

    I have a conceptual idea of what you want to do. You will have the least amount of headache by working as much as possible within SQL Server, in regards to limiting data and the like. When data is written to Excel versions 2003 and lower, you will hit a little snag at the 65,536 record "limit". Each worksheet will only contain 65,536 rows, and additional data will then be added to additional sheets in the same XLS file. This "ceiling" is moved up to 1,000,000 records per worksheet in Excel 2007. ( http://blogs.msdn.com/excel/archive/2006/08/10/694606.aspx )

    I was recently (unfortunately) working in an Excel 2003 file that has roughly 90,000 records (30 MB in size) and took the local file about 22 minutes to open, and was agonizingly slow to work with.

    --

    You will get more detailed responses/suggestions/etc. by taking a few moments to review the following article, and then post a test case for folk to collectively review.

    Forum Etiquette: How to post data/code on a forum to get the best help

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Happy T-SQLing

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • I tend to agree with Damon - bringing back 600,000 rows, especially when someone is interested in no more than 20, is both difficult and not very scalable.

    More details about what is really being done - how the current process flows, would be very helpful.

    You have 2 distinct tasks outlined - checking if the item is current, and then displaying QOH.

    I'd prompt the user for an item, then use this to display QOH if current, or display a message that item is obsolete.

    Much would hinge on 'when' you want to validate the data. It may be better to spend time on the replacement than patching more workflow into the current Excel application.

    Just my $.02 worth. Hope it gives you an idea or 2.

    Greg E

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply