Published: Monday, March 08, 1999
Connecting to Text-Delimited Database Files on the Fly
By Abd Shomad
This article is a follow up to Abd's article, "On the Fly Connection for MS Access DB."
Text Delimited Database files are files that have each column separated by a specified delimiter (a
comma, semicolon, etc.), and each separate row specified by a new line character. In Excel or Access you
can save your tables as comma delimited files. A comma delimited file is an example of a text
delimited file. This article will show you how to connect to CSV (Text Delimited) Database files on
the fly.
Connecting CSV (Text Delimiter) Database On The Fly:
What do you have to do to make this code work:
1. Create virtual directory having script permission
2. Create a "Microsoft Text Driver" file DSN from your ODBC32
from Control Panel. Name it "CSV.DSN" (see appendix A)
or Copy and paste the text from appendix A
3. Save sample data below to "data.txt" (see appendix B)
4. Save this article as "AnyName.asp"
5. Copy all the three files ("csv.dsn", "data.txt", and "AnyName.asp")
on your virtual directory having execute script permission that
you've just created.
6. Browse this application from your browser.
==========================================
Appendix A: If you were too lazy to create your file DSN
from the Control Panel,
Copy & Paste the text below, and save it as "CSV.DSN"
==== beginning of file "CSV.DSN"===
[ODBC]
DRIVER=Microsoft Text Driver (*.txt; *.csv)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=25
MaxBufferSize=512
ImplicitCommitSync=Yes
FIL=text
Extensions=txt,csv,tab,asc
DriverId=27
=== end of file "CSV.DSN" ===
==========================================
Appendix B: Sample data file, save as "data.txt"
==== beginning of sample data file ===
ID,Name,Price
1,"Chairs",$40.00
2,"Table",$75.00
3,"Fork",$1.50
4,"Lamp",$15.00
5,"Rug",$35.00
=== end of file ===
Now, we will connect this database on the fly!
Look at the code below!
Copy and save it as "AnyName.asp"
=== These lines below are the working code. ===
<html>
<body>
<%
' Note that the differences between MDB file and other
' file based database is that MDB file need to have the
' *** file name ("anyfile.mdb") *** as the DBQ value.
' Other databases, such as Comma Delimited (CSV),
' FoxPro (DBF), Paradox (DB) use their respective
' *** DIRECTORY NAME *** as the DBQ value.
' Be sure to not how we specify the DBQ as the
' directory name, NOT as the .txt filename!
' This variable will hold the name of our DSN file
Dim sDSNFile
sDSNFile = "CSV.dsn"
' Let's now dynamically retrieve the current directory
Dim sScriptDir
sScriptDir = Request.ServerVariables("SCRIPT_NAME")
sScriptDir = StrReverse(sScriptDir)
sScriptDir = Mid(sScriptDir, InStr(1, sScriptDir, "/"))
sScriptDir = StrReverse(sScriptDir)
' Time to construct our dynamic DSN
Dim sPath, sDSN
sPath = Server.MapPath(sScriptDir) & "\"
sDSN = "FileDSN=" & sPath & sDSNFile & _
";DefaultDir=" & sPath & _
";DBQ=" & sPath & ";"
' sDSN might now look like:
' FileDSN=C:\Inetpub\wwwroot\CSV.DSN;DefaultDir=
' C:\Inetpub\wwwroot;DBQ=C:\Inetpub\wwwroot;
' Building Data Connection
Dim Conn, rs
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open sDSN
' Get only the Name and Price fields
Dim sql
sql = "SELECT Name,Price FROM data.txt"
'Implicitly create a recordset object with the
'results of this query
set rs = conn.execute(sql)
'Print out the contents of our recordset
Do WHile Not rs.EOF
Response.Write "Name: " & rs("Name")
Response.Write "<BR>Price: " & rs("Price")
Response.Write "<HR>"
rs.MoveNext 'Move to the next record
Loop
'Close our recordset and connection
rs.close
set rs = nothing
conn.close
set conn = nothing
%>
<body>
</html>
Happy Scripting!!!
This article was written by
Abd Shomad,
currently a student of School of Telecommunication Technology
(STTTelkom) Bandung, Indonesia. Abd is involved in many research
projects on Internet related science and often leads some real work teams
during his studies.
