extract items from list in single field, revisited

[mary r]

Hi,
Would this also apply to my situation:

I need to parse a long text field (imported from a very old database format)
which will contain 0 or more 13-character blocks of text. These blocks
represent status update entries; each begins with "!!" but does not have any
delimiters at the end of the block.

I have no experience with the Split() function in Access VBA, and the last
time I worked with arrays was a good 20 years ago.

I had written an event procedure parsing the blocks in a single record and
appending them to a second table using a foreign key to relate the two
tables. I used the Mid() function in my loop to process the string field
character by character.

Now I am faced with the task of processing the entire table at once. So
far, it looks as though the way to go is to use VBA and recordset operations
(with which I have very little experience, being more SQL oriented), to
unpack the status blocks into a temporary table containing the ID and the
string, just to keep this step simple. After creating this temp table, I
would call an append query using Mid() to unpack the status block into the
appropriate fields of the status table.

Thanks in advance!

Here's one way. This function will split a string on a delimiter and can
be used it in an append query

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)

End Function

Using this in the query
SafeSplit([ItemList], ",", 0)
will return the first item in the list. Then change the 0 to a 1 and run
the query again to append records for the second item in each list, and
so on (as many times as there are items in the longest list).

I have a table with fields IndividualID, ItemList. ItemList is a single text
field that has items separated by a comma (e.g., "apple, orange, lemon,
lime").

I need to create a table that lists each item singly on a record.

ID = 101
ItemList = Apple, Orange, Lemon, Lime

translates to

101 Apple
101 Orange
101 Lemon
101 Lime

and so forth through the remaining records.

Can anyone point me to sample code that I can modify or play around with?
I'm not new to programming concepts and have a good idea of the necessary
algorithm, but lack the experience to set it out in VBA.

Any help is truly appreciated.

Ross

 

[Dennis]

JMHO here, but it would seem to be much easier to simply use ADO methodology
to do the entire thing. You have complete control over each step of the
process, and unlike a SQL query, you can single-step through the code to make
sure each element is being parsed correctly.

Hi,
Would this also apply to my situation:

I need to parse a long text field (imported from a very old database format)
which will contain 0 or more 13-character blocks of text. These blocks
represent status update entries; each begins with "!!" but does not have any
delimiters at the end of the block.

I have no experience with the Split() function in Access VBA, and the last
time I worked with arrays was a good 20 years ago.

I had written an event procedure parsing the blocks in a single record and
appending them to a second table using a foreign key to relate the two
tables. I used the Mid() function in my loop to process the string field
character by character.

Now I am faced with the task of processing the entire table at once. So
far, it looks as though the way to go is to use VBA and recordset operations
(with which I have very little experience, being more SQL oriented), to
unpack the status blocks into a temporary table containing the ID and the
string, just to keep this step simple. After creating this temp table, I
would call an append query using Mid() to unpack the status block into the
appropriate fields of the status table.

Thanks in advance!

Here's one way. This function will split a string on a delimiter and can
be used it in an append query

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)

End Function

Using this in the query
SafeSplit([ItemList], ",", 0)
will return the first item in the list. Then change the 0 to a 1 and run
the query again to append records for the second item in each list, and
so on (as many times as there are items in the longest list).

