none
Insert data into Excel in an exact cell

    Question

  • Hi

    If I do the following, I can update a cell in Excel from SQL Express 2008 with the data I want:

    SET @strsql = 'UPDATE OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @strFileName + ''',''SELECT * FROM [MySheet]'') SET [' + @Column + ']=''' + @Variable + ''' WHERE RowID=''' + @RowID + ''''
    

    Is there a way to insert a range of data into Excel starting at a specific cell, rather than just updating one specific cell?

     

    Cheers

    Friday, October 15, 2010 11:29 AM

Answers

  • Hi,

    The error message is explanatory. Could you please remove “MyFirstFieldName” from your statements? Please try to use the following statements:

    INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',

        'Excel 8.0; HDR=YES; Database=C:\Test.xls;’

        'SELECT * FROM [Test$A10:A100]')

    SELECT MyDBField FROM MyTable

     

    Thanks,

    Ai-Hua Qiu


    Constant dropping wears away a stone.
    • Marked as answer by Ye11ow Tuesday, October 19, 2010 7:33 AM
    Tuesday, October 19, 2010 7:24 AM

All replies

  • use SSIS
    Friday, October 15, 2010 12:00 PM
  • Can I do this in a stored procedure though?
    Friday, October 15, 2010 12:16 PM
  • Hi,

    When we want to insert a range of data into an Excel file starting at a specific cell, we can refer to the following statements:

    --insert data into a 2007-2010 Excel file 

     

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

        'Excel 12.0;Database=E:\Test\TestExcel.xlsx;',

        'SELECT ID FROM [Test$A1:B11]')

    SELECT ID FROM Customer WHERE ID>=7 AND ID<=10

       

    -- insert data into a 97-2003 Excel file

      

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

        'Excel 8.0;HDR=YES;Database=E:\Test\Test.xls;',

        'SELECT ID FROM [Test$A1:B11]')

    SELECT ID FROM Customer WHERE ID>=7 AND ID<=10

     

    For more information, please see:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

     

    Thanks,

    Ai-Hua Qiu


    Constant dropping wears away a stone.
    Monday, October 18, 2010 5:25 AM
  • Hi Ai-Hua Qiu

    Thanks for the reply, but I can't get that to work.

    Say I use this, to insert data starting at cell A10.

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

        'Excel 8.0;HDR=YES;Database=C:\Test.xls;',

        'SELECT MyFirstFieldName FROM [Test$A10:A100]')

    SELECT MyDBField FROM MyTable

     

    Then I get:

    "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" supplied invalid metadata for column "MyFirstFieldName". The data type is not supported."

    Presumably because it doesn't know the data type because I'm starting in cell A10 and not cell A1. 

    Any idea how to get around this?

    Cheers

    Monday, October 18, 2010 11:22 PM
  • Hi,

    The error message is explanatory. Could you please remove “MyFirstFieldName” from your statements? Please try to use the following statements:

    INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',

        'Excel 8.0; HDR=YES; Database=C:\Test.xls;’

        'SELECT * FROM [Test$A10:A100]')

    SELECT MyDBField FROM MyTable

     

    Thanks,

    Ai-Hua Qiu


    Constant dropping wears away a stone.
    • Marked as answer by Ye11ow Tuesday, October 19, 2010 7:33 AM
    Tuesday, October 19, 2010 7:24 AM
  • Brilliant, many thanks.  I now have it working.  Really appreciate your help with this.
    Tuesday, October 19, 2010 7:33 AM