Importing CSV file to VB Script Array

M

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

Bob Barrows

Michael said:
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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top