I have a table with fields IndividualID, ItemList. ItemList is a single text
field that has items separated by a comma (e.g., "apple, orange, lemon,
lime").

I need to create a table that lists each item singly on a record.

ID = 101
ItemList = Apple, Orange, Lemon, Lime

translates to

101 Apple
101 Orange
101 Lemon
101 Lime

and so forth through the remaining records.

Can anyone point me to sample code that I can modify or play around with?
I'm not new to programming concepts and have a good idea of the necessary
algorithm, but lack the experience to set it out in VBA.

Any help is truly appreciated.

Ross

 

[Klatuu]

The Split function would be exactly the thing to use in this case. It is
not that difficult and the fact that there is no delimiter on the end makes
it even easier. If it did, the resulting array would have a null element at
the end.

As to working with arrays in Access. It really isn't hard. You do have to
be aware of the Option Base statement. Option Base determines whether array
elements start with 0 or 1. The default is 0, so it will always be 0 unless
you have Option Base 1 specificed at the top of your module. So, the array
index will be 0 to the number of elements -1. It is just like many of the
property collections in Access.

There is also the Ubound function that will tell you what the highest
element index is. If you are using Option Base 0 and there are 6 elements,
the Ubound function will return 5.

So if you need to create a record for each element, here is a general
outline to get you started:

Dim varMyArray As Variant
Dim lngX as Long
Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SomeTable")
varMyArray = Split(BlockOfStrings, "!!")

With rst
For lngX = 0 to UBound(varMyArray)
.AddNew
!BlockField = varMyArray(lngx)
.Update
Next lngX
.Close
End With
Set rst = Nothing

Like I say, this is just an outline, I am sure there will be more to do, but
hopefully, this will get you started.

Good Luck!

Hi,
Would this also apply to my situation:

I need to parse a long text field (imported from a very old database
format)
which will contain 0 or more 13-character blocks of text. These blocks
represent status update entries; each begins with "!!" but does not have
any
delimiters at the end of the block.

I have no experience with the Split() function in Access VBA, and the last
time I worked with arrays was a good 20 years ago.

I had written an event procedure parsing the blocks in a single record and
appending them to a second table using a foreign key to relate the two
tables. I used the Mid() function in my loop to process the string field
character by character.

Now I am faced with the task of processing the entire table at once. So
far, it looks as though the way to go is to use VBA and recordset
operations
(with which I have very little experience, being more SQL oriented), to
unpack the status blocks into a temporary table containing the ID and the
string, just to keep this step simple. After creating this temp table, I
would call an append query using Mid() to unpack the status block into the
appropriate fields of the status table.

Thanks in advance!

Here's one way. This function will split a string on a delimiter and can
be used it in an append query

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)

End Function

Using this in the query
SafeSplit([ItemList], ",", 0)
will return the first item in the list. Then change the 0 to a 1 and run
the query again to append records for the second item in each list, and
so on (as many times as there are items in the longest list).

I have a table with fields IndividualID, ItemList. ItemList is a single
text
field that has items separated by a comma (e.g., "apple, orange, lemon,
lime").

I need to create a table that lists each item singly on a record.

ID = 101
ItemList = Apple, Orange, Lemon, Lime

translates to

101 Apple
101 Orange
101 Lemon
101 Lime

and so forth through the remaining records.

Can anyone point me to sample code that I can modify or play around
with?
I'm not new to programming concepts and have a good idea of the
necessary
algorithm, but lack the experience to set it out in VBA.

Any help is truly appreciated.

Ross

 

[mary r]

Dennis, thanks for your reply. I have very little experience and no training
in ADO, being a dinosaur who still remembers flow charts and linked lists

JMHO here, but it would seem to be much easier to simply use ADO methodology
to do the entire thing. You have complete control over each step of the
process, and unlike a SQL query, you can single-step through the code to make
sure each element is being parsed correctly.

Hi,
Would this also apply to my situation:

I need to parse a long text field (imported from a very old database format)
which will contain 0 or more 13-character blocks of text. These blocks
represent status update entries; each begins with "!!" but does not have any
delimiters at the end of the block.

I have no experience with the Split() function in Access VBA, and the last
time I worked with arrays was a good 20 years ago.

I had written an event procedure parsing the blocks in a single record and
appending them to a second table using a foreign key to relate the two
tables. I used the Mid() function in my loop to process the string field
character by character.

Now I am faced with the task of processing the entire table at once. So
far, it looks as though the way to go is to use VBA and recordset operations
(with which I have very little experience, being more SQL oriented), to
unpack the status blocks into a temporary table containing the ID and the
string, just to keep this step simple. After creating this temp table, I
would call an append query using Mid() to unpack the status block into the
appropriate fields of the status table.

Thanks in advance!

Here's one way. This function will split a string on a delimiter and can
be used it in an append query

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)

