Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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