Text File input Manipulation with ACCESS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Below is the file data I need to manipulate into an ACCESS database or a VB
database. Here's the TXT file:

================================
REFDES LIST
Harness Assembly: 2CWH33203-0010,-,1
Effectivity: 2AA:4-4, 2AA:5-5, 2AA:1-1, 2AA:6-9999, 2AA:3-3, 2AA:2-2
Date: 2004-09-17
Sheet number: 1
..
..
..
..
..
..
=================================

The following is needed

1) Read records 2,3 and 4 only

2) Manipulate RECORD 2 into 3 fields as follows
2CWH33203-0010,-,1 (part_num, rev, seq)

3) RECORD 3
For each comma delimited effectivity, 3 fields as follows
2AA, 4, 4 (variant, start, stop)
So for this particular RECORD 3 there would be the following fields:
2AA,4,4,2AA,5,5,2AA,1,1,2AA,6,9999,2AA,3,3,2AA,2,2 (variant, start,
stop, variant, start, stop.....)
RECORD 3 fileds would require until end of line for RECORD 3 (here is a
guess but the total field, multiples of 3 for this record, is 75)

4) RECORD 4
Extract date and place it in a cell

I have VB6 Professional

Thanks
Jay
(e-mail address removed)
 
You have to write code to do this.
The wizards expect data in a table like structure.
Your file is not in that format.

Here is a skeleton to get you started.

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub
 
Back
Top