#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0

    Using GetRows to read a file not reading first record


    I am using VB6 to read a text file with the following code:

    Code:
         Set conn = New ADODB.Connection
        conn.Open _
          "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & ";" & _
          "Extended Properties=""text;HDR=No;FMT=Delimited( )"""
    
        Set cm = New ADODB.Command
        cm.ActiveConnection = conn
        cm.CommandType = adCmdText
        cm.CommandText = "SELECT * FROM c:\tempAscDir\tempAscFile.asc"
    
        Set rs = New ADODB.Recordset
        
        rs.Open cm, , adOpenKeyset, adLockOptimistic
    
        dataArray = rs.GetRows
    This works ok except that the first record in the tempAscFile.asc does not appear in the array darray.
    The record in darray(0,0) is the second record in the tempAscFile.asc.

    How can I get the first record in tempAscFile.asc?
    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    It is quite possible that Access Jet is looking for the field names in the first line. I have never used Access in this manner, and quite frankly I don't think I ever would. There are too many things that can go wrong with a delimited text file, and using a single command to import that data leaves you with a difficult troubleshooting problem when something does go wrong.

    I have a universal import program that I use to import comma separated financial data from different sources. The VB6 file system is used to read the data one line at a time, check the data for format, and then output it to the Access database. I can make the source code available to you if you so desire.

    J.A. Coutts
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    10
    Rep Power
    0
    It's much simpler:
    Code:
    Dim dataArray() As String
      Open "c:\tempAscDir\tempAscFile.asc" For Input As #1
      dataArray = Split(Input(LOF(1), 1), vbCrLf)
      Close #1

IMN logo majestic logo threadwatch logo seochat tools logo