SQL Server Performance

Update values in an excel sheet

Discussion in 'SQL Server DTS-Related Questions' started by jdoyle, Oct 23, 2003.

  1. jdoyle New Member

    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.

  2. satya Moderator

  3. jdoyle New Member

    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
    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?


  4. jdoyle New Member

    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.

Share This Page