• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1692
  • Last Modified:

Export table in SQL 2000 to Excel

I'm using SQL 2000 Enterprise Edition and I'm trying to use the IMport / Export wizard to export the contents of a table to excel "but" I need this process to over write the spreadsheet and not append it. In the wizard the option to "Delete all rows" is grayed out. What am I doing wrong. I'm that that great in SQL .. PLease help
0
compdigit44
Asked:
compdigit44
  • 5
  • 4
  • 3
  • +1
1 Solution
 
Patrick MatthewsCommented:
Hello compdigit44,

If you are using DTS, you could try an ActiveX task to clear the worksheet first, with code like
this:

Dim xlApp, xlWb, xlWs

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\folder\subfolder\file.xls")
Set xlWs = xlWb.Worksheets("NameOfSheet")
xlWs.Rows.Delete
xlWb.Save
xlWb.Close
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

Regards,

Patrick
0
 
Jai STech ArchCommented:
do you want to delete the records in your TABLE ? while IMPORTING or delete the records in EXCEL while exporting ?
0
 
compdigit44Author Commented:
I just need to DTS export job to export the contents of the table to excel but clear out all the information first in the spreadsheet so it starts from scratch.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
jindalankushCommented:
During exporting data. you must clear data
Dim xlApp, xlWb, xlWs

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\folder\subfolder\file.xls")
Set xlWs = xlWb.Worksheets("NameOfSheet")
    xlWs .Activate
    xlWs .Cells.Select
    xlWs .Cells.EntireColumn.AutoFit
Selection.ClearContents
xlWb.Save
xlWb.Close
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

then export data  into excel
0
 
Patrick MatthewsCommented:
jindalankush,

Out of curiosity, what exactly did your post add to this thread?  Your content nearly exactly
mirrored what I posted a day and a half earlier, even down to using the same variable
names...

Regards,

Patrick
0
 
compdigit44Author Commented:
Thanks for the reply..Again I'm not that skilled in SQL yet. Where would I input the information the both of you posted? Can you please send me some basic steps? Also why can't the Export wizard just clear the spreadsheet? Thanks again
0
 
Jai STech ArchCommented:
the information that was give before has to be written in a .NET(or equivalent) project...which does not really come with DTS...i am surprised that these codes are being given for a DTS !!!!

i understand that your EXCEL sheet is getting appended with the records each time...see whether there is a option in DTS like "empty sheets" or "delete existing" in the DTS...

after selecting the DESTINATION datasource as EXCEL (in your DTS - wizard)
click EDIT next to the table name and check the option "drop and re-create"(or something similar)...
0
 
Patrick MatthewsCommented:
jaiganeshsrinivasan said:
>>the information that was give before has to be written in a .NET(or equivalent) project...which
>>does not really come with DTS...i am surprised that these codes are being given for a DTS !!!!

The question specified DTS, which is SQL Server 2000.  DTS uses VBScript for its ActiveX tasks,
not VB.net.

SQL Server 2005 uses VB.net for its analogous service, SSIS.
0
 
Patrick MatthewsCommented:
compdigit44 said:
>>Thanks for the reply..Again I'm not that skilled in SQL yet. Where would I input the information
>>the both of you posted?

You would need to create a new DTS package, and the code I provided would go into an ActiveX
step.  After that step executes, you would do another step to transfer the data to Excel.

>>Can you please send me some basic steps?

See above.  The UI is actually relatively intuitive for the simple stuff.

>>Also why can't the Export wizard just clear the spreadsheet? Thanks again

Because the Import/Export Wizard is meant for "quick and dirty" one-off things.  Anything that
needs to be sustainable and/or repeatable requires a little investment up front.
0
 
compdigit44Author Commented:
But how will the DTS package know to run the Active X script before copying the new data to the spreadsheet?
0
 
Patrick MatthewsCommented:
compdigit44,

Because you can configure the DTS package to run the two steps sequentially.

Regards,

Patrick
0
 
compdigit44Author Commented:
Question while poking around the DTS wizard I found the option to "drop and recreate destination table" will this clear the spreadsheet before the data is copied to the spreadsheet again?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now