Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Reading CSV into RecordSet using GetString() (MSDN)

Reply
Thread Tools

Reading CSV into RecordSet using GetString() (MSDN)

 
 
Roland Hall
Guest
Posts: n/a
 
      07-23-2005
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=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" &
"Extended Properties='Text;HDR=NO;FMT=Delimited'"

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=CSVDelimited

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 FirstRowHasNames. 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 FirstRowHasNames
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=False

My current schema.ini:
[austin.csv]
Format=CSVDelimited
ColNameHeader=False

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=Microsoft.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.GetString(adClipString,,,,""),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(arrAccounts) - 1
lprt arrAccounts(i)
next

Full source for this issue:
dim conn, rs, strPath, arrAccounts, arr, i
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
strPath = Server.Mappath("/csv/")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" &
"Extended Properties=Text"
rs.Open "SELECT DISTINCT caseNum FROM austin.csv", conn, adOpenStatic,
adLockOptimistic, adCmdText
arrAccounts = split(rs.GetString(adClipString,,,,""),vbCr)
rs.Close
for i = 0 to ubound(arrAccounts) - 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. */


 
Reply With Quote
 
 
 
 
[MSFT]
Guest
Posts: n/a
 
      07-25-2005
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(adClipString, , , , "")

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

arrAccounts = Split(ss, vbCr)

Hope this help,

Luke

 
Reply With Quote
 
 
 
 
Roland Hall
Guest
Posts: n/a
 
      07-25-2005
"[MSFT]" wrote in message news:(E-Mail Removed)...
: 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(adClipString, , , , "")
:
: 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


 
Reply With Quote
 
[MSFT]
Guest
Posts: n/a
 
      07-26-2005
Thank you for update.

Regards,

Luke

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting Recordset to CSV Michael ASP General 1 12-31-2006 12:03 PM
Reading CSV into recordset using GetString - msdn account Roland Hall ASP General 14 07-23-2005 06:14 PM
Reading CSV into recordset using GetString Roland Hall ASP General 2 07-21-2005 05:11 AM
ASP RecordSet output to a .CSV textfile Matt Massie ASP General 1 10-26-2003 02:10 PM
RecordSet.Move or RecordSet.AbsolutePosition?? Hung Huynh ASP General 8 09-24-2003 11:07 AM



Advertisments