SQL Server: remove from OPENDATASOURCE

Question

It works:

SELECT * FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source=d:\JobFiles\MyFile.xlsx; Extended properties=Excel 8.0')...MySheet$ 

So does it:

 INSERT INTO OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source=d:\JobFiles\MyFile.xlsx; Extended properties=Excel 8.0')...MySheet$ SELECT * FROM blahblahblah 

So why not?

 DELETE FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source=d:\JobFiles\MyFile.xlsx; Extended properties=Excel 8.0')...MySheet$ 

I did some searches without real luck. In the end, all I need is excel rows deleted before I can insert fresh data, and I want to achieve this only with SQL.

Answer 1

You cannot delete whole lines via OPENDATASOURCE. According to http://support.microsoft.com/kb/257819 :

Delete

You are more limited in deleting Excel data than data from a relational data source. In a relational database, a “row” has no meaning or existence other than a “record”; in an excel sheet, this is not true. You can delete values ​​in fields (cells). However, you cannot:

• Delete the entire entry immediately or you will receive the following error message:

Deleting data in a linked table is not supported by this ISAM.

You can delete a record only by extinguishing the contents of each individual field.

• Delete the value in the cell containing the Excel formula, or you receive the following error message:

In this context, the operation is not allowed.

• You cannot delete the empty spreadsheet table in which the data was deleted, and your recordset will continue to display empty records corresponding to these empty rows.

Answer 2

Thanks to Yuriri - this is what I thought.

For someone else in my situation, this was my “cleanest” workaround: get an empty version of the file (in my case, “MyFileBlank.xlsx”) and follow these steps:

 --delete existing file exec master..xp_cmdshell 'del d:\JobFiles\MyFile.xlsx' go --create new file from blank template exec master..xp_cmdshell 'copy d:\JobFiles\MyFileBlank.xlsx d:\JobFiles\MyFile.xlsx' go INSERT INTO OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source=d:\JobFiles\MyFile.xlsx; Extended properties=Excel 8.0')...MySheet$ SELECT * FROM blahblahblah