Reading CSV into RecordSet using GetString() (MSDN)

[Roland Hall]

Three times the charm? Sorry for the repost. Trying to get my account
right.

I have two(2) issues.

I'm experiencing a little difficulty and having to resort to a work around.
I already found one bug, although stated the bug was only in ODBC, which I'm
not using. It appears to be in the OLEDB driver also.

My connection was:
conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & strPath & ";" &
"Extended Properties='Tex t;HDR=NO;FMT=De limited'"

I got information from here:
http://msdn.microsoft.com/library/de...ng03092004.asp

I am using a schema.ini file and in it was:

[austin.csv]
Format=CSVDelim ited

Col1=personid Text
Col2=currDate Text
Col3=transCode Text
Col4=caseNum Text
Col5=caseType Text
Col6=defName Text
Col7=unknown Text

1. The first line in the csv file was being ignored, as if it was a header
line. I verified by putting a blank line in the file and then I was able to
see the first record, which was now the second line.

I read this was a bug but for FirstRowHasName s. It wasn't what I was using
but the effect was the same for HDR=NO.
"However, due to a bug in the ODBC driver, specifying the FirstRowHasName s
setting currently has no effect. In other words, the Excel ODBC driver (MDAC
2.1 and later) always treats the first row in the specified data source as
field names."

Ref: http://support.microsoft.com/kb/257819

I found another article that fold me to use something else in the schema.ini
file:
http://www.aspdb.com/Site/tor/Manual04/T_csvtext.shtm

ColNameHeader=F alse

My current schema.ini:
[austin.csv]
Format=CSVDelim ited
ColNameHeader=F alse

Col1=personid Text
Col2=currDate Text
Col3=transCode Text
Col4=caseNum Text
Col5=caseType Text
Col6=defName Text
Col7=unknown Text

I now get the first row without the need for the blank line.

My current connection string:
conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & strPath & ";" &
"Extended Properties=Text "
2. I am getting a blank line using rs.GetString but at the end.

My line:
arrAccounts = split(rs.GetStr ing(adClipStrin g,,,,""),vbCr)

I thought perhaps it was returning a blank line because I ended my cursor,
in the csv file on a blank line at the end. I have this issue using FSO and
CSV files. I removed it but I still have the issue.

I am able to get past it by reducing my upperboundary by 1 but it feels like
a work-around.

for i = 0 to ubound(arrAccou nts) - 1
lprt arrAccounts(i)
next

Full source for this issue:
dim conn, rs, strPath, arrAccounts, arr, i
Set conn = Server.CreateOb ject("ADODB.Con nection")
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
strPath = Server.Mappath( "/csv/")
conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & strPath & ";" &
"Extended Properties=Text "
rs.Open "SELECT DISTINCT caseNum FROM austin.csv", conn, adOpenStatic,
adLockOptimisti c, adCmdText
arrAccounts = split(rs.GetStr ing(adClipStrin g,,,,""),vbCr)
rs.Close
for i = 0 to ubound(arrAccou nts) - 1
lprt arrAccounts(i)
next

Am I causing the issue myself or is this a known issue?

TIA...

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */

[[MSFT]]

Hello Roland,

The blank line was generated by the Split method, when the Split char is
"vbCr". A incorrect section was generated by Split method. Your work around
is just the right way to get around the problem. Or remove the last vbCr
before Split:

ss = rs.GetString(ad ClipString, , , , "")

ss = Left(ss, Len(ss) - 1)

arrAccounts = Split(ss, vbCr)

Hope this help,

Luke

[Roland Hall]

"[MSFT]" wrote in message news:GC******** *****@TK2MSFTNG XA01.phx.gbl...
: Hello Roland,
:
: The blank line was generated by the Split method, when the Split char is
: "vbCr". A incorrect section was generated by Split method. Your work
around
: is just the right way to get around the problem. Or remove the last vbCr
: before Split:
:
: ss = rs.GetString(ad ClipString, , , , "")
:
: ss = Left(ss, Len(ss) - 1)
:
: arrAccounts = Split(ss, vbCr)

I guess I'll keep it the way it is then. Since vbCr was the delimiter, I
didn't expect it to add a blank element on the end of the array. If it been
a visible character, I might have noticed.

a,b,c,

Thanks for your help Luke.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

[[MSFT]]

Thank you for update.

Regards,

Luke