How to automate this import

[Roy]

Hello,
As a part of a data dump from a source,I will be getting a
text file which has to imported into Access table.

But the file dump from Unix is kinda convoluted and inspite of using
the line import method or with few pointers from previous posts,I am
unable to do so.

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



||

As you see,its follows no specified format.
Kindly help

Roy

[pietlinden]

I'm not sure this reply will be much help, but here goes.

is there any way you can explain what all the fields/data are? (Or can
you get that from someone?) I just did something like this, but far
more understandable. but without a key of some kind to understand what
all these data are, it's nearly impossible to put the information in
any kind of logical order.

[Roy]

Hi,

Thanks for the reply but it did not contain your explanation.Also
to answer your point,there is no header information.the first fild is a
integer field and the rest are text.Does this help you?

[pietlinden]

Are the delimiters between the fields constant?
If you have that, you can read the lines one at a time and then use
Split to break the pieces into individual values.

You might need to create an array or something to hold the parsed
values before you write them to a recordset/table.

How much of this do you know how to do or need help with?

[Roy]

This is the code i used which was from one of the Access gurus on this
group.The code works till before the recordset,RS2.
After that it bombs with a error saying "item not found".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

Thanks

[thetuckers_jersey]

My two-penneth, I use a program called Textpad which is basically a
text editor but extremely powerfull. You can create macros to
manipulate your data ie comma delimenate fields, block select/deklete
bioth horizontally & vertically etc. Definitely worth a look.

Regards

[thetuckers_jersey]

My two-penneth, I use a program called Textpad which is basically a
text editor but extremely powerfull. You can create macros to
manipulate your data ie comma delimenate fields, block select/deklete
bioth horizontally & vertically etc. Definitely worth a look.

Regards

[Roy]

Textpad?

[Roy]

RichP,
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
||