How do I parse a field into records 2

[calandrelli]

I was given a dataset today that looks like this...

Field Value
Part# 97510
TID 100000000
Doc_Type TCP
Stage 5P
Panels 9108B0003,9108D0003,9108L0003,9108M0003
Materials 05695,07212,1980B,1980C,1980D
Revision 10

As you can see the Panels and Materials fields are multi-value fields. I need to parse the fields into multiple records but I don't have a clue where to begin. The number of values in each field is variable and the character length of each value is variable. Any ideas an where to start?

 

[lameid]

The text functions come to mind... INSTR, MID, left, right and Len.

In addition to that you will need recordsets. One to read the data and one or more to write it.

The below should help get you started.

Dim RS as Recordset 'ADODB or DAO doesn't matter you choose
'Open the Recordset
Do 

   RS.AddNew
   RS!Field = Left(strIn, instr(1,strIN,",") - 1)
   RS.Update
   strIn = Right(strIN,Len(strIn -instr(1,strIN,","))
Loop Until instr(1,strIN,",") = 0

 

[PHV]

If you'll do it by code then I suggest the Split function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[calandrelli]

Thank you both for the advice. I used both approaches and came up with the following code which worked great. tbl_LIMS_Cleaned contained the field I needed to parse. tbl_LIMS_Materials was a blank table I created to hold the parsed data. I am also capturing the Primary key from tbl_LIMS_Cleaned so I can tie the parsed data back to the original record.

Sub Split_Material()
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim intCounter As Integer
Dim PK1 As String 'Primary Key in tbl_LIMS_cleaned
Dim MT As String 'Field to be parsed
Dim mat 'Array populated by the split funtion
Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
With rst1
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open "select * From tbl_LIMS_Cleaned"
End With
With rst2
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open "select * From tbl_LIMS_Materials"
End With

Do Until rst1.EOF
    PK1 = rst1.Fields("Prime_Key")
    If IsNull(rst1.Fields("Materials")) Then GoTo Null_Jump
    MT = rst1.Fields("Materials")
    mat = Split(MT, ",")
    For intCounter = 0 To UBound(mat)
        rst2.AddNew
        rst2.Fields(0) = PK1
        rst2.Fields(1) = mat(intCounter)
        rst2.Update
    Next intCounter
Null_Jump:
    rst1.MoveNext
Loop
rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing
End Sub

 

[PHV]

You may get rid of the Null_Jump stuff by simply reduce the resultset of rst1:
.Open "select * From tbl_LIMS_Cleaned WHERE NOT (Materials IS NULL)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886