Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Rupa
Posting Yak Master
123 Posts |
 Posted - 2008-05-15 : 06:30:32
|
| Hey allI am exporting table rows (based on a query) into an excel file but I don't want to append to the file. I would like to delete the rows that were previously added and then add the new data. The file has column headings and I would like these to exist all the time. I know how to export the data but don't know how to delete 'old' data rows from excel.Any guidance will be highly appreciated.Many thanks,Rupa |
|
|
karthik_padbanaban
Constraint Violating Yak Guru
263 Posts |
Posted - 2008-05-15 : 08:29:59
|
quote:
Originally posted by Rupa
Hey allI am exporting table rows (based on a query) into an excel file but I don't want to append to the file. I would like to delete the rows that were previously added and then add the new data. The file has column headings and I would like these to exist all the time. I know how to export the data but don't know how to delete 'old' data rows from excel.Any guidance will be highly appreciated.Many thanks,Rupa
Jet provider does not support neither truncate or delete. You have 2 options:Have an empty excel template that you clone before the running the dataflow, orUse execute sql task to create a new workbook before running the dataflow try to use the File System Task to copy the XLS file from your template location. |
 |
|
|
Rupa
Posting Yak Master
123 Posts |
Posted - 2008-05-15 : 08:46:35
|
| Thx Karthik...M working on it now..will let u know if I succeed. Many thanks!!Rupa |
|
|
|
Rupa
Posting Yak Master
123 Posts |
Posted - 2008-05-15 : 09:11:08
|
| Sorry to be thick Karthik..Could you please guide me on how to copy the template..I'm a newbie so am still learning.Many thanks,Rupa |
|
|
|
Rupa
Posting Yak Master
123 Posts |
Posted - 2008-05-15 : 09:20:19
|
| Ignore the message Karthik. It's done the job. I used the template process. Many thanks for that.Rupa |
|
|
|
carpel
Starting Member
1 Post |
Posted - 2009-05-03 : 11:33:33
|
How to delete rows in the destination file before inserting new records.I suggest create worksheet Master with only header row and copy the worksheet Master into worksheet to fill every time.Steps to do in a store procedure are:1. Create a “Master.xlsx” worksheet with header columns: Name and Date2. exec master..xp_cmdshell 'copy c:\Master.xlsx c:\test.xlsx'3. INSERT INTO OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'' , ''Excel 12.0 Xml;Database=C:\ test.xlsx;'' , ''SELECT * FROM [foglio1$]'' ) SELECT [Name], GETDATE() as Date FROM msdb.dbo.sysjobsHappy coding by carpel  |
|
|
|
|
|
|
Delete excel destination rows