How can I import non-delimited text file into access

[Brandon]

Hello i have a non delimited text file and no fixed width either. How can I
import them.

Data has a name, street address and city state and zip

I need the name in one field, the street address in another and city state
and zip in a third field.

Data looks like this in notepad and has 4 blank lines between each record

Jerry Lessland
3145 J P Morgan Chase St
Toledo OH 43611




Anitra Newiliams
3001 Wanderer Dr
Memphis TN 38128

Any thoughts as to how i can import into fields in Access?
Thanks!

 

[DrEvil]

I don't know of any way to import data like that, maybe someone else does but
chances are slim.
Not sure how many records do you have and can you fix it manually or what
kind of program you are using to extract that data but maybe you can look a
those options.
Data needs to be normalized with clear indicators as to where does the field
stop or begin, and looks like you have data in 3 rows by your example.

Amir

 

[Brandon]

Yes it is basically 3 rows of data followed by 4 rows of space and i have
almost 100,000 records. When I opened the file in Word, there did appear to
be a return at the end of each line so i was wondering if there is a way to
tell access to take the first, second and third rows as fields and then skip
4 rows and repeat until the end of the file?
Brandon

 

[Douglas J. Steele]

You can use VBA to read the file.

Dim intFile As Integer
Dim intLine As Integer
Dim strBuffer As String
Dim strName As String
Dim strAddress As String
Dim strCity As String
Dim strFile As String

strFile = "C:\Folder\File.txt"
intFile = FreeFile()
Open strFile For Input As #intFile
intLine = 1
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
Select Case intLine
Case 1
strName = strBuffer
Case 2
strAddress = strBuffer
Case 3
strCity = strBuffer
' You now have the 3 lines. Write them to your table...
Case 4, 5, 6, 7
' Ignore
Case Else
' Should never happen...
End Select
intLine = intLine + 1
If intLine > 7 Then
intLine = intLine - 7
End If
Loop
Close #intFile