Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reading csv file (Access 2000)

    I would like to know how I can access the very first row of data stored in a CSV file using Access 2000. After I have opened the recordset, the pointer is always pointing to the second row, assuming the first row contains the field names. What if the first row is also data?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reading csv file (Access 2000)

    How are you opening the recordset? Have you created a link to the CSV file in the database, or are you opening it directly? Are you using DAO or ADO?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading csv file (Access 2000)

    Yes I did and here is the code:

    Dim rstUFG As New ADODB.Recordset

    Dim strFilePath As String
    strFilePath = "c:download"

    Dim strFileName As String
    strFileName = "test.csv" '

    Set m_cn = New ADODB.Connection
    With m_cn
    .CursorLocation = adUseClient
    .ConnectionString = _
    "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "Initial Catalog=" & strFilePath & ";"
    .Open
    End With
    rstUFG.Open "select * from test.csv", m_cn, adOpenDynamic, adLockOptimistic
    rstUFG.MoveFirst
    Do While rstUFG.EOF <> True
    Debug.Print rstUFG.Fields(0), rstUFG.Fields(1), rstUFG.Fields(2)
    rstUFG.MoveNext
    Loop

    I am using ADO and the first row of data is skipped.

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reading csv file (Access 2000)

    I got around it by creating a DSN that specifies that there is no column header row, and used this in the connection string:

    .ConnectionString = "FILEDSN=C:Program FilesCommon FilesODBCData SourcesTest.dsn;"

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading csv file (Access 2000)

    Thanks Hans,

    Is there any other way to get around with the problem without the DSN stuff?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reading csv file (Access 2000)

    I couldn't think of another solution, perhaps another Lounger has a brighter idea?

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Reading csv file (Access 2000)

    I struggled with using the ODBC provider (MSDASQL.1) with no luck; recordset refused to return 1st row of .CSV text file w/o field headings. To solve problem, use the Jet OLEDB provider instead of ODBC as shown in this example:

    Public Sub adoTestOpenCSVRecordset()
    On Error GoTo Err_Handler

    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim strConn As String
    Dim strSQL As String
    Dim strMsg As String

    Set cnn = New ADODB.Connection

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:ACCESS;" & _
    "Extended Properties=""text;HDR=NO;FMT=Delimited;"";"

    cnn.ConnectionString = strConn
    cnn.Open
    Debug.Print cnn.ConnectionString

    Set rst = New ADODB.Recordset
    strSQL = "SELECT * FROM Products.txt"
    rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic

    Debug.Print rst.ActiveConnection

    With rst
    .MoveFirst
    Do Until .EOF
    Debug.Print .Fields(0), .Fields(1), .Fields(2)
    .MoveNext
    Loop
    .Close
    End With
    cnn.Close

    Exit_Sub:
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "ADO CSV RECORDSET ERROR"
    Resume Exit_Sub

    End Sub

    I tested this with .CSV text file "Products.txt" exported from Northwind.mdb w/o field headings. The above sub returned all rows, including first row (data). Note connection string extended properties specifies: HDR=NO; this is same parameter you will see when you link text file "manually" & check the link specification connection string. I would like to take credit for this but actually got idea from this MSKB article:

    HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases

    HTH

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading csv file (Access 2000)

    Thanks Mark,

    It just works greatly and exactly the way that I want. Thanks to Hans for the insight as well.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading csv file (Access 2000)

    Mark,

    One more issue to bother you. Now I can read the first row, instead of treating it as field names, how can I specify that column A should be field(1) instead of (0)?

    Thanks

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Reading csv file (Access 2000)

    If you mean you want the first field to be identified as Field(1), sorry, I don't think you have any choice in the matter. The ADO Fields Collection has the same properties as any other collection. As noted in ADO "Help" files:
    <hr>The Item property is the default property for all collections; therefore, the following syntax forms are interchangeable:

    collection.Item (Index)
    collection (Index)

    Index A Variant expression that evaluates either to the name or to the ordinal number of an object in a collection.<hr>
    The other property that applies to all ADO Collections is the Count property. As noted in ADO Help:
    <hr>Count Property
    Indicates the number of objects in a collection.

    Return Value
    Returns a Long value.

    Remarks

    Use the Count property to determine how many objects are in a given collection.
    Because numbering for members of a collection begins with zero, you should always code loops starting with the zero member and ending with the value of the Count property minus 1. If you are using Microsoft Visual Basic and want to loop through the members of a collection without checking the Count property, use the For Each...Next command. <hr>

    (Emphasis added.) In other words, all Collection indexes (indices?) are zero-based. So AFAIK you are stuck with Field(0) for first column. Other option is to use field (column) headings in the text file.

    HTH

  11. #11
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading csv file (Access 2000)

    Mark,

    I think there is a solution to it.

    Instead of telling the system

    Debug.Print rst.Fields(0), rst.Fields(1), rst.Fields(2)

    you can use

    Debug.Print rst!f1, rst!f2, rst!f3

    In fact rst!f1 is the same as your rst.fields(0)

    Cheer.

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Reading csv file (Access 2000)

    In further reply, your other option would be to refer to the Fields collection Item index by name rather than ordinal number. For example, here is modified excerpt of sample code posted in original reply:

    Dim i as Integer
    Set rst = New ADODB.Recordset
    strSQL = "SELECT * FROM Products.txt"
    rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
    rst.Fields.Refresh

    With rst
    .MoveFirst
    Do Until .EOF
    ' For test purposes print field names:
    ' For i = 0 To rst.Fields.Count - 1
    ' Debug.Print .Fields(i).Name & " " & .Fields(i).Value
    ' Next i
    ' Debug.Print ' Blank line between records

    ' Debug.Print .Fields("F1"), .Fields("F2"), .Fields("F3")
    ' Simpler syntax:
    Debug.Print !F1, !F2, !F3
    .MoveNext
    Loop
    .Close
    End With
    cnn.Close

    If you uncomment the "For i = 0 To rst.Fields.Count - 1" loop & run code, you will see that if there are no column headings defined, the fields will be named "F1", "F2", etc in sequence. Thus you can reference the fields by this name instead of the item number, as shown above in this line:

    Debug.Print .Fields("F1"), .Fields("F2"), .Fields("F3")

    Or, simplified:

    Debug.Print !F1, !F2, !F3

    Other than this, I know of no other options; a Field object's Name property is read-only.

    HTH

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Reading csv file (Access 2000)

    Thanx, as noted I figured it out in meantime, had not seen your most recent reply....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •