I have a CSV file with no header output by a process I do not control:
FOO,<auto>,12345678,8005882300, ,2
FOO,<auto>,23456789,2128675309, ,2
FOO,<auto>,34567890,3125577203, ,2
FOO,<auto>,45678901,9198423089, ,2
I'm trying to access it using Classic ASP with ADO then print out the phone number using this code:
stmt = "SELECT * FROM baz.txt"
connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:;Extended Properties='Text;HDR=No;FMT=Delimited'"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connectstring
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open stmt, conn, adLockOptimistic, , adCmdText
If Not rs.eof Then
Data=rs.GetRows()
End If
for r = 0 to UBound(Data,2)
response.write(Data(3,r) & "<br>")
next
Even though I have the HDR
flag set to No
the result set never includes the first row:
2128675309
3125577203
9198423089
What am I doing wrong that it appears the first row is still being skipped?
I wanted to post the answer to my own question in case someone else runs into a similar situation in the future.
For the purposes of the post I had oversimplified my code, and in so doing I had removed the thing that was making it break. The code was actually contained within a loop to iterate through several files in several subfolders:
path = "I:"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set dirFolders = objFSO.GetFolder(path).SubFolders
For Each subFolder in dirFolders
Set dirFiles = objFSO.GetFolder(subFolder).Files
For Each bar in dirFiles
stmt = "SELECT * FROM " & bar
connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& path &";Extended Properties='Text;HDR=No;FMT=Delimited'"
[...]
In practice, this is what was actually getting passed to ADO:
stmt = "SELECT * FROM I:\20140509\baz.txt"
connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:;Extended Properties='text;HDR=No;FMT=CSVDelimited'"
Having the full path included in stmt
and an incomplete path in connectstring
caused the HDR
flag to be ignored. It would have been nice if ADO had broken a little less subtly when I fed that to it, but it is what it is.
Corrected code:
For Each path in dirFolders
Set dirFiles = objFSO.GetFolder(path).Files
For Each bar in dirFiles
stmt = "SELECT * FROM " & objFSO.GetFileName(bar)
connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& path &";Extended Properties='Text;HDR=No;FMT=Delimited'"
Ultimately, it's a reminder that chucking a couple of response.write
statements at puzzling code is a Good Idea, and when it doesn't work like you expect, to strip it down to brass tacks to make sure you're looking in the right place.