Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: Possible to Update a table based on Excel?

    Hi all
    I was wondering if it was possible to update a table based off of information from Excel. here is what I though would have worked.

    Code:
    update MyTest Set acctNumber='111' FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\MyFile.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') where [ProductGroup]='Hal Butts'
    with 'Update MyTest' being the table name. It does have the same name as the excel file. Just to rule that out.

    It gives me this error

    Code:
    Ambiguous column name 'ProductGroup'.
    If it is possible what is the correct syntax??

    tibor

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    update MyTest Set acctNumber='111'
       FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'
    ,  'Excel 8.0;Database=C:\MyFile.xls;HDR=YES'
    , 'SELECT * FROM [Sheet1$]') WHERE [ProductGroup]='Hal Butts'
    Isn't complete. Your problem may be in the code that is missing from your post.

    -PatP

  3. #3
    Join Date
    Mar 2006
    Posts
    82
    Well that is all the code I have.

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    update mt
    set mt.acctNumber = '111'
    from MyTest mt
    inner join OPENROWSET('Microsoft.Jet.OLEDB.4.0'
    , 'Excel 8.0;Database=C:\MyFile.xls;HDR=YES'
    , 'SELECT * FROM [Sheet1$]') ors ON mt.joincolumn = ors.joincolumn
    WHERE mt.[ProductGroup]='Hal Butts'

    The above isn't going to work, because I have no idea how you are trying to find the right columns to update based on the OPENROWSET. You need to figure it out though. You have to do something like what I have shown in the example, so you have an executable piece of code that makes sense.

    Since ProductGroup is in both the MyTest table AND the spreadsheet, the UPDATE statement can't figure out which column you are referring to when you reference it.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Mar 2006
    Posts
    82
    great thanks. its seems to be a bit long but works nicely. thank you so much.

    tibor

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    However.....you would be better served by loading the excel file into a staging table and the perform your dml operations....excel is very unpredictable and is open to be modified by end users that could break you process...

    If you do the load, only the load may fail, or if it does load, may fail a data audit the you would write....

    I've come to hate excel
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2006
    Posts
    82
    Well i am still quite newbish to sql. how exactly would i load it to a staging table?

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Use defalt DTS import facility to transfer rows from excel to the table (create this table after assessing the columns on the excel sheet). Then use SELECT INTO to get the required data from staging to main table.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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