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:Duplica te Entry For ItemId SerialId ModelNo [12350901] [123456086632509] [2016] )
(TA:606:Duplica te 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,123456 086632509,2016
45685470,957125 352146255,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:Duplica te Entry For ItemId SerialId ModelNo [12350901] [123456086632509] [2016] )
(TA:606:Duplica te 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:Duplica te Entry For ItemId SerialId ModelNo [12350901] [123456086632509] [2016] )
(TA:606:Duplica te 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:
[CODE=sql]
SELECT tblTest.Field_T est, fParseString1([Field_Test]) AS Field1, fParseString2([Field_Test]) AS Field2, fParseString3([Field_Test]) AS Field3
FROM tblTest
WHERE (((tblTest.Fiel d_Test) Is Not Null));[/CODE]
[CODE=vb]Public Function fParseString1(s trString As String)
Dim varParsedString As Variant

varParsedString = Split(strString , "[")

'The 2nd Element in the Array will contain the 1st Parsed Field
fParseString1 = Left$(varParsed String(1), Len(varParsedSt ring(1)) - 2)
End Function[/CODE]
[CODE=vb]Public Function fParseString2(s trString2 As String)
Dim varParsedString As Variant

varParsedString = Split(strString 2, "[")

'The 3rd Element in the Array will contain the 2nd Parsed Field
fParseString2 = Left$(varParsed String(2), Len(varParsedSt ring(2)) - 2)
End Function[/CODE]
[CODE=vb]Public Function fParseString3(s trString3 As String)
Dim varParsedString As Variant

varParsedString = Split(strString 3, "[")

'The 4th Element in the Array will contain the 3rd Parsed Field
fParseString3 = Left$(varParsed String(3), Len(varParsedSt ring(3)) - 3)
End Function[/CODE]
OUTPUT (not showing [Field_Test], the entire String):
[CODE=text]
Field1 Field2 Field3
12350901 123456086632509 2016
45685470 957125352146255 14
000000 1111 222222222222214
[/CODE]
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,123456 086632509,2016
45685470,957125 352146255,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:Duplica te Entry For ItemId SerialId ModelNo [12350901] [123456086632509] [2016] )
(TA:606:Duplica te 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:
[CODE=sql]
SELECT tblTest.Field_T est, fParseString1([Field_Test]) AS Field1, fParseString2([Field_Test]) AS Field2, fParseString3([Field_Test]) AS Field3
FROM tblTest
WHERE (((tblTest.Fiel d_Test) Is Not Null));[/CODE]
[CODE=vb]Public Function fParseString1(s trString As String)
Dim varParsedString As Variant

varParsedString = Split(strString , "[")

'The 2nd Element in the Array will contain the 1st Parsed Field
fParseString1 = Left$(varParsed String(1), Len(varParsedSt ring(1)) - 2)
End Function[/CODE]
[CODE=vb]Public Function fParseString2(s trString2 As String)
Dim varParsedString As Variant

varParsedString = Split(strString 2, "[")

'The 3rd Element in the Array will contain the 2nd Parsed Field
fParseString2 = Left$(varParsed String(2), Len(varParsedSt ring(2)) - 2)
End Function[/CODE]
[CODE=vb]Public Function fParseString3(s trString3 As String)
Dim varParsedString As Variant

varParsedString = Split(strString 3, "[")

'The 4th Element in the Array will contain the 3rd Parsed Field
fParseString3 = Left$(varParsed String(3), Len(varParsedSt ring(3)) - 3)
End Function[/CODE]
OUTPUT (not showing [Field_Test], the entire String):
[CODE=text]
Field1 Field2 Field3
12350901 123456086632509 2016
45685470 957125352146255 14
000000 1111 222222222222214
[/CODE]
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 ]:)