parse text (2 Viewers)

[Philmore]

I have a text file in this format:

Name: Bill Gates
address1:One Microsoft Way
address2:Redmond, Washington
address3:USA

and I want it in this format.
address1 address2 address3
One Microsoft Way Redmond, Washington USA

 

[Fuga]

In a query, make a new field and put:

[adress1] & " " & [adress2] & ", " & [adress3]

Is that what you want?

Fuga.

 

[Fuga]

Just realized that you had posted this in the VBA forum, so you are probably not looking for a query.

What is it you want to do?

Fuga.

 

[Philmore]

I have a text file that has a record spanning three lines with no delimiter.

 

[namliam]

and you want to put that into a table?

That shouldnt be to hard...

Let me know.

Regards

 

[Philmore]

can you help me
this is what i have
address1
address2
address3
address1
address2
address3
address1
address2
address3

this is what i want
address1 address2 address3
address1 address2 address3
address1 address2 address3

 

[namliam]

Sub importTextFile()
Dim myLine As String
Dim myCount As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("YourTable")
Open "C:\test.txt" For Input As #1
Input #1, myLine
myCount = 1
Do While Not EOF(1)
    Select Case myCount
        Case 1
            rs.AddNew
            rs.Fields("Address1") = myLine
        Case 2
            rs.Fields("Address2") = myLine
        Case 3
            rs.Fields("Address3") = myLine
            rs.Update
            myCount = 1
    End Select
    
    Input #1, myLine
Loop

End Sub

without more detailed information above is all i can do for you. try and addapt it to your needs...

Regards

 

[Philmore]

with one case it appends the firstline of the text file to the table but if i put the other case 2 and three in the statement it does not work.

 

[directormac]

Methinks you need a statement in both case 1 and 2 that increments intMyCount, else it's always case 1.

But I just woke up...

--Groggy Mac

 

[Philmore]

I'm not sure I understand

 

[namliam]

Mac's suggestion is "on the money"

Sub importTextFile()
Dim myLine As String
Dim myCount As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("YourTable")
Open "C:\test.txt" For Input As #1
Input #1, myLine
myCount = 1
Do While Not EOF(1)
    Select Case myCount
        Case 1
            rs.AddNew
            rs.Fields("Address1") = myLine
        Case 2
            rs.Fields("Address2") = myLine
        Case 3
            rs.Fields("Address3") = myLine
            rs.Update
            myCount = 0
    End Select
    
    Input #1, myLine
    myCount = myCount + 1
Loop

End Sub

or:

Sub importTextFile()
Dim myLine As String
Dim myCount As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("YourTable")
Open "C:\test.txt" For Input As #1
Input #1, myLine
myCount = 1
Do While Not EOF(1)
    if myCount = 1 then rs.addnew
    rs.Fields("Address" & myCount) = myLine
    if mycount = 3 then 
        rs.Update
        myCount = 0
    End if 
    
    Input #1, myLine
    myCount = myCount + 1
Loop

End Sub

Either should now work...

Regards

 

[Philmore]

Thank's a million guys.