Repost : Reading a Text File

[Roy]

This was the code I used which was suggested by you.Can you
help me here?
The code works till before the recordset,RS2. After that it bombs with
a error saying "item not found in this collection".I tried to add
additional fields but to no avail.
DataFeed_001 is a table with a single memo field .Feed _002 is the
final table to which the data is parsed.
Private Sub cmdimportText_Click()
Dim db As DAO.Database, i As Integer, j As Integer
Dim k As Integer, m As Integer, n As Integer, p As Integer
Dim RS1 As DAO.Recordset, RS2 As DAO.Recordset
Dim SourceDoc As String, str1 As String
DoCmd.SetWarnings False
DoCmd.OpenQuery "FeedDelete"
DoCmd.SetWarnings True
Set db = CurrentDb
Set RS1 = db.OpenRecordset("DataFeed_001**")
SourceDoc = "C:\Roy\DataFeed.txt"
Open SourceDoc For Input As #1
i = 0
Do While Not EOF(1)
Line Input #1, str1
RS1.AddNew
RS1(0) = str1
RS1.Update
RetVal = SysCmd(acSysCmdSetStatus, i)
i = i + 1
Loop
Close #1
RS1.MoveFirst
Set RS2 = db.OpenRecordset("Feed_002")
Do While Not RS1.EOF
str1 = RS1(0)
i = 1
j = 1
RS2.AddNew
For k = 0 To RS2.Fields.COUNT - 1
j = InStr(i, str1, " ")
m = Len(str1) - (Len(str1) - j)
p = m - i
str2 = Trim(Mid(str1, i, p))
RS2(k) = str2
i = j + 1
Next
j = Len(str1) - i - 1
str2 = Right(Trim(str1), j)
RS2(k) = str2 ' --this is the last column in table------it bombs
here................
RS2.Update
RS1.MoveNext
RetVal = SysCmd(acSysCmdSetStatus, "Parsing " & n)
n = n + 1
Loop
RS1.Close
RS2.Close
End Sub
The file from Unix is below"
the file from unix is as below:
1000 NULL 33497 Sorborate and
Internment
tank Hitilirect Plavdorm and Business Systems inte
Application Services Incident/Outage caused

by software failure. Incident/Outage caused by software failure.
Int BIB - Dash
Cash Management
Asia-Pacific New Hitisard customers unable
to access
ATM & hitilirect |GitiNone Brisbane,
Australia| Yek-Ling Chong
|With joint effort from Singapo| Aug 12 2001 10:58PM
Non-critical (Yellow) Closed Fe
Ganuelas
[phone]
NULL
NULL
NULL
NULL NULL NULL
NULL
NULL NULL
NULL NULL
NULL
Jul 27
2001 3:30PM Aug 11 2001 8:00PM Not Implemented
NULL NULL
NULL ||
(GMT+10:00) East Australian Standard Time
NULL
NULL NULL
NULL
NULL NULL
NULL NULL NULL
NULL
||

[Randy Harris]

You don't have that assignment inside your for...next loop. By the time you
execute it, k will be past that last field in your table.
(RS2.Fields.Count -1) is the last field in the table. Suppose you have 3
fields...(RS2.Fields.Count would equal 3) RS2(0) is the first, RS(1) is the
second, RS(2) would be the last field.

