How to parse a record with multiple fields

[stevlee]

I have a table that contains log entries.
The table has two columns, the first column is a timestamp and the second column contains the data I need to parse. What I have to do is seperate the three fields in the second column into three columns.
Here is an example of two rows of data from the second column;

(TA:606:Duplicate Entry For ItemId SerialId ModelNo [12350901] [123456086632509] [2016] )
(TA:606:Duplicate Entry For ItemId SerialId ModelNo [45685470] [957125352146255] [14] )

I am not too familiar with Access 2000 but reviewing the forums I attempted to use instr with left and right functions to extract. Didn't work too well for me.
I think the brackets were the problem....

[Delerna]

just to clarify the question, what should the two records of data look like after running the query on them?

my guess is
12350901,123456086632509,2016
45685470,957125352146255,14

but im only guessing

[missinglinq]

Like Delerna said, we need to see what kind of results you want. Also, in the two examples below

(TA:606:Duplicate Entry For ItemId SerialId ModelNo [12350901] [123456086632509] [2016] )
(TA:606:Duplicate Entry For ItemId SerialId ModelNo [45685470] [957125352146255] [14] )

are the hilited fields always the same number of characters in length or do they vary?

Welcome to TheScripts!

Linq ;0)>

[ADezii]

I have a table that contains log entries.
The table has two columns, the first column is a timestamp and the second column contains the data I need to parse. What I have to do is seperate the three fields in the second column into three columns.
Here is an example of two rows of data from the second column;

(TA:606:Duplicate Entry For ItemId SerialId ModelNo [12350901] [123456086632509] [2016] )
(TA:606:Duplicate Entry For ItemId SerialId ModelNo [45685470] [957125352146255] [14] )

I am not too familiar with Access 2000 but reviewing the forums I attempted to use instr with left and right functions to extract. Didn't work too well for me.
I think the brackets were the problem....

As long as the data to be parsed remains within brackets ([ ]), and a single space exists between the data elements and at the end of the 3rd element, then it becomes a fairly simple matter. Assuming the 2nd Column is in a Field named [Field_Test], which is in a Table named tblTest, then it becomes a Query with three Calculated Fields calling 3 simple Functions:

SELECT tblTest.Field_Test, fParseString1([Field_Test]) AS Field1, fParseString2([Field_Test]) AS Field2, fParseString3([Field_Test]) AS Field3
FROM tblTest
WHERE (((tblTest.Field_Test) Is Not Null));

Public Function fParseString1(strString As String)
Dim varParsedString As Variant
 
varParsedString = Split(strString, "[")
 
'The 2nd Element in the Array will contain the 1st Parsed Field
fParseString1 = Left$(varParsedString(1), Len(varParsedString(1)) - 2)
End Function

Public Function fParseString2(strString2 As String)
Dim varParsedString As Variant
 
varParsedString = Split(strString2, "[")
 
'The 3rd Element in the Array will contain the 2nd Parsed Field
fParseString2 = Left$(varParsedString(2), Len(varParsedString(2)) - 2)
End Function

Public Function fParseString3(strString3 As String)
Dim varParsedString As Variant
 
varParsedString = Split(strString3, "[")
 
'The 4th Element in the Array will contain the 3rd Parsed Field
fParseString3 = Left$(varParsedString(3), Len(varParsedString(3)) - 3)
End Function

OUTPUT (not showing [Field_Test], the entire String):

Field1        Field2            Field3
12350901    123456086632509      2016
45685470    957125352146255      14
000000        1111                222222222222214               
 

NOTE: Last Record was a Test Record, the other 2 used the supplied data.

[stevlee]

just to clarify the question, what should the two records of data look like after running the query on them?

my guess is
12350901,123456086632509,2016
45685470,957125352146255,14

but im only guessing

Thanks for replying so quickly, the data should be put into a new table with three columns containing the data you have seperated by commas.

[stevlee]

Like Delerna said, we need to see what kind of results you want. Also, in the two examples below

