Why isn't the ADO Extended Properties "HDR=No" flag being obeyed here?

Question

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?

are you sure that does not work? i copied your source code and for me it works... — ulluoink, May 12 '14 at 12:32
Thanks for pointing that out, it sent me back to the drawing board and I was able to find the real problem. — Wildcat Matt, May 13 '14 at 15:04

score 1 · Accepted Answer · answered May 13 '14 at 15:33

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.

Hi I am in the same boat here, the first row is skipped but I would like to clarify one thing, what is the value of path variable? did path have full file path or full directory path? Because I face this problem if path has full file path http://stackoverflow.com/q/1458068/2459982 — hima, Mar 02 '15 at 15:15

Why isn't the ADO Extended Properties "HDR=No" flag being obeyed here?

1 Answers1

Linked