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.
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>