Parsing data with unknown or variable length

[Steve_A]

I am going to be receiving data twice a week in a Excel file and one of the
fields has this type of data in it delimited by _ (uncerscores)

Column Name:Release Action ID
R_CN-AA3317.0048_WBS-4.050.3_CNAA-3317|R_CN-AA3317.0257_WBS-4.050.3

I need to put each piece of data between the _ in a seperate field. I have
done this by making a table of the ID and this colum and exporting it and
then reimporting it as _ delimited the joining it back up by ID number but
would like something a bit faster "LOL"

In one record set there may not be any data in the column but the next one
may have 19 or more so I do not know how many columns it will take and can
very from one week to the next (it will always be more, not less)

Thanks for looking

 

[John W. Vinson]

I am going to be receiving data twice a week in a Excel file and one of the
fields has this type of data in it delimited by _ (uncerscores)

Column Name:Release Action ID
R_CN-AA3317.0048_WBS-4.050.3_CNAA-3317|R_CN-AA3317.0257_WBS-4.050.3

I need to put each piece of data between the _ in a seperate field. I have
done this by making a table of the ID and this colum and exporting it and
then reimporting it as _ delimited the joining it back up by ID number but
would like something a bit faster "LOL"

In one record set there may not be any data in the column but the next one
may have 19 or more so I do not know how many columns it will take and can
very from one week to the next (it will always be more, not less)

Thanks for looking

Use the Split() function - it would be simplest to write some VBA code to
parse the field, but you could do it simply in a query, updating the first
field to (say)

=Split([Release Action ID], "_")(2)

to get the third "field" (since it's zero based).

 

[Steve_A]

I am going to be receiving data twice a week in a Excel file and one of the
fields has this type of data in it delimited by _ (uncerscores)

Column Name:Release Action ID
R_CN-AA3317.0048_WBS-4.050.3_CNAA-3317|R_CN-AA3317.0257_WBS-4.050.3

I need to put each piece of data between the _ in a seperate field. I have
done this by making a table of the ID and this colum and exporting it and
then reimporting it as _ delimited the joining it back up by ID number but
would like something a bit faster "LOL"

In one record set there may not be any data in the column but the next one
may have 19 or more so I do not know how many columns it will take and can
very from one week to the next (it will always be more, not less)

Thanks for looking

Use the Split() function - it would be simplest to write some VBA code to
parse the field, but you could do it simply in a query, updating the first
field to (say)

=Split([Release Action ID], "_")(2)

to get the third "field" (since it's zero based).

Thanks John, I gave it a quick try and it did not like the parnes or
something, I will work on it in the morning
Thanks

 

[John W. Vinson]

I am going to be receiving data twice a week in a Excel file and one of the
fields has this type of data in it delimited by _ (uncerscores)

Column Name:Release Action ID
R_CN-AA3317.0048_WBS-4.050.3_CNAA-3317|R_CN-AA3317.0257_WBS-4.050.3

I need to put each piece of data between the _ in a seperate field. I have
done this by making a table of the ID and this colum and exporting it and
then reimporting it as _ delimited the joining it back up by ID number but
would like something a bit faster "LOL"

In one record set there may not be any data in the column but the next one
may have 19 or more so I do not know how many columns it will take and can
very from one week to the next (it will always be more, not less)

Thanks for looking

Use the Split() function - it would be simplest to write some VBA code to
parse the field, but you could do it simply in a query, updating the first
field to (say)

=Split([Release Action ID], "_")(2)

to get the third "field" (since it's zero based).

Thanks John, I gave it a quick try and it did not like the parnes or
something, I will work on it in the morning
Thanks

On second think, it in fact will not work in a query. A quick and dirty
approach would be to create a custom function:

Public Function GetPiece(strIn As String, strDelim as String, _
iPos As Integer) As String
Dim strParse() As String
strParse = Split(strIn, strDelim)
If iPos < 0 Or iPos > UBound(strParse) Then
MsgBox "Element " & iPos & " not in string!"
Else
GetPiece = strParse(iPos)
End If
End Sub

Call it like:

ThirdPiece: GetPiece([Release Action ID], "_", 2)

should return "WBS-4.050.3" given the value above.

 

[Steve_A]

On Tue, 3 Jun 2008 18:20:01 -0700, Steve_A <allen.stATverizon.net.huh> wrote:

I am going to be receiving data twice a week in a Excel file and one of the
fields has this type of data in it delimited by _ (uncerscores)

Column Name:Release Action ID
R_CN-AA3317.0048_WBS-4.050.3_CNAA-3317|R_CN-AA3317.0257_WBS-4.050.3

I need to put each piece of data between the _ in a seperate field. I have
done this by making a table of the ID and this colum and exporting it and
then reimporting it as _ delimited the joining it back up by ID number but
would like something a bit faster "LOL"

In one record set there may not be any data in the column but the next one
may have 19 or more so I do not know how many columns it will take and can
very from one week to the next (it will always be more, not less)

Thanks for looking

Use the Split() function - it would be simplest to write some VBA code to
parse the field, but you could do it simply in a query, updating the first
field to (say)

=Split([Release Action ID], "_")(2)

to get the third "field" (since it's zero based).

Thanks John, I gave it a quick try and it did not like the parnes or
something, I will work on it in the morning
Thanks

On second think, it in fact will not work in a query. A quick and dirty
approach would be to create a custom function:

Public Function GetPiece(strIn As String, strDelim as String, _
iPos As Integer) As String
Dim strParse() As String
strParse = Split(strIn, strDelim)
If iPos < 0 Or iPos > UBound(strParse) Then
MsgBox "Element " & iPos & " not in string!"
Else
GetPiece = strParse(iPos)
End If
End Sub

Call it like:

ThirdPiece: GetPiece([Release Action ID], "_", 2)

should return "WBS-4.050.3" given the value above.

Thanks John, I will give that approach a try