End Function

Using this in the query
SafeSplit([ItemList], ",", 0)
will return the first item in the list. Then change the 0 to a 1 and run
the query again to append records for the second item in each list, and
so on (as many times as there are items in the longest list).


On Thu, 19 Jan 2006 13:17:02 -0800, Pendragon

I have a table with fields IndividualID, ItemList. ItemList is a single text
field that has items separated by a comma (e.g., "apple, orange, lemon,
lime").

I need to create a table that lists each item singly on a record.

ID = 101
ItemList = Apple, Orange, Lemon, Lime

translates to

101 Apple
101 Orange
101 Lemon
101 Lime

and so forth through the remaining records.

Can anyone point me to sample code that I can modify or play around with?
I'm not new to programming concepts and have a good idea of the necessary
algorithm, but lack the experience to set it out in VBA.

Any help is truly appreciated.

Ross

 

[mary r]

Thanks very much, Klatuu. I think I can move forward with this now. Have a
great day!

The Split function would be exactly the thing to use in this case. It is
not that difficult and the fact that there is no delimiter on the end makes
it even easier. If it did, the resulting array would have a null element at
the end.

As to working with arrays in Access. It really isn't hard. You do have to
be aware of the Option Base statement. Option Base determines whether array
elements start with 0 or 1. The default is 0, so it will always be 0 unless
you have Option Base 1 specificed at the top of your module. So, the array
index will be 0 to the number of elements -1. It is just like many of the
property collections in Access.

There is also the Ubound function that will tell you what the highest
element index is. If you are using Option Base 0 and there are 6 elements,
the Ubound function will return 5.

So if you need to create a record for each element, here is a general
outline to get you started:

Dim varMyArray As Variant
Dim lngX as Long
Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SomeTable")
varMyArray = Split(BlockOfStrings, "!!")

With rst
For lngX = 0 to UBound(varMyArray)
.AddNew
!BlockField = varMyArray(lngx)
.Update
Next lngX
.Close
End With
Set rst = Nothing

Like I say, this is just an outline, I am sure there will be more to do, but
hopefully, this will get you started.

Good Luck!

Hi,
Would this also apply to my situation:

I need to parse a long text field (imported from a very old database
format)
which will contain 0 or more 13-character blocks of text. These blocks
represent status update entries; each begins with "!!" but does not have
any
delimiters at the end of the block.

I have no experience with the Split() function in Access VBA, and the last
time I worked with arrays was a good 20 years ago.

I had written an event procedure parsing the blocks in a single record and
appending them to a second table using a foreign key to relate the two
tables. I used the Mid() function in my loop to process the string field
character by character.

Now I am faced with the task of processing the entire table at once. So
far, it looks as though the way to go is to use VBA and recordset
operations
(with which I have very little experience, being more SQL oriented), to
unpack the status blocks into a temporary table containing the ID and the
string, just to keep this step simple. After creating this temp table, I
would call an append query using Mid() to unpack the status block into the
appropriate fields of the status table.

Thanks in advance!

Here's one way. This function will split a string on a delimiter and can
be used it in an append query

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)

End Function

Using this in the query
SafeSplit([ItemList], ",", 0)
will return the first item in the list. Then change the 0 to a 1 and run
the query again to append records for the second item in each list, and
so on (as many times as there are items in the longest list).


On Thu, 19 Jan 2006 13:17:02 -0800, Pendragon

I have a table with fields IndividualID, ItemList. ItemList is a single
text
field that has items separated by a comma (e.g., "apple, orange, lemon,
lime").

I need to create a table that lists each item singly on a record.

ID = 101
ItemList = Apple, Orange, Lemon, Lime

translates to

101 Apple
101 Orange
101 Lemon
101 Lime

and so forth through the remaining records.

Can anyone point me to sample code that I can modify or play around
with?
I'm not new to programming concepts and have a good idea of the
necessary
algorithm, but lack the experience to set it out in VBA.

Any help is truly appreciated.

Ross