Delete Data from Excel

Question

Delete Data from Excel

parth83.rawal

SSCommitted

Points: 1858
More actions
October 7, 2009 at 2:09 am

#226865

Hi,
I have 1 excel in C drive which is i am writing into it, and sending as attachment using SQL server procedure,
Now when my procedure loops through the Cursor again for 2nd mail to send;
First it should delete all the data from Excel and then again Write the data to Excel and send as attachment to the user.
I am not able to delete the data.
Thanks
Parth

Viewing 9 posts - 1 through 8 (of 8 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

can you provide the code that you are using to delete from the excel spreadsheet? Then we can help you figure out what's going wrong.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

parth83.rawal SSCommitted Points: 1858 More actions · Answer 2

Delete openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\MOMA.xls;','Select * from [sheet1$]')

above code we are using for deleting data.

starunit SSCommitted Points: 1817 More actions · Answer 3

parth83.rawal (10/8/2009)
Delete openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\MOMA.xls;','Select * from [sheet1$]')
above code we are using for deleting data.

I'm sorry i cannot test (my crappy Vista machine isn't allowing OPENROWSET today...) but, don't you need a 'FROM' keyword in there between DELETE and OPENROWSET?

Or perhaps you can change the inner statement to 'DELETE FROM [sheet1$]'?

Good Luck,

Mark
Just a cog in the wheel.

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 4

Are you getting an error? If so, what is it?

A potential workflow alternative may be to:

-delete the file

-copy from a template to make a new file

-write your data

parth83.rawal SSCommitted Points: 1858 More actions · Answer 5

If I delete the file, and create a new one how am i gonna write my data as my Excel which i am sending is containing some macro and Columns with some names so there are some dependencies so how to avoid such things ..!!?

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 6

Copy from a template file where your template has all the formatting and macros you need.

Untested code

DECLARE @cmd VARCHAR(1000)

DECLARE @TemplateFile VARCHAR(200)

SET @TemplateFile = 'Template.xls '

DECLARE @DestinationFile VARCHAR(200)

SET @DestinationFile = 'MyFile.xls '

DECLARE @FilePath VARCHAR(160)

SET @FilePath = '\\Server1\MyFolder\'

SET @cmd = 'COPY ' + @FilePath ++@TemplateFile + @FilePath + @DestinationFile

print @cmd

--Then write to the file you just created using openrowset...

le_billy Old Hand Points: 312 More actions · Answer 7

Great idea, I was having and exact issue. I will try it. 🙂

Billy Le

le_billy Old Hand Points: 312 More actions · Answer 8

Great idea emily-1119612!!! Kudos to you.

It work it work!!! for what it is that I am doing :).

I was setting up a SSIS package where it import data from an Excel spreadsheet into Table A then run a query to join 2 table A and B together then export to Excel Result_Output.xls, but I need my Result_Output.xls to be empty for the next run or it will just append the data, I was trying all different syntax to delete the record from the Result_Output.xls spreadsheet but to avail, you gave me the idea of creating an extra step in SSIS to copy over the template :).

thank you thank you,

Billy Le