Solved

Data export in SQL Server 7

Posted on 2001-06-20
5
296 Views
Last Modified: 2012-05-04
I'm using MS SQL Server 7

I want to export data from the database to the Excel file.

When DTS Export Wizard asks: "Specify Table Copy or Query" and I select query, is it possible to export data into the several tables (or worksheets) of the Excel file.
0
Comment
Question by:jjylonen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 2

Expert Comment

by:ykchakri
ID: 6210315
It is not possible through wizard. But, you can manipulate using Design package.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 6210321
You can only export 1 table at a time, thus you would need to use 1 DTS Wizard execution per table.
Cheers
0
 
LVL 1

Assisted Solution

by:Galiak_VS
Galiak_VS earned 50 total points
ID: 6210360
You can do a little program in Foxpro or VB and export this in Excel. Look this:

Set decimal to 0
      Local nom_tech,nbr_tech,dstart,dstop,garanty,rangee,resul_5,resul_7,resul_8
      resul_5=0
      resul_7=0
      resul_8=0
      rangee=0
      nom_tech=""
      nbr_tech=0
      dstart=thisform.text1.value
      dstop=thisform.text2.value
*////
   
   FOR nCnt = 1 TO ThisForm.List1.ListCount
      IF ThisForm.List1.Selected(nCnt)
         nom_tech=nom_tech + alltrim(ThisForm.List1.List(nCnt))
      ENDIF
   ENDFOR
   nbr_tech=len(nom_tech)
   nbr_tech=nbr_tech/6
*\\\\

******************************************************************************

* Copier le fichier Excel
*////
copy file c:\vamsql\forms\feuille.xls to c:\feuille_copy.xls
*\\\\

******************************************************************************

*Cr?ation de l'objet pour travailler avec Excel + ouverture
*////
ex=createobject("Excel.application")
ex.visible=.t.
ex.workbooks.open("c:\feuille_copy.xls")
*\\\\

******************************************************************************

*Copier une feuille en Excel (pas utiliser dans le programme)
*////
*ex.sheets(1).copy(ex.sheets(3))
*\\\\

******************************************************************************

*?criture dans la feuille #1
*////
if nbr_tech=0 then
else
rangee=6
ex.sheets(1).select
reste_tech=nom_tech
ex.Cells(1,1) = "Rapport des temps des techniciens pour la p?riode du " + dtoc(dstart) + " au " + dtoc(dstop)
for x2=1 to nbr_tech step 1
aff_tech=left(reste_tech,6)
reste_tech=right(nom_tech,len(reste_tech)-6)
ex.Cells(rangee,2).font.bold=.t.
ex.Cells(rangee,2) = "Technicien : " + aff_tech

rangee=rangee+2
ex.Cells(rangee,1) = "#Bon Travail"
ex.Cells(rangee,2) = "#Clients"
ex.Cells(rangee,3) = "Compagnie"
ex.Cells(rangee,4) = "Temps"
ex.Cells(rangee,5) = "Tarif"
ex.Cells(rangee,6) = "Estim."
ex.Cells(rangee,7) = "Total"
ex.Cells(rangee,8) = "D?but"
ex.Cells(rangee,9) = "Fin"

ex.range(ex.cells(rangee,1),ex.cells(rangee,9)).Font.Bold=.t.
            garanty=thisform.garanty.value
            if garanty=0 then
            SQLEXEC(MyConnection, 'select *, datediff(minute,wotime.dtimein,wotime.dtimeout) as Temps_TOTAL_min from wotime,wowork where wotime.cwono = wowork.cwono and wotime.ctech=?aff_tech and lwarranty=0 and dtimein>=?dstart and dtimeout<=?dstop order by wotime.cwono,wowork.cwono','cur_wotime')
            else
            SQLEXEC(MyConnection, 'select *, datediff(minute,wotime.dtimein,wotime.dtimeout) as Temps_TOTAL_min from wotime,wowork where wotime.cwono = wowork.cwono and wotime.ctech=?aff_tech and dtimein>=?dstart and dtimeout<=?dstop order by wotime.cwono,wowork.cwono','cur_wotime')
            endif
            select cur_wotime
            tot5=0
            tot7=0
            tot8=0
            for x=1 to reccount() step 1
                  go(x)
                  rangee=rangee+1
                  ex.Cells(rangee,1) = cur_wotime.cwono
                  ex.Cells(rangee,2) = cur_wotime.ccustno
                  ex.Cells(rangee,3) = cur_wotime.cbcompany
                  ex.Cells(rangee,8) = left(dtoc(cur_wotime.dtimein),8)
                  ex.Cells(rangee,9) = left(dtoc(cur_wotime.dtimeout),8)
                  ex.Cells(rangee,4) = cur_wotime.Temps_TOTAL_min
                  ex.Cells(rangee,5) = cur_wotime.nrate
                  ex.Cells(rangee,6) = round((cur_wotime.Temps_TOTAL_min/60)*cur_wotime.nrate,0)
                  doubleval=cur_wotime.cwono
                  skip+1
                  if doubleval=cur_wotime.cwono then
                  skip-1
                  else
                  skip-1
                  ex.Cells(rangee,7) = round(cur_wotime.nlabtotal,0)
                  tot8=tot8+cur_wotime.nlabtotal
                  endif
                  
                  tot5=tot5+cur_wotime.Temps_TOTAL_min
                  tot7=tot7+((cur_wotime.Temps_TOTAL_min/60)*cur_wotime.nrate)
                  
            endfor
                  rangee=rangee+1
                  ex.Cells(rangee,1) = "Total"
                  ex.cells(rangee,1).Font.Bold=.t.
                  ex.Cells(rangee,4) = tot5
                  ex.cells(rangee,4).Font.Bold=.t.
                  ex.Cells(rangee,6) = round(tot7,0)
                  ex.cells(rangee,6).Font.Bold=.t.
                  ex.Cells(rangee,7) = round(tot8,0)
                  ex.cells(rangee,7).Font.Bold=.t.
                  rangee=rangee+3
                  resul_5=resul_5+tot5
                  resul_7=resul_7+tot7
                  resul_8=resul_8+tot8
                  select cur_wotime
                  use
endfor
ex.Cells(3,2).font.bold=.t.
ex.Cells(3,2)= "TEMPS TOT"
ex.Cells(3,3).font.bold=.t.
ex.Cells(3,3)= "ESTIM.TOT"
ex.Cells(3,4).font.bold=.t.
ex.Cells(3,4)= "GRAND TOT"
ex.Cells(4,1).font.bold=.t.
ex.Cells(4,1)= "TOTAL:"
ex.Cells(4,2).font.bold=.t.
ex.Cells(4,2)= round(resul_5,0)
ex.Cells(4,3).font.bold=.t.
ex.Cells(4,3)= round(resul_7,0)
ex.Cells(4,4).font.bold=.t.
ex.Cells(4,4)= round(resul_8,0)
0
 

Expert Comment

by:CleanupPing
ID: 9282169
jjylonen:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 3

Expert Comment

by:SQLMaster
ID: 9514264
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Split between angelIII and Galiak_VS
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Dheeraj Jain
EE Cleanup Volunteer
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question