Importing CSV file to VB Script Array

Discussion in 'ASP General' started by Michael Gooding, Sep 12, 2003.

  1. 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???
     
    Michael Gooding, Sep 12, 2003
    #1
    1. Advertisements

  2. Michael Gooding

    Bob Barrows Guest

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

    Bob Barrows
     
    Bob Barrows, Sep 12, 2003
    #2
    1. Advertisements

  3. 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
     
    Michael Gooding, Sep 12, 2003
    #3
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.