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

Data export in SQL Server 7

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
jjylonen
Asked:
jjylonen
2 Solutions
 
ykchakriCommented:
It is not possible through wizard. But, you can manipulate using Design package.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You can only export 1 table at a time, thus you would need to use 1 DTS Wizard execution per table.
Cheers
0
 
Galiak_VSCommented:
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
 
CleanupPingCommented:
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
 
SQLMasterCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now