(TA:606:Duplicate Entry For ItemId SerialId ModelNo [12350901] [123456086632509] [2016] )
(TA:606:Duplicate Entry For ItemId SerialId ModelNo [45685470] [957125352146255] [14] )

are the hilited fields always the same number of characters in length or do they vary?

Welcome to TheScripts!

Linq ;0)>

The first highlighted field is always 8 characters (VARCHAR2) and the second field will range from 12 to 16 characters (VARCHAR2). This example contains numbers, but they will contain alphanumerics.

[stevlee]

As long as the data to be parsed remains within brackets ([ ]), and a single space exists between the data elements and at the end of the 3rd element, then it becomes a fairly simple matter. Assuming the 2nd Column is in a Field named [Field_Test], which is in a Table named tblTest, then it becomes a Query with three Calculated Fields calling 3 simple Functions:

SELECT tblTest.Field_Test, fParseString1([Field_Test]) AS Field1, fParseString2([Field_Test]) AS Field2, fParseString3([Field_Test]) AS Field3
FROM tblTest
WHERE (((tblTest.Field_Test) Is Not Null));

Public Function fParseString1(strString As String)
Dim varParsedString As Variant
 
varParsedString = Split(strString, "[")
 
'The 2nd Element in the Array will contain the 1st Parsed Field
fParseString1 = Left$(varParsedString(1), Len(varParsedString(1)) - 2)
End Function

Public Function fParseString2(strString2 As String)
Dim varParsedString As Variant
 
varParsedString = Split(strString2, "[")
 
'The 3rd Element in the Array will contain the 2nd Parsed Field
fParseString2 = Left$(varParsedString(2), Len(varParsedString(2)) - 2)
End Function

Public Function fParseString3(strString3 As String)
Dim varParsedString As Variant
 
varParsedString = Split(strString3, "[")
 
'The 4th Element in the Array will contain the 3rd Parsed Field
fParseString3 = Left$(varParsedString(3), Len(varParsedString(3)) - 3)
End Function

OUTPUT (not showing [Field_Test], the entire String):

Field1        Field2            Field3
12350901    123456086632509      2016
45685470    957125352146255      14
000000        1111                222222222222214               
 

NOTE: Last Record was a Test Record, the other 2 used the supplied data.

Great!!!! I am new to Access. Where would I inject this code?

[ADezii]

Great!!!! I am new to Access. Where would I inject this code?

The 'Public' Functions would be place into a Standard Code Module. The SQL can be copied and pasted into the SQL View on a New Query.

[NeoPa]

Define a public function Element() in a standard code module :

Public Function Element(strWhole As String, intElement As Integer) As String
  Element = Split(Split(strWhole, "[")(intElement), "]")(0)
End Function

This would be called using :

SELECT Field_Test,
       Element([Field_Test], 1) AS Field1,
       Element([Field_Test], 2) AS Field2,
       Element([Field_Test], 3) AS Field3
FROM tblTest
WHERE ([Field_Test] IS NOT NULL)

[ADezii]

Define a public function Element() in a standard code module :

Public Function Element(strWhole As String, intElement As Integer) As String
  Element = Split(Split(strWhole, "[")(intElement), "]")(0)
End Function

This would be called using :

SELECT Field_Test,
       Element([Field_Test], 1) AS Field1,
       Element([Field_Test], 2) AS Field2,
       Element([Field_Test], 3) AS Field3
FROM tblTest
WHERE ([Field_Test] IS NOT NULL)

Hello NeoPa, I really must congratulate you on quite an elegant, innovative, and simplistic solution. A single Function Procedure instead of one, a winner every time! What made you think of the "Double Split" idea? Again, job well done!

[NeoPa]

Thanks ADezii :)
I was planning on a Split using "] [" then I saw it wouldn't work very well. Decided "[" was the one to use, then it occurred to me that I could also strip the extraneous bits with another simple Split().

PS. I WAS pleased with myself I must admit ]:)