none
delete row or sheet in excel from query

    Question

  • i want to delete row in excel file from query because when i insert data in 2nd time, data is not insert in proper place for example

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:\TestDB\first.xls;',
    'SELECT cid, ClassName FROM [Sheet1$]')
    SELECT cid, classname FROM Class
    GO

    now rows insert from top row, it's fine. now i want to execute this again. i want to delete old data and insert new data. for this purpose i use this query

     update openrowset('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\TestDB\first.xls;', 
    'SELECT cid, ClassName FROM [Sheet1$]') 
    set cid=null,
    classname=null
    
    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:\TestDB\first.xls;',
    'SELECT cid, ClassName FROM [Sheet1$]')
    SELECT cid, classname FROM Class
    GO

    Now insert from 60 row not in top row. I want to insert again in top row

    please help thanks

    Saturday, June 14, 2014 9:01 AM

All replies

  • you cant delete row itself from excel using openrowset. you can only clear its contents using UPDATE

    What I did on similar occasions is to drop the table using DROP command and then recreate it and populate the data. I do it inside SSIS package


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Saturday, June 14, 2014 10:22 AM
  • i use this query

    drop openrowset('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\TestDB\first.xls;', 
    'SELECT cid, ClassName FROM [Sheet1$]') 

    its not working 

    or you want to say i should code on development side?

    Saturday, June 14, 2014 11:28 AM
  • Hi,

    As much as I understand (I am almost not working with excel at all) we can not drop a sheet or delete the rows, using OpenRowSet, in a excel, but you can delete all the rows which give you the same afect using UPDATE. 

    Check this link:

    http://aartemiou.blogspot.co.il/2009/02/manipulating-excel-97-2003-worksheets.html

    update OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samples\excel_file_2003.xls;','SELECT * FROM [Sheet1$]')
    set code=NULL, description=NULL

    I hope this answer your needs :-)


    [Personal Site] [Blog] [Facebook]signature

    Saturday, June 14, 2014 12:57 PM
    Moderator
  • I think you should start to create another structure to achieve this. Ssis package...and you should overwrite the xls.

    So, insert the data in a table (SQLAA) and use a package with a simple dataflow to create (option overwrite of course) a new file excel. It's really easy. Google ten minutes "Data flow from sql fill xls file" and you'll got the solution.

    I hope this post helped you

    Monday, June 16, 2014 10:27 AM