I have two separate data sources that don't talk to each other and I need to do a comparison of the information. Each system has a primary key but only one is shared, sort of. We have System S and system H. I can do an excel data extract from both. The extract from system H does not have any primary keys from system S. The data extract from system S does have the primary key for system H, but it is within a comment section. And there could be multiple primary keys from system H in the comments of system S. The one good thing, it is always in the exact same format. Bad part, there could also be general comments before or after in the comment. for example. "Estimated delivery is 01/01/2020 12345(5), 23456(45) 34567(6). Follow Up with ACME Corp in 10 days" The 5 digit number is the primary key for system H and the number in the () is the quantity for that order. If I can extract that Primary Key for system H, then I could match that record from system to the records in system H. If I can extract the number and QTY together, I know how to separate into two fields. I just don't know how to go about extracting them from the string. Note, there could be one or many numbers+qty to extract.
Since it is a 1 to many match, I'm thinking I copy the comments field with the system S ID# to a new table, do the separating then put it back together later.
So, in field called Comments we have: "Estimated delivery is 01/01/2020 12345(5), 23456(45) 34567(6). Follow Up with ACME Corp in 10 days"
I need to extract:
ID = 12345 Qty =5
ID = 23456 Qty=45
ID = 34567 Qty=6
I'm not even sure what to google to figure this out. Any help would be appreciated.
thanks!
Johnny