Parsing and Rearranging

[Zachry1]

I want to modify my Description field contents by separating parts of the
description and adding information from other fields. For Ex:
Desc (Currently) = STUD BOLT 2 NUTS, ASME B18.2.1 / ASME B18.2.2, A193 GR.
B7 / A194 GR. 2H, FULL LENGTH THREADED
Sixe 1 = .625
Size 2 = Null (in this case, but other records may have a 2nd Size field)
Wt1 = 3.25
Wt2 = Null (in this case, but other records may have a 2nd Wt field)

I want to be able to:
1. parse after the 1st coma (,),
2. insert any size and wt data from the other fields, separate then by an (x)
3. account for fields that may be null now, but have content in subsequent
records.
4. then reinsert any remaining Desc content after the insertion.

I should end up with:
STUD BOLT 2 NUTS, .625 x 3.25, ASME B18.2.1 / ASME B18.2.2, A193 GR. B7 /
A194 GR. 2H, FULL LENGTH THREADED

(did not cross-post)
Thanks in advance

 

[Allen Browne]

Zacahry, you cannot design a relational database like that.

One of the core concepts in data normalization is that you put only one
thing in any field. This field has heaps of different things, i.e. several
entities separated by commas, and each one of those contains multiple
values. There is no way this thing will fly.

Do some reading about how to break things down into related tables, each
with atomic fields. Jeff Conrad has a stack of links to get you started:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

If you are actually trying to import bad data and parse it into tables you
have correctly normalized, see help on Split().

 

[Zachry1]

Allen,
thanks for the reply.
unfortunately i am the recipient of this data and have to deal with it, it
comes to us that way from engineers.
i use this as an BOM import into Oracle, and have to make useful for users
to use the data.
i have several folks manually manipulate the content so that it has all of
the data elements for importing.
my goal is to reduce the level of user interaction, and have the program
prepare a load file for an Oracle open interfac table.
hope this clarifies the situation better.

 

[Allen Browne]

Okay, so it is unnormalized data you are trying to import.

See help on Split(). It can at least separate the data at the comma for you.
You will want to do this programmatically. OpenRecordset() on the source
data, and loop though the records until EOF. For each one use Split() to
create the array. You can then loop through the array, concatenating values
together into the output string the way you want, and replacing the original
text before moving on to the next record of the recordset.

 

[Zachry1]

Allen,
thanks for the help.
if you have some sample code that i may copy i would appreciate it.