Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    8

    Question Unanswered: Importing multiple lines into access (HELP ME PLS)

    I've got a flat file which is not delimitered in any way nor is it fixed length however the records are each consistantly on 3 seperate rows.

    My goal is to load this file into an access table then run some substr queries later to parse it.

    The problem i've got is getting access to load 3 rows of data then combining this as one long string and treating it as 1 record thus continuing till the next 3 and so on until eof.

    The way round it is probably to write VBA script, has anyone got any examples of as they would be much appriciated.

    TIA

    MultiD

  2. #2
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91

    Re: Importing multiple lines into access (HELP ME PLS)

    Hi ! try this:

    Code:
    
    Dim Linha As String
    
    Dim rs as ADODB.RecordSet
    rs= New recordset
    
    with rs
        .CursorType = adOpenForwardOnly
        .LockType = adLockOptimistic
        .Source = "SELECT Field1, Field2, Field3 FROM youTable"
         .Open , CurrentProject.Connection
    end with
    
    NomeArq = "c:\sistemas\ponto\arquivo.txt"  ' Path + name of your file
    
    Open NomeArq For Input As #1
    
    Do While Not EOF(1) 
    
        Line Input #1, Linha ' Reading lines
    
     
    'As you didnīt say the length of your line, I defined it as 30 characters, and 10 chars for each column
    
    
        rs.AddNew
            RS!Field1 = Mid(Linha, 1,  10)
            RS!Field2 = Mid(Linha, 11, 20)
            RS!Field3 = Mid(Linha, 21,  30)
    
            
        rs.Update
    Loop
    
    
    ' Fecha antes de reabrir em outro modo.
    Close #1
    
    rs.Close
    
    Set rs=Nothing
    
    MsgBox "Thatīs all Folks!", vbInformation
    I didnīt test the code, but should work.
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

Posting Permissions

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