Silicon Thumb

Since the 1980s, I've worked with computers, watched them grow, shrink, change and improve. I've worked with a lot of users and solved a lot of problems in that time too, so I thought this would be a good place to share some of the random things I've found and solved. If you have some odd problem, email me. If I can figure it out I'll post the answer here.

My Photo
Location: Mansfield, Texas, United States

I am a veteran computer geek, but I prefer the term 'Hired Gun', since that gives the (misleading) impression that I know what I'm talking about. I have worked on all sizes of system as an engineer, developer, technical support and operations, and at all levels from Operator to CIO.
I have some certifications, but what they are depends on what Microsoft is calling them this week.

If you have a question, and don't mind the answer being posted, email me here, removing the spam stopper.

Tuesday, November 14, 2006

Creating CSV Files out of Thin Air

CSV (Comma Separated Values) are handy if you want to quickly import information into Excel, Access etc. If you have your own web server, or a co-located server, then it is relatively easy to create CSV files from your database on the fly, although it can be a bit of a pain to manage if you have a lot of hits on your server.

If you don't have as much control over the webserver, or simply want an easy way to produce a file for a single user regardless of how many people are using the server, there is an easy way to do this with ASP.

Most ASP users are familiar with Response.Write and Response.Redirect, but there are other cool methods associated with it that don't get as much play as they probably should. In the case below we are going to use Response.ContentType and Response.AddHeader to easily generate a csvFile on the fly.

First let's connect to our database and loop through the values, storing them in a variable called csvFile:

Option Explicit
Dim conn, SQL, rs, csvFile
Set conn = Server.CreateObject("ADODB.Connection")

' Change to your Parameters
conn.Open "DSN=myDSN;DATABASE=MyDatabase;UID=myUserid;PWD=myPassword"
Set rs = Server.CreateObject("ADODB.RecordSet")
' Create a Header Record
csvFile = "Name, Cell, Work" & vbCrLf
rs.Open SQL, conn, 1, 3

Do while not rs.EOF
csvFile = csvFile & rs("name") & "," & rs("cell") & rs("workphone") & vbcrlf
rs.MoveNext: Loop

Set conn = Nothing
set rs = Nothing

Once the data has been collected and store in the variable csvFile we write to the screen for the first time.

Response.ContentType = "application/csv"
Response.AddHeader "content-disposition", "inline; filename=export.csv"

The above lines let the web browser know that what it is about to receive should not be handle as a web page, but instead should be treated like another type of file (in this case, CSV). If you have a handler set for CSV Files (And if you use Microsoft Office, the chances are you do, and that it is Excel) then instead of a web page being displayed, you will get and Open/Save box, similar to this:

Click Open, and the file will be loaded directly into Excel. The end user will still need to adjust columns and make the headings look pretty, but it gives them 90% of what they need and the rest should be under their control anyway, IMHO.


Post a Comment

<< Home