0

I have a client who has a huge Excel file. They absolutely want to continue to work with this file. They asked us if we can update data in the file from a PocketPC.

I created a linked server to the spreadsheet:

EXEC master.dbo.sp_addlinkedserver 
    @server = N'ExcelFile', 
    @srvproduct=N'Excel', 
    @provider=N'Microsoft.ACE.OLEDB.12.0', 
    @datasrc=N'Filename.xls', 
    @provstr=N'Excel 12.0;IMEX=1;HDR=YES;'

I can successfully query the file with the following:

SELECT * 
FROM ExcelFile...[Feuil1$]

If the file is already open, I get an error. I guess the file MUST be closed?

Anyway, is there a way to update cells in the Excel file with something like:

UPDATE ExcelFile...[Feuil1$]
SET [BIP] = 123456
WHERE [BIP] = '966985'

I get this error:

An error occurred while preparing the query "UPDATE Feuil1$ set BIP = (1.234560000000000e+005) WHERE BIP=(9.669850000000000e+005)" for execution against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelFile".

Thanks for your time and help

pnuts
  • 58,317
  • 11
  • 87
  • 139
Dominic
  • 159
  • 1
  • 3
  • 13

0 Answers0