Need to Import a text file line by line

[ThereUare]

I have a text file that is not in a friendly format for Access. Can someone provide me help to get the following into a table?
TextFile>

Name: Blow Joe DOB: 17Oct78

and so on
EndofFile<

It has more but I just need to get started.

 

[AnotherHiggins]

- Data > Text to Columns
- File Delimited
- Delimiter is [highlight]:[/highlight]

Either choose not to import the columns containing the categories or delete them once parsed.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ThereUare]

Well my problem is that I don't know the code to insert them into columns. The text file contains alot more information that I do not need. I need to be able to search for 'Name:' and insert the value after that into a column. Then move onto "DOB:" and insert those values into another column.
Thanks for anything you can provide me. I could really use some example code.

 

[AnotherHiggins]

Sorry about that. "Data > Text to Columns" is for Excel, not Access.

But at the same time, this is not the correct forum in which to ask about VBA, and you never said you were looking for code, so I provided a non-code answer. When importing into Access, you can still use colons as your delimiter to get everything into separate fields.

If you need help with code/vba/macro, please post a new thread in forum707.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[TomCologne]

anotherhiggins is right about the forum but since we're already here, I've posted some code under: thread68-1409298: readout textfiles to table

Maybe that helps to get you started.

TomCologne

 

[TomCologne]

Lots of time on my hands today;-)
If you're dealing with a single file you could use something like this:

[COLOR=green]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'' References:
'' MS DAO 3.6
'' MS Scripting Runtime
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/color]
Sub ReadTxtImport01()
On Error GoTo Err_ReadTxtImport01

Dim fs As FileSystemObject, f As File, ts As TextStream  [COLOR=green]'' Variables for FileSystemObject
'Dim rst As DAO.Recordset[/color]
Dim strFile As String
Dim strLine As String
Dim strName As String, strDOB As String

strFile = "C:\TextFiles\sample04.txt"

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(strFile)

        Set ts = f.OpenAsTextStream(1, -2)
        [COLOR=green]'Set rst = CurrentDb.OpenRecordset("tblTextFiles")[/color]
        
            Do While ts.AtEndOfStream <> True
                
                strLine = ts.ReadLine
                strName = Mid(strLine, InStr(strLine, "Name: ") + 5, InStr(strLine, "DOB: ") - 6)
                strDOB = Mid(strLine, InStr(strLine, "DOB: ") + 4)
                            
                            Debug.Print strName, strDOB
                            
[COLOR=green]'                            With rst
'
'                                .AddNew
'
'                                !Name = strName ''Text field
'                                !DOB = strDOB ''Text field
'
'                                .Update
'
'                            End With[/color]
'
                        
            Loop

Exit_ReadTxtImport01:

ts.Close
[COLOR=green]'rst.Close

'Set rst = Nothing[/color]
Set ts = Nothing
Set f = Nothing
Set fs = Nothing

Exit Sub

Err_ReadTxtImport01:

Debug.Print Err.Number, Err.Description

End Sub

Hope this helps.

TomCologne

 

[ThereUare]

Sorry for not replying earlier. Thanks to all who made suggestions. I tried the code but I'm not having any luck. I'll keep tinkering with it.

 

[TomCologne]

What's the problem?

TomCologne