Parser help needed (1 Viewer)

R

RSW

Registered User.
Local time
Today, 01:18
Joined
May 9, 2006
Messages
178
I really hope this is easy...I inherited the maintenance of an Access database from someone...I am OK with Access, but a complete novice at VBA.

My database needs to read manufacturing log files. Here's a very simplistic idea of what the files USED to look like:

Partition number: 1
Serial number: 1001
_________________
Partition number: 1
Placement #: 1
Part#: 12345
Partition number: 1
Placement #: 2
Part#: 23456

However they have changed the process so that the equipment builds several serial numbers at once, in different partitions. So a log file might look like this:

Partition number: 1
Serial number: 1001
Partition number: 2
Serial number: 1002
Partition number: 3
Serial number: 1003
_________________
Partition number: 1
Placement #: 1
Part #: 12345
Partition number: 2
Placement #: 1
Part #: 12345
etc...

The point of this database is to link Part # with Serial number. Since there was only one serial number per log file before, there is a
Dim Tile_Serial as String
statement in the front of the VBA code, as the parser expects to read one and only one serial number.

I am hoping to find out two things:

1. How do I change Tile_Serial into an array or something so that I can match parts to serial numbers? The serial numbers per build can vary between one and three.

2. Assuming I make it an array, how will it be treated differently from string variables? They are all I have so far. Basically I need to ensure that as the parser goes down the log file and sees a "Partition number", it stores that somehow so it will be linked to the Serial number through that Partition number.

I am really, really hoping this is easy to update. I just got put on an assignment I'm not yet qualified for, and it's taken me forever to decipher what this code actually does. Any help anyone can provide will be really appreciated!!
 
M

Moniker

VBA Pro
Local time
Today, 03:18
Joined
Dec 21, 2006
Messages
1,567
What format is that log file in? You really should need very little programming at all to do this, as it can probably all be done in tables and queries, but it depends on the format of that file.
 
R

RSW

Registered User.
Local time
Today, 01:18
Joined
May 9, 2006
Messages
178
Moniker said:
What format is that log file in? You really should need very little programming at all to do this, as it can probably all be done in tables and queries, but it depends on the format of that file.
Click to expand...

Unfortunately, it is a flat text file. The parser is already all set up, I just need to update it for handling multiple serial numbers at once.
 
M

Moniker

VBA Pro
Local time
Today, 03:18
Joined
Dec 21, 2006
Messages
1,567
Do you always have the list of Partition Numbers and Serial Numbers at the top, and then the list of Partition, Placement, and Part second? In other words, is it a giant list like this:

Partition number: 1
Serial number: 1001
Partition number: 2
Serial number: 1002
Partition number: X
Serial number: X

And then this part starts:

Partition number: 1
Placement #: 1
Part #: 12345
Partition number: x
Placement #: x
Part #: x

If that's the case, this will become fairly straightforward.
 
R

RSW

Registered User.
Local time
Today, 01:18
Joined
May 9, 2006
Messages
178
Exactly! I thought it would be straightforward, I just have never done this before and I am looking at someone else's code.
 
R

RSW

Registered User.
Local time
Today, 01:18
Joined
May 9, 2006
Messages
178
Sorry to bump...can anyone assist?
 
W

WayneRyan

AWF VIP
Local time
Today, 09:18
Joined
Nov 19, 2002
Messages
7,122
RSW,

I hate getting data like that!

If you are trying to get the unique Partition/Serial/Part/Placement it
can be surprisingly easy.

Code: 
Dim rst As DAO.Recordset
Dim strPartition As String
Dim strSerial    As String
Dim strPart      As String
Dim strPlacement As String

Dim strBuffer As String

Set rst = CurrentDb.OpenRecordset("Select * From YourTable")

Open "C:\SomeFile.txt" For Input As #1

