Delete Rows from Excel

• lijusmail

  Default port

  Points: 1486

  More actions

  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

• JuncTheMayl

  Hall of Fame

  Points: 3912

  More actions

  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.

• lijusmail

  Default port

  Points: 1486

  More actions

  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