So, if you want to assign a value to the last field in the table, use:
RS2(RS2.Fields.Count - 1) = str2 ' Don't use a For...Next index outside of
the For...Next loop!
"Roy" <pr********@yahoo.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
This was the code I used which was suggested by you.Can you
help me here?
The code works till before the recordset,RS2. After that it bombs with
a error saying "item not found in this collection".I tried to add
additional fields but to no avail.
DataFeed_001 is a table with a single memo field .Feed _002 is the
final table to which the data is parsed.
Private Sub cmdimportText_Click()
Dim db As DAO.Database, i As Integer, j As Integer
Dim k As Integer, m As Integer, n As Integer, p As Integer
Dim RS1 As DAO.Recordset, RS2 As DAO.Recordset
Dim SourceDoc As String, str1 As String
DoCmd.SetWarnings False
DoCmd.OpenQuery "FeedDelete"
DoCmd.SetWarnings True
Set db = CurrentDb
Set RS1 = db.OpenRecordset("DataFeed_001**")
SourceDoc = "C:\Roy\DataFeed.txt"
Open SourceDoc For Input As #1
i = 0
Do While Not EOF(1)
Line Input #1, str1
RS1.AddNew
RS1(0) = str1
RS1.Update
RetVal = SysCmd(acSysCmdSetStatus, i)
i = i + 1
Loop
Close #1
RS1.MoveFirst
Set RS2 = db.OpenRecordset("Feed_002")
Do While Not RS1.EOF
str1 = RS1(0)
i = 1
j = 1
RS2.AddNew
For k = 0 To RS2.Fields.COUNT - 1
j = InStr(i, str1, " ")
m = Len(str1) - (Len(str1) - j)
p = m - i
str2 = Trim(Mid(str1, i, p))
RS2(k) = str2
i = j + 1
Next
j = Len(str1) - i - 1
str2 = Right(Trim(str1), j)
RS2(k) = str2 ' --this is the last column in table------it bombs
here................
RS2.Update
RS1.MoveNext
RetVal = SysCmd(acSysCmdSetStatus, "Parsing " & n)
n = n + 1
Loop
RS1.Close
RS2.Close
End Sub
The file from Unix is below"
the file from unix is as below:
1000 NULL 33497 Sorborate and
Internment
tank Hitilirect Plavdorm and Business Systems inte
Application Services Incident/Outage caused

by software failure. Incident/Outage caused by software failure.
Int BIB - Dash
Cash Management
Asia-Pacific New Hitisard customers unable
to access
ATM & hitilirect |GitiNone Brisbane,
Australia| Yek-Ling Chong
|With joint effort from Singapo| Aug 12 2001 10:58PM
Non-critical (Yellow) Closed Fe
Ganuelas
[phone]
NULL
NULL
NULL
NULL NULL NULL
NULL
NULL NULL
NULL NULL
NULL
Jul 27
2001 3:30PM Aug 11 2001 8:00PM Not Implemented
NULL NULL
NULL ||
(GMT+10:00) East Australian Standard Time
NULL
NULL NULL
NULL
NULL NULL
NULL NULL NULL
NULL
||

[Roy]

Randy,
Thanks much for the reply.Now it does'nt come with
the error like "Item not found in this collection,but it says
"field too small to accept the data".Obviously it is not splitting the
memo field ?
This is my corrected code as suggested by you:
For k = 0 To RS2.Fields.COUNT - 1
j = InStr(i, str1, " ")
m = Len(str1) - (Len(str1) - j)
p = m - i
str2 = Trim(Mid(str1, i, p))
'str2 = Split(str1, Chr(9))

RS2(k) = str2
'RS2(RS2.Fields.COUNT - 1) = str2
i = j + 1
Next
j = Len(str1) - i - 1
str2 = Right(str1, j)
'str2 = Split(str1, Chr(9))
'RS2(k) = str2 ' --this is the last column in table
RS2(RS2.Fields.COUNT - 1) = str2

Is this okay?I did not get the line about the For..Next loop .Can you
explain.

Thanks,

Roy

[pietlinden]

when the code fails, what is the line it fails on? What field is being
written? What are your field sizes in your table? The problem seems
obvious - you might want to throw some code in there to determine where
the failure is occurring - either throwing in some real error trapping
or doing something like using Debug.Print to write the field name and
the value you're trying to write into it. then the fix should be
obvious.

[Roy]

Thanks for your comments,but maybe you fail to understand the
problem.There is one table with one memo filed where all
the text fields are read into.Then there is a second table with
required number of fields to which the data from table is parsed into.
If you can and have time,kindly copy and paste the text file i have
pasted and also the code which i use.The datatype in second table is
all text.