Import Text File - 4 lines per row

[The Boondock Saint]

Howdy all,
Ive been looking on google etc for a answer but ive had no luck at all.
Ive got a text file which will always put out data in 4 lines per listing..
for example

Goats
123
Goats are lovely
$10
Rabbits
125
Rabbits are fury
$20
Cows
193
Cows make milk
$100

etc etc what id like to be able to do, is automatically bring that data in
to a table with 4 fields

basically it would be like

Goats | 123 | Goats are lovely | $10
Rabbits | 125 | Rabbits are fury etc etc

Does anyone have any idea on how I could do this,as I cant change the format
of the text file.

Cheers Saint

 

[Douglas J. Steele]

My advice would be to read the file in line-by-line in VBA (using the Line
Input # Statement), and write to the database after every 4th row:

Dim curField4 As Currency
Dim intLoop As Integer
Dim intFile As Integer
Dim intFIeld2 As Integer
Dim strBuffer As String
Dim strField1 As String
Dim strField3 As String
Dim strSQL As String

intFile = FreeFile()
Open "C:\Folder\File.txt" For Input As #intFile
intLoop = 1
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
Select Case intLoop
Case 1
strField1 = strBuffer
Case 2
intField2 = CInt(strBuffer)
Case 3
strField3 = strBuffer
Case 4
curField4 = CCur(strBuffer)
strSQL = "INSERT INTO MyTable " & _
"(Field1, Field2, Field3, Field4) " & _
"VALUES ('" & strField1 & "', " & _
intField2 & ", '" & strFIeld3 & "', " & _
curField4 & ")"
CurrentDb.Execute strSQL, dbFailOnError
intLoop = 0
End Select
intLoop = intLoop + 1
Loop

Close #intFile

 

[The Boondock Saint]

Thanks for the awesome reply Douglas

Ive got it working lovely,
I notice that if the string is longish (50ish) it cuts off the ending when
putting it into a text field, is there any way around this?

Cheers Saint

 

[Douglas J. Steele]

How is the field defined in the table? 50 characters is the default length
for a text field: perhaps you need to increase it.

 

[The Boondock Saint]

Your spot on, it was set at 50, it says that it can be upto 255 for a text
field, if I had one that was longer (not that I should) what would be the
best field type to choose that would accept the strings.

Thanks for your help once again, thats really awesome.
Saint

 

[Douglas J. Steele]

If you need longer than 255 characters, you'll have to use a Memo field
instead.