Solved

Data export in SQL Server 7

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

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

896 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now