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

Answer 1

use SSIS

Answer 2

Can I do this in a stored procedure though?

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

Brilliant, many thanks.  I now have it working.  Really appreciate your help with this.