Line Input #1, strBuffer
While Not EOF(1)
   If InStr(strBuffer, "Partition") > 0 Then
      strPartition = Mid(strBuffer, Instr(strBuffer, ": ") + 2)
   ElseIf InStr(strBuffer, "Serial") > 0 Then
      strSerial = Mid(strBuffer, Instr(strBuffer, ": ") + 2)
      '
      ' Save after every new serial #
      '
      rst.AddNew
      rst!Partition = strPartition
      rst!Serial = strSerial
      rst!Part = strPart
      rst!Placement = strPlacement
      rst.Update
   ElseIf InStr(strBuffer, "Part") > 0 Then
      strPart = Mid(strBuffer, Instr(strBuffer, ": ") + 2)
   ElseIf InStr(strBuffer, "Placement") > 0 Then
      strPlacement = Mid(strBuffer, Instr(strBuffer, ": ") + 2)
   End If
   Line Input #1, strBuffer
   Wend
Close #1

hth,
Wayne
 
R

RSW

Registered User.
Local time
Today, 01:18
Joined
May 9, 2006
Messages
178
I will try something like that and let you know! Thanks!
 
R

RSW

Registered User.
Local time
Today, 01:18
Joined
May 9, 2006
Messages
178
Wayne,

I am not sure I understand the code (remember I am a VBA novice).

I understand how it adds records for the Partition, Serial, Part and Placement. However I am not sure I understand where/how the one-to-one relationship between Partition and Serial is stored. I don't care about Partition--only Serial--but unfortunately after listing their relationships in the beginning of the log file, the Serial numbers are not mentioned after that. Can you explain what I am missing?

Thanks!!
 
W

WayneRyan

AWF VIP
Local time
Today, 09:18
Joined
Nov 19, 2002
Messages
7,122
R,

The example posted only dealt with ONE simple table. Any further clarification
will depend on the existing table structure(s) within your database.

Wayne
 
B

Bat17

Registered User.
Local time
Today, 09:18
Joined
Sep 24, 2004
Messages
1,687
try this variation on Wayne's code. it assumes two tables that will be joined on 'Partition' and that in your text file the will be at least one blank line between the two sets of data.
Code: 
Dim rst As DAO.Recordset
Dim strPartition As String
Dim strSerial    As String
Dim strPart      As String
Dim strBuffer As String

Set rst = CurrentDb.OpenRecordset("Select * From tblText")

Open "H:\temp\texttest.txt" For Input As #1

Line Input #1, strBuffer
While Not Len(strBuffer) = 0
   If InStr(strBuffer, "Partition") > 0 Then
      strPartition = Mid(strBuffer, InStr(strBuffer, ": ") + 2)
   ElseIf InStr(strBuffer, "Serial") > 0 Then
      strSerial = Mid(strBuffer, InStr(strBuffer, ": ") + 2)
      '
      ' Save after every new serial #
      '
      rst.AddNew
      rst!Partition = strPartition
      rst!Serial = strSerial
      rst.Update
    End If
   Line Input #1, strBuffer
Wend
' fill second table
Set rst = CurrentDb.OpenRecordset("Select * From tblText2")
While Not EOF(1)
    Line Input #1, strBuffer
   If InStr(strBuffer, "Partition") > 0 Then
      strPartition = Mid(strBuffer, InStr(strBuffer, ": ") + 2)
   ElseIf InStr(strBuffer, "Part #:") > 0 Then
      strPart = Mid(strBuffer, InStr(strBuffer, ": ") + 2)
      '
      ' Save after every new Part #
      '
      rst.AddNew
      rst!Partition = strPartition
      rst!Part = strPart
      rst.Update
    End If
Wend
Set rst = Nothing
Close #1

Peter
 
R

RSW

Registered User.
Local time
Today, 01:18
Joined
May 9, 2006
Messages
178
Thanks everyone! Really appreciate the help.
 
You must log in or register to reply here.

Similar threads

S
Solved Can I use Dlookup/DMIN lookups within VBA where part of the criteria is looking for a 'null' value in a field?
Replies
8
Views
548
June7
June7
T
Solved Add new record from list box
Replies
5
Views
395
arnelgp
arnelgp
P
Exit Do loop
Replies
9
Views
589
gemma-the-husky
gemma-the-husky
P
Solved Dlookup Mismatch error
Replies
10
Views
714
Poco_90
P
P
Solved Seat license security
2
Replies
25
Views
1,384
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom