How to read whole line from txt file with row delimiter?

I have text file with row, which can have several row separators. How to divide this big row into several rows using VBA?. I need to import this file. I do so (Sep is the field delimiter):

Open FName For Input Access Read As #1

While Not EOF(1)
   Line Input #1, WholeLine
   If Right(WholeLine, 1) <> Sep Then
       WholeLine = WholeLine & Sep
   End If

   StrArray = Split(WholeLine, Sep)
Wend

or may be you can suggest me anothe开发者_如何学编程r way to import data from txt file with field and row separators?

Well you have a lot of things in your question that need clarification, but maybe this will help you in the right direction. I am going to assume you are appending this to a table. You'll have to change the following things for your scenario: 1. ImportTableName 2. FieldName 3. FileName 4. sDelimiter - probably this too. also don't forget to add some error handling. If you drop out without closing the file, it stays locked and will affect your attempts at retrying the import.

Const sDelimiter As String = " sep "
Dim rsImport As ADODB.Recordset
Dim vShortLine As Variant
Dim arrLongLine As Variant
Dim sLongLine As String
Set rsImport = New ADODB.Recordset
With rsImport
    .Open ImportTableName, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    Open FileName For Input As #1    ' Open file.
    While Not EOF(1)
        Line Input #1, sLongLine
        arrLongLine = Split(sLongLine, sDelimiter)
        For Each vShortLine In arrLongLine
            .AddNew
            !FieldName = vShortLine
        Next
    Wend
    Close #1    ' Close file.
    .UpdateBatch
    .Close
End With
Set rsImport = Nothing