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

  2. Michael Gooding

    Bob Barrows Guest

    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
    Bob Barrows, Sep 12, 2003
    #2
    1. Advertising

  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

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Michael Gooding, Sep 12, 2003
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. BobLaughland

    Importing a CSV file

    BobLaughland, Mar 22, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    20,814
    shijoforum
    Apr 17, 2010
  2. Tintin92
    Replies:
    1
    Views:
    1,663
    Andrew Thompson
    Feb 14, 2007
  3. sagar

    Importing a csv file

    sagar, Apr 30, 2007, in forum: Python
    Replies:
    3
    Views:
    761
    Tim Golden
    Apr 30, 2007
  4. tom
    Replies:
    1
    Views:
    1,287
    ahaskins
    Jul 13, 2009
  5. Rahul
    Replies:
    1
    Views:
    313
    Gabriel Genellina
    Mar 25, 2008
Loading...

Share This Page