Update values in an excel sheet | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Update values in an excel sheet


I have an excel sheet that I import into a table with dts, I then compare the values from the sheet with other tables in the database and update a status field with a pass or fail value. I then export the table back to the excel document with the changes. However some of the values from the sheet are lost on the import due to a bug in the isam driver. Is it possible to treat the excel sheet as a table in dts without importing it, and update the status cells on the sheet with a sql query. Thanks
John
May take help of this KBAhttp://support.microsoft.com/default.aspx?scid=KB;en-us;Q195951 and check whether its related. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
This is the query. I think it will work, but I need to reference a table in another database using a different connection than the connection to the excel documnet. I think I have the syntax wrong.
UPDATE [Sheet1$]
SET Status = ‘Pass’
FROM [Sheet1$] AS R, regdatabase.registration.dbo.r2moblist AS L
WHERE R.LES = ‘LES-R2’ AND
R.Status IN (‘New’, ‘Fail’) AND
R.[device id (satellite)] = L.mobile AND
R.[forward id] = L.fid AND
R.[return id] = L.rid AND
R.pin LIKE L.pin
I get an error from the MS JET DB Engine. It thinks the FROM statement is part of the value I want to SET. I doesn’t seem to support the use of FROM in an update query. Is there a way to link this excel doc as a table, or view in the same database without importing it? Thanks
Finally got this to work. I had to add the excel doc as a linked server named excelsource with the command below sp_addlinkedserver ‘excelsource’, ‘jet 4.0’, ‘Microsoft.Jet.OLEDB.4.0’,
‘c: emp
eg_con.xls’, null, ‘excel 5.0’
Then I selected the cells to import and added them to a named range called "excelsource" in excel. This acted as the table name.
I then changed the query to this:
UPDATE excelsource…excelsource
SET Status = ‘Pass’
FROM excelsource…excelsource AS R, regdatabase.registration.dbo.r2moblist AS L
WHERE (R.LES = ‘LES-R2’) AND (R.Status IN (‘New’,’Fail’) AND R.device_id_sat = L.mobile AND R.forward_id = L.fid
AND R.return_id = L.rid AND R.pin LIKE L.pin)
the query bulider in dts will say optional FROM clause not supported but it still works when you parse it.
]]>