ASP code query DB then export to Excel 1

[ajacode]

Can anyone tell me if it is possible to run an asp page which returns a table of data 5 columns 2000 rows and then either automatically or when a button is clicked..export it to an Excel page and place it on the users Desktop ?

IF so is there a place I can see a code example?

Thanks

AJ

 

[ufobaby]

hi, u surely can do it.... below is the code to do it

Set ConLog = Server.CreateObject("adodb.connection&quot
ConLog.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & strDbPath & ";pwd=" & strDbPwd

Set rs = Server.CreateObject("ADODB.Recordset&quot
rs.Open QueryString,ConLog

'//// Create users folder
dim FilName
dim FilPath
FilPath = <<some path on server (temporary only)>
Set fso = server.CreateObject(&quot;scripting.filesystemobject&quot

'/// Decide which format to create
if Request.Form(&quot;strType&quot = &quot;pipe&quot; then
'=== Simple Text file Pipe Delimited
FilName = filecheck (FilPath & &quot;/&quot;,session(&quot;me&quot & &quot;_Query&quot;,&quot;.txt&quot
set ts = fso.CreateTextFile(FilPath & &quot;/&quot; & FilName)
'===> This is some standard function to write to a file
standarddump rs,&quot;|&quot;,ts,True
elseif Request.Form(&quot;strType&quot = &quot;xls&quot; then
'=== Excel File with the help of tab as delimiter
FilName = filecheck (FilPath & &quot;/&quot;,session(&quot;me&quot & &quot;_Query&quot;,&quot;.xls&quot
set ts = fso.CreateTextFile(FilPath & &quot;/&quot; & FilName)
standarddump rs,chr(09),ts,True
end if

ts.close
set ts=nothing
Dim Stream
Set Stream = server.CreateObject(&quot;ADODB.Stream&quot
Stream.Open
Stream.Type =1
Stream.LoadFromFile Server.MapPath(FilPath & &quot;/&quot; & filname)
Response.ContentType = &quot;bad/type&quot;
Response.AddHeader &quot;content-disposition&quot;, &quot;attachment; filename=&quot; & filName
Response.Charset = &quot;UTF-8&quot;
Response.BinaryWrite Stream.Read
Stream.Close
Set Stream = Nothing
%>

when this page will load it will ask for &quot;save as&quot; dialog box.
if u have any problem with the code write back....


Niraj

 

[ajacode]

Wow,

You're faster than Google !!

Thanks I start giving it a try and get back if I hit a snag.

Thanks very much.

AJ

 

[ajacode]

Niraj,
**********************
rs.Open QueryString,ConLog

Is the QueryString in this line the actual sql select statement I am using ?
***********************
'/// Decide which format to create

How do I decide? In the SQL string ?

BTW I am using Oracle but I see where I replace the conn string..

I guess the next thing I have to check to see if the I_user has write proveledges to the folder..

Thanks

AJ

 

[ufobaby]

hi, thx for the star
this was faster than Google coz i'v spent a lot of nights digging into Google for this info ... anyways that's not the point ...

1.] Yup, QueryString is the actual SQL stmt. I have stored it on a page in a hidden field. Then use Request.Form(..)

2.] again one of the hidden fields has the Delimiter value and retrive in the same manner

hope this helps , else i'll mail u both the pages u ca ngo thru.


Niraj

 

[ajacode]

I think that answers it..

Thanks Again

AJ

 

[ckchang]

What is the &quot;standarddump&quot; function in the middle of the page. This is the first time I have seen this. I try to find the documentation about this. Can anyone explain it?. This is a great article. I have a similar problem of an excelsheet about 10~ 12 columns and 5000 rows at least, can go as many as 12K rows. Thanks.

 

[ajacode]

Here's a second way. If you look at ASPTODAY.com
and search the site for &quot;excel&quot; you'll find the entire aticle with 3 different methods heres #2
Method Two: &quot;Comma Separated Values (CSV)&quot;
A comma separated values file is a second alternative to exporting a web page to an Excel-readable format. This format offers a bit more flexibility then the ContentType property. CSV also offers two big advantages over the other methods; firstly you do not need any special software on the client or the server to create it, and secondly, the files tend to be smaller than a native Excel file.
The CSV format is defined as follows : commas delimit the columns and a carriage return separates the rows . Commas, as separators, can pose a problem with fields that contain commas (such as $1,234); this will cause an extra column in the row to be created. This problem can be remedied easily by wrapping the field with commas on each end.
We can see how to create a CSV file by walking through the function CreateCSVFile() from the sample provided.
Line 1: strFile = GenFileName()
Line 2: Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot
Line 3: Set a = fs.CreateTextFile(server.MapPath(&quot;.&quot & &quot;\&quot; & strFile & Line 4: &quot;.csv&quot;,True)
Line 5: If Not oRS.EOF Then
Line 6: strtext = chr(34) & &quot;Year&quot; & chr(34) & &quot;,&quot;
Line 7: strtext = strtext & chr(34) & &quot;Region&quot; & chr(34) & &quot;,&quot;
Line 8: strtext = strtext & chr(34) & &quot;Sales&quot; & chr(34) & &quot;,&quot;
Line 9: a.WriteLine(strtext)
Line 10: Do Until oRS.EOF
Line 11: For i = 0 To oRS.fields.Count-1
Line 12: strtext = chr(34) & oRS.fields(i) & chr(34) Line 13: & &quot;,&quot;
Line 14: a.Write(strtext)
Line 15: Next
Line 16: a.Writeline()
Line 17: oRS.MoveNext
Line 18: Loop
Line 19: End If
Line 20: a.Close
Line 21: Set fs=Nothing
Line 22: Response.Write(&quot;Click <A HREF=&quot; & strFile & &quot;.csv>Here</A> to Line 23: to get CSV file&quot
The first line creates a unique filename by calling the function GenFileName() which we will discuss later.
Lines Two through Four , create the textfile to be written to, using the FileSystemObject object and CreateTextFile function. This sample writes the files in the same directory as the source file, you may want to create a separate directory to hold these files in a real production application.
Lines Five through Nine , will produce the headers for the first row. I have hard-coded the column names because the report is always the same, though it is possible to read database column names and use them instead. Notice how I have included the comma between each field. The use of the WriteLine function sends them to the file along with a carriage return.
Lines Ten through Eighteen loops through the recordset and place quotation marks around each field, followed by a comma. The Write function then sends each field to the file. A WriteLine completes each row with the carriage return.
The last few lines simply close the file, releases the objects and places a link on the webpage so it can be retrieved.
When you click on the link generated, you are prompted with the Save or Open dialog. If you choose Open , the file will be opened in Excel (provided that it is installed on the computer). If you choose Save , you can save the text file to a storage device and import it into a variety of applications.


Good Luck

HTH

 

[ufobaby]

ajacode , good research !
but if u read the code i posted then u'll find that
1.] i use a &quot;tab&quot; as a delimiter and file extension as &quot;.xls&quot;. Once the user has saved it on the local machine , he just needs to dbl click to open directly in excel w/o using the &quot;text import wizard&quot; (many ppl donot know the use of this)
2.] No problems with commas or specila characters in this method.

ckchang , ummm... i actually did not mention properly what that &quot;standarddump&quot; does , apologies.
it's acuatom function written by me to generate files from a recordset. below is the code for ur ref.
========================================================
Function StandardDump(ByRef rs,delim,ByRef ts,Heading)
'====================================
'This function generates a dump file
'for a particular table. Headings are
'optional depending on the arguments
'====================================
Dim RCTR
'==== Check For Headings ====
RCTR = 0
If Heading = True Then
Do While rs.Fields.Count <> RCTR
If RCTR <> 0 Then
ts.Write delim
End If
ts.Write ucase(rs.Fields(RCTR).Name) & &quot;&quot;
RCTR = RCTR + 1
Loop
ts.WriteLine
End If
'==== Write to the File here ====
RCTR = 0
ctr = 0
If rs.Eof = False Then
rs.MoveFirst
Do While rs.EOF = False
Do While rs.Fields.Count <> RCTR
If RCTR <> 0 Then
ts.Write delim
End If
ts.Write rs(RCTR) & &quot;&quot;
RCTR = RCTR + 1
Loop
ts.WriteLine
ctr = ctr + 1
rs.MoveNext
RCTR = 0
Loop
rs.movefirst
End If

End Function
======================================================
gives a lot of functionality , can be widely used for creating any type of file.

guys your suggestions / comments on the above will surely add value.

hope ur doubts are cleared else write back


Niraj