Separating imported 1-column text file into a 2-columns 2

[qjade]

Hello friends,
I am attempting to import a text file into my Access dB I would like to get additional help on how to manipulate the layout of the data within that file. A sample file would look like the following with "LocCode" being the column heading:

LocCode
R01CODE1
F00111111
F00222222
F00333456
R01CODE9
MISCELLANEOUS
JUNK
F00444444

What I would like to do is separate this one-columned text file into 2 separate columns where any record that begins with "R01" will occupy the first column. Any data afterward will occupy the second column until the next "R01" is read. The desired outcome would be as follow with "Data" as the 2nd column's heading:

LocCode Data
R01CODE1 F00111111
R01CODE1 F00222222
R01CODE1 F00333456
R01CODE9 MISCELLANEOUS
R01CODE9 JUNK
R01CODE9 F00444444

I hope my rambling made a little sense. Please let me know if I can clarify anything. Thanks for reading and lending a helping hand.

 

[PaulF]

This code imports a Text file named LocCode.txt and stored in the "C:\" root folder. It populates a table called tblLocCode with fields LocCode and Data.

Dim db As DAO.Database, rst As DAO.Recordset
Dim strLC As String, strIn As String
Set db = CurrentDb
Set rst = db.OpenRecordset("tblLocCode")
Open "C:\LocCode.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, strIn
If Len(Nz(strIn, "")) = 0 Then GoTo MoveNextHere
If strIn = "LocCode" Then GoTo MoveNextHere
If Left(strIn, 3) = "R01" Then
strLC = strIn
GoTo MoveNextHere
End If
With rst
.AddNew
!LocCode = strLC
!Data = strIn
.UPDATE
End With
MoveNextHere:
Loop
Close #1
Set db = Nothing
Set rst = Nothing
MsgBox "Import Complete"

PaulF

 

[qjade]

Thank you for responding so quickly Paul. I attempted your suggestion but my experience is very lacking so please let me know what I am doing wrong.
1. I created a module with your code above and saved it as Public Function LocCodeInput.
2. I then created a macro with the action of "RunCode" pointing to the Function name of "LocCodeInput".

I tried to run the macro Paul but ran into a compile error of "User-defined type not defined" for the first line of your code (' db As DAO.Database'). Thanks again for your help and hope to hear back from you soon.

 

[MichaelRed]

Public Function basCurrCodeData(MyInput As String) As Boolean

    'Michael Red    12/14/2004  Tek-Tips Thread705-967890 _
     Sample Usage:
     Print basCurrCodeDate("C:\Documents and Settings\MIchael-Admin\My Documents\MsAccess\LocCode.Txt") _
        ; False


    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Dim strOneCol As String
    Dim CurrCode As String
    Dim StrCodeData() As String
    Dim Idx As Long

    strOneCol = basGrabFile(MyInput)
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblLocCodeData", dbOpenDynaset)

    StrCodeData = Split(strOneCol, vbCrLf)

    With rst
        While Idx < UBound(StrCodeData)
    
            'Assumes the 'R01CODE*' is a loc code.
            If (Left(StrCodeData(Idx), 7) = "R01CODE") Then
                'It is a location Code
                CurrCode = StrCodeData(Idx)
             Else
                'It is a "Data Value"
                .AddNew
                    !LocCode = CurrCode
                    !LocData = StrCodeData(Idx)
                .Update
            End If
            Idx = Idx + 1
        Wend
    End With

    Set rst = Nothing
    Set dbs = Nothing

End Function
Public Function basGrabFile(FilIn As String) As String

    'Michael Red    3/3/2003
    'Sample Usage:  ? basGrabFile("C:\MsAccess\DrawArcsInVB.Txt")
    'Note the Arg [FilIn] is the FULLY QUALIFIED PATH of the Source _
     and the entire text is returned to the caller [prog | procedure]

    Dim MyFil As Integer

    Dim MyTxt As String
    Dim MyPrts() As String
    Dim MyPrtRec() As String

    'Just grab the Stuff
    MyFil = FreeFile

    Open FilIn For Binary As #MyFil

    MyTxt = String(LOF(MyFil), " ")
    Get #MyFil, 1, MyTxt

    Close #MyFil

    basGrabFile = MyTxt

End Function

MichaelRed

 

[MichaelRed]

Missing the ref to the DAO library. COde window. Tools, References. Scroll down to Microsoft things. Se3lect the "latest" (e,g, Highest #) for the DAO library.





MichaelRed

 

[qjade]

Thanks a million to both Michael and Paul. I went with Paul suggestion before yours Michael. However, couldn't have done it without your last error/life saving suggestion. It still amazes me how wonderful the members of this forum are when you guys continually give helping hands to complete strangers, such as myself, almost as instantaneous after questions is posted. Keep up the miracle works you guys!!!!