Reading CSV into recordset using GetString - msdn account

[Roland Hall]

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/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.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. */
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

 

[Roland Hall]

: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/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.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. */
: 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
:
:
:

 

[Bob Barrows [MVP]]

Can you post a small extract from your csv file so we can attempt to
reproduce this?

Bob Barrows

 

[Bob Barrows [MVP]]

news:[email protected]...

Oh!, I just noticed your revised subject line. Are you an msdn subscriber
and were these two issues directed at MS? I'll see if I can find someone to
respond

 

[Bob Barrows [MVP]]

Roland Hall wrote:

In order to get MS's attention, you have to use the no-spam email alias that
you got when you registered at
http://msdn.microsoft.com/newsgroups/managed/. You can guarantee that alias
will be used if you post using the web-based system they've created.
otherwise, you have to create an account in OE using the no-spam alias you
chose (I do not remember "nononono.us" being one of the email domain choices
at the registration site, but the site is experiencing problems right now so
I can't confirm it one way or the other).

See:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#configuring

"In the E-mail Address box, add the no-spam alias you registered. If you
post with any other e-mail address, we cannot guarantee a response from the
community or a Microsoft Support Engineer within two business days. "

Bob Barrows

 

[Roland Hall]

: Roland Hall wrote:
:
: In order to get MS's attention, you have to use the no-spam email alias
that
: you got when you registered at
: http://msdn.microsoft.com/newsgroups/managed/. You can guarantee that
alias
: will be used if you post using the web-based system they've created.
: otherwise, you have to create an account in OE using the no-spam alias you
: chose (I do not remember "nononono.us" being one of the email domain
choices
: at the registration site, but the site is experiencing problems right now
so
: I can't confirm it one way or the other).

They asked what email address I wanted to use so they would know it was
managed. That's what I decided on.

: See:
:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#configuring
:
: "In the E-mail Address box, add the no-spam alias you registered. If you
: post with any other e-mail address, we cannot guarantee a response from
the
: community or a Microsoft Support Engineer within two business days. "
:
: Bob Barrows
: --
: Microsoft MVP - ASP/ASP.NET
: Please reply to the newsgroup. This email account is my spam trap so I
: don't check it very often. If you must reply off-line, then remove the
: "NO SPAM"
:
:

 

[Roland Hall]

in message
: Roland Hall wrote:
: > : >> I have two(2) issues.
: <snip>
: >>
: >> Am I causing the issue myself or is this a known issue?
: >>
: Can you post a small extract from your csv file so we can attempt to
: reproduce this?

Here is a massaged record from the file:
999999,6232005,AD,3998717,TR,"KKKKKK, QQQQQQQ ",1111
SUENA ST , ,AUSTIN
,TX,71741,1111111111,1704866,EE,3071963,111111111,
, ,
,331.5,0,331.5,3091998,FAIL TO MAINTAIN FINANCIAL RESP ,OOOOOOOOOO &
OOOOO ,VRY11F ,TX,318,CHEV , ,BLU, ,N ,0, ,Y

It's fixed-length but also comma-delimited so I'm using comma-delimited.

This is my work-around since I get an extra record at the bottom:
for i = 0 to ubound(arrAccounts) - 1

This shows how the data gets read, folder is massaged. I changed my
connectionstring because HDR=NO was not working. I'm controlling it with
the schema.ini file.

dim conn, rs, strPath, arrAccounts, arr, count, records(), x
count = 0
set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
strPath = Server.Mappath("/somefolder/")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" &
"Extended Properties=Text"
rs.Open "SELECT DISTINCT case_num FROM austin.csv", conn, adOpenStatic,
adLockOptimistic, adCmdText
arrAccounts = split(rs.GetString(adClipString,,,,""),vbCr)
rs.Close
rs.Open "SELECT * FROM austin.csv", conn, adOpenStatic, adLockOptimistic,
adCmdText
arr = split(rs.GetString(adClipString,,vbTab,,"null"),vbCr)
rs.Close
set rs = Nothing
conn.Close
set conn = Nothing
prt "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">"
prt "<html>"
prt "<body>"
removeDuplicates arrAccounts, arr

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

Col1=personid Text
Col2=curr_date Text
Col3=trans_code Text
Col4=case_num Text
Col5=case_type Text
Col6=def_name Text
Col7=def_add1 Text
Col8=def_add2 Text
Col9=def_city Text
Col10=def_state Text
Col11=def_phone Text
Col12=def_zip Text
Col13=dl_num Text
Col14=dl_state Text
Col15=def_dob Text
Col16=ss_num Text
Col17=empl_name Text
Col18=empl_addr Text
Col19=empl_phone Text
Col20=ttl_due Text
Col21=ttl_paid Text
Col22=bal_due Text
Col23=issue_date Text
Col24=viol_desc Text
Col25=viol_place Text
Col26=plate_num Text
Col27=plate_st Text
Col28=plate_exp Text
Col29=vehi_make Text
Col30=vehi_model Text
Col31=vehi_color Text
Col32=conviction Text
Col33=status_cod Text
Col34=stats_date Text
Col35=bad_addr Text
Col36=adjudicated Text

I have the code working but I was only concerned about the HDR=NO not
working and the extra array element at the upper boundary.

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

 

[Roland Hall]

in message
: Roland Hall wrote:
: > :
: Oh!, I just noticed your revised subject line. Are you an msdn subscriber
: and were these two issues directed at MS? I'll see if I can find someone
to
: respond

Or anyone that has an answer. I'm concerned with the HDR=NO in the Jet
OLEDB and GetString returning and extra line only when read from a text
file.

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

 

[Bob Barrows [MVP]]

They asked what email address I wanted to use so they would know it
was managed. That's what I decided on.

When I go to the registration site
(https://msdn.one.microsoft.com/Subscriber/1033/ManagedNewsGroups.asp - this
link only works if you sign into http://msdn.microsoft.com/subscriptions/
with your Passport), I am given a choice of 5 domains to use for my alias:

@community.nospam
@newsgroup.nospam
@noemail.nospam
@nospam.nospam
@online.nospam

There is no way to choose another domain. Once you select a posting alias
using one of those domains, you need to use it per the instructions in this
link:

http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#configuring


Bob Barrows

 

[Roland Hall]

in message
: Roland Hall wrote:
: > : >> Roland Hall wrote:
: >>
: >> In order to get MS's attention, you have to use the no-spam email
: >> alias that you got when you registered at
: >> http://msdn.microsoft.com/newsgroups/managed/. You can guarantee
: >> that alias will be used if you post using the web-based system
: >> they've created. otherwise, you have to create an account in OE
: >> using the no-spam alias you chose (I do not remember "nononono.us"
: >> being one of the email domain choices at the registration site, but
: >> the site is experiencing problems right now so I can't confirm it
: >> one way or the other).
: >
: > They asked what email address I wanted to use so they would know it
: > was managed. That's what I decided on.
: >
:
: When I go to the registration site
: (https://msdn.one.microsoft.com/Subscriber/1033/ManagedNewsGroups.asp -
this
: link only works if you sign into http://msdn.microsoft.com/subscriptions/
: with your Passport), I am given a choice of 5 domains to use for my alias:
:
: @community.nospam
: @newsgroup.nospam
: @noemail.nospam
: @nospam.nospam
: @online.nospam
:
: There is no way to choose another domain. Once you select a posting alias
: using one of those domains, you need to use it per the instructions in
this
: link:
:
:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#configuring

I had never heard of that before. I was informed differently and I believe
that conversation took place over the phone.
Thanks I'll check it out.

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

 

[Roland Hall]

I haven't been able to get into the configuration. It says it's down but I
could connect from another server but not sure how to get there.

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

 

[Bob Barrows [MVP]]

I haven't been able to get into the configuration. It says it's down
but I could connect from another server but not sure how to get there.

I ran into the same problem yesterday but was able to get in after several
attempts.

FWIW, I got right in a couple min. ago. Steps:
1. go to http://msdn.microsoft.com/subscriptions/
2. Click the button to sign into MSDN Downloads with Passport
3. After successfully signing in, click the word "here" in this sentence:

Get Unlimited Free PSS Support for Technical Issues!
Unlimited free support is available now to MSDN subscribers via the MSDN
Managed Newsgroups. In over 200 developer newsgroups, Microsoft will assure
that you receive a response to your posts within 2 business days. This
service is included as a benefit of your active subscription. You can get
started by registering here.


which brings you here:
https://msdn.one.microsoft.com/Subscriber/1033/ManagedNewsGroups.asp, where
you register your alias, linking it to your Passport and enabling MS PSS to
recognize your posts in the newsgroups.

At least, that's my understanding of the process. I don't claim to speak
for MS or MSDN.

Bob Barrows

 

[Roland Hall]

in message
: Roland Hall wrote:
: > I haven't been able to get into the configuration. It says it's down
: > but I could connect from another server but not sure how to get there.
:
: I ran into the same problem yesterday but was able to get in after several
: attempts.
:
: FWIW, I got right in a couple min. ago. Steps:
: 1. go to http://msdn.microsoft.com/subscriptions/
: 2. Click the button to sign into MSDN Downloads with Passport
: 3. After successfully signing in, click the word "here" in this sentence:

That's what I was doing and #2 would fail with "We're busy. Go away!"

: Get Unlimited Free PSS Support for Technical Issues!
: Unlimited free support is available now to MSDN subscribers via the MSDN
: Managed Newsgroups. In over 200 developer newsgroups, Microsoft will
assure
: that you receive a response to your posts within 2 business days. This
: service is included as a benefit of your active subscription. You can get
: started by registering here.
:
:
: which brings you here:
: https://msdn.one.microsoft.com/Subscriber/1033/ManagedNewsGroups.asp,
where
: you register your alias, linking it to your Passport and enabling MS PSS
to
: recognize your posts in the newsgroups.
:
: At least, that's my understanding of the process. I don't claim to speak
: for MS or MSDN.

Well, someone needs to. (O:=

Ok, I got it updated. Thanks for the help. What should I do about my
question now? Any ideas?

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

 

[Bob Barrows [MVP]]

Ok, I got it updated. Thanks for the help. What should I do about my
question now? Any ideas?

None, unfortunately. Maybe repost it?

 

[Roland Hall]

in message
: Roland Hall wrote:
: > Ok, I got it updated. Thanks for the help. What should I do about my
: > question now? Any ideas?
:
: None, unfortunately. Maybe repost it?

Holy Moly, a third time? Ok.

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