จดกันลืม Connecting CSV (Text Delimiter) Database On The Fly


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

ความคิดเห็น

แสดงความคิดเห็น

โพสต์ยอดนิยมจากบล็อกนี้

Oculus Rift

รูปภาพ
Update (3.1.17): Oculus Rift is getting a massive price drop in the US. Announced at GDC 2017 , the hardware will now be sold in a package deal with the Oculus Touch controllers for $599. Separately, you can now by the Oculus Rift headset for $499, and the Touch controllers for $99. That makes it about $200 than its closest competitor, the HTC Vive, and a pretty good deal for gamers just getting into VR. It's tough being the trailblazer, especially when it comes to technology, and the Oculus Rift has felt that more than anyone else.  It was the product that kicked off the virtual reality craze and got the world interested the new medium, but after its announcement other competitors came to market with a more complete vision, with motion controllers included in the box and other technological advantages.  When we first reviewed the Oculus Rift it was still having teething problems, but now, after the release of its own (very capable) motion-controllers in the form of the Oculus Tou...
อ่านเพิ่มเติม

Panasonic Lumix FZ2500 / FZ2000

รูปภาพ
Panasonic has long offered a respectable range of superzoom cameras within its Lumix portfolio, but the Lumix FZ1000 was still a notable addition when it arrived three years ago. The reason? While it adhered to the same formula as previous FZ models, by employing a 1.0-inch-type sensor it promised far better image quality than we'd expect from such a camera. This, combined with its DSLR-style body, meant it became a natural rival to the Sony Cyber-shot RX10 , a camera that also married a long zoom with the same size of sensor. Sony has since updated its camera twice, most recently with the Sony RX10 III , while Canon has also muscled in on the action with its 1.0-inch-sensor-toting PowerShot G3 X . Now, Panasonic is fighting back with the FZ2500 (it's called the FZ2000 outside the US). The new model arrives with a longer lens than its predecessor's, together with a handful of updated features, and the FZ1000's video specs sweetened with a few extra additions. Indee...
อ่านเพิ่มเติม

Apple TV

รูปภาพ
Update : A new report suggests that a 4K Apple TV box is finally in the works –   read more about it here. Apple TV review continues below... If it existed in a bubble, the revamped 2015 Apple TV would be a stellar product. It offers a steady improvement over its three-year-stagnant predecessor - a device built for the then revolutionary new standard of 1080p - and comes with one of the most premium remotes on the market. If there was nothing to compare it to, it'd be hard to point out the number of flaws the system has. It's lacking the majority of Australian streaming apps, for example, and Siri recognises less than half the commands on the new Apple TV than it does on iOS, watchOS or OSX. The situation is improving. At WWDC 2016 Apple noted that 1,300 video channels have now made it to the device alongside 6,000 native apps. That's not to mention the fact Siri is voiceless at the moment, relying on text and graphics to respond to any inquiries you might have. Siri has h...
อ่านเพิ่มเติม