Reading CSV into recordset using GetString

Discussion in 'ASP General' started by Roland Hall, Jul 15, 2005.

  1. Roland Hall

    Roland Hall Guest

    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, Jul 15, 2005
    #1
    1. Advertising

  2. Roland Hall

    Bullschmidt Guest

    Roland,

    I see this was posted almost a week ago with no replies.

    It's not exactly the same method you're trying to use but here is what
    has worked for me in reading a CSV file into a database's table.

    You can connect to a csv file (which can be opened in Excel and which an
    Excel file can be converted into) in good form just as you can to a
    regular database.

    The .csv file needs to be uploaded to the server.

    And you can have two recordsets open at the same time.

    So I'd suggest going through this recordset one row at a time and within
    this loop add a new record to the "real" database's recordset.

    And for help connecting to a text file using the Jet OLE DB provider:
    http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForTextFi
    les

    And based on the above link realize that the actual filename does NOT go
    in the connection string - rather it goes in the SQL statement
    (definitely a little tricky).

    Best regards,
    J. Paul Schmidt, Freelance ASP Web Designer
    http://www.Bullschmidt.com
    ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...

    <<
    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/dnclini
    c/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...
    >>



    *** Sent via Developersdex http://www.developersdex.com ***
    Bullschmidt, Jul 21, 2005
    #2
    1. Advertising

  3. Roland Hall

    Roland Hall Guest

    "Bullschmidt" <-nospam> wrote in message
    news:...
    : Roland,

    Hi Paul...

    : I see this was posted almost a week ago with no replies.

    Yes, and I have an MSDN Universal subscription and I posted again with that
    account and still nothing.

    : It's not exactly the same method you're trying to use but here is what
    : has worked for me in reading a CSV file into a database's table.
    :
    : You can connect to a csv file (which can be opened in Excel and which an
    : Excel file can be converted into) in good form just as you can to a
    : regular database.
    :
    : The .csv file needs to be uploaded to the server.

    The file is already on the server.

    : And you can have two recordsets open at the same time.

    ....but I only need one.

    : So I'd suggest going through this recordset one row at a time and within
    : this loop add a new record to the "real" database's recordset.

    What "real" database? I have a .csv file.

    : And for help connecting to a text file using the Jet OLE DB provider:
    : http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForTextFi
    : les

    Perhaps you missed this part of my post...

    My connection was:
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath &
    ";" & "Extended Properties='Text;HDR=NO;FMT=Delimited'"

    : And based on the above link realize that the actual filename does NOT go
    : in the connection string - rather it goes in the SQL statement
    : (definitely a little tricky).

    Yes, I know.

    : rs.Open "SELECT DISTINCT caseNum FROM austin.csv", conn, adOpenStatic,
    : adLockOptimistic, adCmdText

    Here is the full source again:

    : 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

    My two issues are:

    1. HDR in the connection string is dysfunctional. I can get past it with:

    ColNameHeader=False

    .... in the schema.ini file.

    2. GetString returns an empty element at the end. I have had to employ a
    workaround of decrementing the upper boundary of my array by one with loop.
    GetString, to my knowledge, doesn't display this behavior when I retrieve
    rows from a database. My CSV file does not have an empty line at the end.

    --
    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, Jul 21, 2005
    #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. Nakhi

    datareader.getstring(0) error

    Nakhi, Jul 14, 2003, in forum: ASP .Net
    Replies:
    3
    Views:
    2,119
    Nakhi
    Jul 15, 2003
  2. Hung Huynh
    Replies:
    8
    Views:
    298
    Bob Barrows
    Sep 24, 2003
  3. andrewdreib

    Request.querystring or Recordset.GetString?

    andrewdreib, Feb 28, 2005, in forum: ASP General
    Replies:
    1
    Views:
    163
    Jason Brown [MSFT]
    Mar 3, 2005
  4. Roland Hall
    Replies:
    14
    Views:
    268
    Roland Hall
    Jul 23, 2005
  5. Roland Hall
    Replies:
    3
    Views:
    178
    [MSFT]
    Jul 26, 2005
Loading...

Share This Page