Solved

Data export in SQL Server 7

Posted on 2001-06-20
5
291 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
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

830 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