Importing CSV file to VB Script Array

[Michael Gooding]

I've been working on some coursework that requires the use of an excel
spreadsheet and a CSV file to be read into an ASP page and worked
with.

What I have done is set up an array to store the values from the CSV
file and accessed the spreadsheet using an Excel Recordset.

For some reason only the first 2 values in the CSV file (stored in the
array as the first and last) are comparing properly with values from
the spreadsheet.

When the comparisons are made I have used a loop to go through all the
records in the recordset and then another loop to search the array.
If I hard code the values instead of the array values the results are
as expected.

I've tried making sure all the values are trimmed and preceeded with
the "cstr" operation and still no joy. Even when I've written the
comparisons to screen it is saying "RGD11 = RGD11 = False"

Has any one got any clues as to what is going on???

[Bob Barrows]

Michael Gooding wrote:

I've been working on some coursework that requires the use of an excel
spreadsheet and a CSV file to be read into an ASP page and worked
with.

What I have done is set up an array to store the values from the CSV
file and accessed the spreadsheet using an Excel Recordset.

For some reason only the first 2 values in the CSV file (stored in the
array as the first and last) are comparing properly with values from
the spreadsheet.

When the comparisons are made I have used a loop to go through all the
records in the recordset and then another loop to search the array.
If I hard code the values instead of the array values the results are
as expected.

I've tried making sure all the values are trimmed and preceeded with
the "cstr" operation and still no joy. Even when I've written the
comparisons to screen it is saying "RGD11 = RGD11 = False"

Has any one got any clues as to what is going on???

No. We're going to need to see some code, along with the sample data that
generates the error.

Bob Barrows

[Michael Gooding]

Reading CSV File into array:

csv_to_read="book1.csv"
set fso = createobject("scripting.filesystemobject")
set act = fso.opentextfile(server.mappath(csv_to_read))
imported_text = act.readline
imported_text = replace(imported_text,chr(13),",")
imported_text = replace(imported_text,chr(34),"")
split_text=split(imported_text,",")
num_imported=ubound(split_text)+1
total_imported_text = act.readall
total_imported_text = replace(total_imported_text,chr(13),",")
total_imported_text = replace(total_imported_text,chr(34),"")
total_split_text=split(total_imported_text,",")
total_num_imported=ubound(total_split_text)
Set cnnExcel = Server.CreateObject("ADODB.Connection")
sspath = "c:/website/comp/cz/xxxx/resources/test1.xls"

cnnExcel.Open "DBQ=" & sspath & ";" & "DRIVER={Microsoft Excel Driver
(*.xls)};"

counter = 0
for i = 0 to (total_num_imported - 1)
if i mod num_imported = 0 then
pinstore(counter) = total_split_text(i)
counter = counter + 1
end if

next
for i = 0 to (num_imported -1)
if i mod num_imported = 0 then
pinstore(counter) = split_text(i)
counter = counter + 1
end if

next
Connecting to excel spreadsheet:

Set cnnExcel = Server.CreateObject("ADODB.Connection")
sspath = "c:/website/comp/cz/xxxx/resources/test1.xls"
cnnExcel.Open "DBQ=" & sspath & ";" & "DRIVER={Microsoft Excel Driver
(*.xls)};"

Setting excel recordset:

Set rstExcel = Server.CreateObject("ADODB.Recordset")
q = "SELECT * FROM TestData where relationship <> 'SELF' and Name =" &
replace((request("name")),"_"," ") & ";"
rstExcel.Open q, cnnExcel, adOpenStatic, adLockPessimistic

Comparing values within a loop:

rstExcel.Open q, cnnExcel, adOpenStatic, adLockPessimistic
rstExcel.MoveFirst

' Loop through the data rows showing data in an HTML table.
step3 = 0
Do until rstExcel.EOF
Response.Write "<tr bgcolor='#EEEEEE'>" & vbCrLf
For I = 0 To iCols - 1
response.write icols & " I = " & I & "<br>"
column = I mod(icols)
select case column
case 0

case 1

Response.Write "<td>" & rstExcel.Fields.Item(I).Value & "</td>"
case 2
Response.Write "<td>" & rstExcel.Fields.Item(I).Value & "</td>"
case 3
Response.Write "<td>" & rstExcel.Fields.Item(I).value & "</td>"
case 4
Response.Write "<td>" & rstExcel.Fields.Item(I).Value & "</td>"
case 5

completed = false
for pincount = 0 to counter - 1

if rstexcel.fields.item(I-2).value = pinstore(pincount) then
completed = true
end if
next
if completed = true then
Response.Write "<td>Yes</td>"
else
Response.Write "<td><font color='#FF0000'>No</font></td>"
end if
end select

Next 'I
Response.Write "</tr>" & vbCrLf

rstExcel.MoveNext
Loop

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!