When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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."

- continued -

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"===
DRIVER=Microsoft Text Driver (*.txt; *.csv)
=== end of file "CSV.DSN" ===

Appendix B: Sample data file, save as "data.txt"

==== beginning of sample data file ===
=== 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. ===


' 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

'Close our recordset and connection
set rs = nothing
set conn = nothing


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.

ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article