0

I am using the below code to export data to excel file. This is working fine when the excel file is blank. But when I update the filed to null then the insert query is executed successfully but the excel is showing blank.

update openrowset('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\..\.xlsx;', 
'select Column1,Column2,Column3 FROM [Sheet1$]')
set  Column1=null,Column2=null,Column3=null


insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\..\.xlsx;', 'SELECT * FROM [Sheet1$]') 
select Column1,Column2,Column3 from table_Name

I want set blank the file first before writing to that.

Kindly help.

KousiK
  • 825
  • 7
  • 17
  • 39

1 Answers1

0

From what I've read online, openrowset is read only so you need to use OPENDATESOURCE instead. Try something like this.

UPDATE OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=E:\...\YourExcelFile.xlsx;;Extended Properties=Excel 12.0')...[Sheet1$]
    SET Column1=null,Column2=null,Column3=null


INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=E:\...\YourExcelFile.xlsx;;Extended Properties=Excel 12.0')...[Sheet1$]
   SELECT Column1,Column2,Column3 from table_Name
Stephan
  • 5,891
  • 1
  • 16
  • 24
  • Its working for first time but during update for second time it's showing OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Bookmark is invalid.". Msg 7346, Level 16, State 2, Line 1 Cannot get the data of the row from the OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". – KousiK Mar 17 '15 at 05:23
  • 1
    Here look at this forum. It should have all the answers you need. http://stackoverflow.com/questions/13888082/ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null-returned-m – Stephan Mar 17 '15 at 13:25