Post reply

Delete Rows from Excel

  • August 14, 2008 at 6:45 am

    #191149

    Hi Team,

    First of all thanks for all the support provided in helping me to achieve the code.

    Would require a small help now.

    I have created jobs which will populate 6 excel sheets in one excel file. This job runs daily. What i would require is a code which will help me to delete the rows in the excel file and then populate it again . If this is not done then what happens is the excel file gets appended.

    Or else if i could get a code which helps me to dynamically create excel file based on the date, it would be appropriate.

    Thanks

    Liju

  • August 14, 2008 at 7:22 am

    #858030

    first, how are you populating the xl sheets? can you post the code? i ask because i'm wondering if there isn't a switch you can use that tells it to overwrite instead of append.

    second, there are a number of ways to accomplish what you want to do if you can't set it to overwrite. you can write a single line into your job/proc/whatever that calls xp_cmdshell with a command to delete the xl sheet (but you'd have to enable xp_cmdshell in the surface area configuration), or you can use xp_cmdshell to rename/move the file into an archive folder, or (probably the best solution) you can write one line into your code to calculate a name with today's date embedded into it, like SELECT @XLFileName = REPLACE(CONVERT(VARCHAR(32), GetDate(), 101), '/', ''), and then use that as the xl filename.

  • August 19, 2008 at 1:39 am

    #859822

    Hi Lenny

    Thanks for replying. Please find your queries answered below.

    The xl sheet is populated in the following way using the code

    insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=D:\Book1.xls;',

    'SELECT * FROM [Sheet1$]')

    select * from testtable

    What i will do is, with my small capability of creating codes, i will try and use your codes and will let you know

    Thanks so much for the reply

    Liju

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply