Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Unanswered: Table Join between Excel Sheet and MS-SQL

    My company has recently transitioned to a centralized Oracle database model.
    For the sake of security, the "powers that be" have also denied any query ability to any central tables. They refuse to create views or any other open tables for people to query.
    Instead, they provide a "tool" which people can use to download data - to Excel Spreadsheets.
    In the past, before this "improvement" lots of users in the local plants were able to query the old system to bring data into spreadsheets for reports, analysis, etc.
    Now the place is jammed to the hilt with linked spreadsheets - people do their "table joins" with linked cells and Excel VLookups (yuk). This is because the "powers that be" still demand that these reports, analysis, etc. be done.

    I am trying to use SQL-DMO to create a table join between one of these Excel Data pulls and a MS SQL Server table in Excel so that I can join without VLookup. IS SQL-DMO the right way to go?

    Has anyone done this? I think I am close, but I don't know how to use the SQL-DMO attached Excel table object I've created in a join. I can't see the object in MS Query. I am not adverse to doing the whole thing in VBA...

    Here is another question. Most of these Data pulls using the "tool" (ball and chain, boat anchor) are done once a day or once a week. Would a better strategy be to create MS SQL server Tables that are dropped and re-written when the data is pulled into Excel? This would mean that the report worksheet does not have to import the Excel Data pull sheet to MS SQL when it updates its query.
    Last edited by robnjay; 10-13-06 at 11:18.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd use DTS to load the excel data into MSSQL tables on a weekly basis, essentially creating a datamart of information that people can actually use.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2005
    Posts
    5
    I guess I'll Look up DTS.
    How does this operate?
    Is this a job on the server?
    One of the problems I have is that the Excel Data pulls are done by individuals, and are not centrally located...

  4. #4
    Join Date
    Feb 2005
    Posts
    5
    Blindman:

    I haven't got a clue how to really use DTS.

    I went into Enterprise Mgr., then DTS and created an "Excel97-2000" link to the spreadsheet file in question.

    Then I fiddled around in there until I got the following code into the SQL Pane.

    Code:
    SELECT * INTO mySQLDb.NewTablename
    FROM 
       (SELECT [zExport$].* FROM  [zExport$]);
    This code actually appeared to run --- once.
    If I try to run it again, it errors out, saying that the table "NewTablename" already exists.
    This table is not visible in Enterprise Manager... Where does it "exist"?

    Do you have an example for using DTS to create the datamart?

    Thanks
    Rob

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Rob, DTS is both complex and powerful. Be forwarned that it is also poorly architected and is itself a hodge-podge of loosely integrated technologies. You will need to avail yourself of the documentation available in Books Online to learn more about DTS. It is not a subject you can learn about in a few posts.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2005
    Posts
    5
    Blindman:

    Thanks for sticking with me....

    I've managed to get a table built that is updated from an Excel sheet using DTS.

    There is one hurdle that I haven't found the answer to. How can I get the DTS package to run as a specific Windows Logon ID (not Mine). In fact, I would prefer to set up a special log on who's password does not expire (we use these for automation - they are otherwise tightly locked down so that the only purpose for their use can be the automated task).

    The user (in our user domain) would run his data export "tool" to leave the file on a server. My created logon would have access to that dir, so it could read the Excel file...

    So, can a DTS package be "run as" using specified credentials? I would schedule the package using SQL Server to run each day and update the table from the file.

    Thanks for your help!!

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In DTS, the connection object allows you to specify a Windows login or a dedicated SQL Server login. I can't recall whether the Windows login uses the login of the service or the login of whatever kicked off the package.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Just a suggestion:

    MS Access works with Excel spreadsheets pretty well. It also connects to SQL Server nicely (or other ODBC connections).

    You can connect to the SQL Server table and see it in spreadsheet format. Then cut-n-paste the entire spreadsheet in.

    Note: Access will cry bloody murder if you do not have a unique identifier column (or set of columns). An identity column works just fine.

    That's a manual process, just to get things going, but it's pretty easy to automate. If you do automate it; just make sure your Access table definitions stay current.

    Sorry I cannot advise regarding DTS either, my experience is only barely more than your own. What little I've used I've found gotcha's all along, but once working, it repeats itself nicely. It's finiky about sequence of events and you it assumes you know full well what you're doing. It'll let you put duplicate entries into an identity column. Because it uses BULK INSERT (I assume) it bypasses integrity rules, (appearently, again, I'm a novice at it).

    Corporate - do they realize that Excel spreadsheets are both security risks and very limited in size and versitility? They should at the very least create a scheduled transfer to Reporting Tables in their Oracle database, that contain only the data they want to expose. Exporting spreadsheets is more appropriate for roving salespeople wanting customer analysis info on their laptop, stuff like that.

    Symathies.
    Last edited by vich; 10-13-06 at 22:13.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •