customizing excel export/import with SQL Server

[GeeWond3r]

Are they any asp code out there that allows me to export/import Excel via SQL SErver? Have alot of projects to do, n I just need some quick scripts to speed up the workload.

 

[onpnt]

DTS in SQL Server will be your best bet then if your time is restrained. Not ASP

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811

 

[DreXor]

you can use SQL to output to recordset ( if you use stored procs on the sql serv it'll speed it up even more ) then the easiest way to get to excel the data is to string format the recordset to standard csv formatting, use addheaders for contenttype of excel, and name the page whatever.xls and enable it alone as an asp, and boom, you got online dynamic excel spreadsheet based off sql data, it's pretty dam fast even with hoards of data. if you're generating HUGE reports, i might recommend leaning to RS.GetRows instead of a standard recordset and since it'll be in array format, it'd be a nested for/next pair to cycle the fields, then rows.

 

[DreXor]

very very basic example :

<%
response.buffer = true

set con = server.createobject("adodb.connection")
con.open <connection method>
Set RS = Con.Execute("select * from whatevertable")

dim exceloutput(0)
exceloutput(0) = ""

'generating field name headers
for each field in rs.fields
  exceloutput(0)= exceloutput(0) & """" & field.name & ""","
next
'this next line ( and ones later ) are for delimiting records
'the commas are for delimiting columns
'would be "nice" to patch this up after to take out the last comma
'cause that leaves a dud final column

exceloutput(0)= exceloutput(0) & vbcrlf

do while not rs.eof
  for each field in rs.fields
    exceloutput(0)= exceloutput(0) & """" & rs(field.name) & ""","
  next
  exceloutput(0)= exceloutput(0) & vbcrlf
  rs.movenext
loop

con.close
set rs = nothing
set con= nothing

Response.ContentType = "application/vnd.ms-excel"
response.write exceloutput(0)
response.flush
%>