Condition breakdown

[Mac1206]

I have this COND column in Excel which I need to import into Access but as you see, each row has this long string that needs to be converted into rows: Sold to party, Price Group and Shipping Condition would need to be represented 5 times for this particular Price Group....I'm at a lost trying to convert this....Thanks in Advance

Sold to party = 100092131 And Price Group = 27 And Shipping condition = RR And Price Group Includes 19,22,25,27,29

<tbody>
</tbody>

 

[xenou]

This is very weird. Can you explain more? In database design generally you do not import conditions as columns in tables - you use conditions to select from columns in tables!

 

[Mac1206]

This is very weird. Can you explain more? In database design generally you do not import conditions as columns in tables - you use conditions to select from columns in tables!

You are correct, I'm trying to import an excel file that has a Condition column which need to be converted into fields and rows...My row J2 is this one line : Sold to party = 100092131 And Price Group = 27 And Shipping condition = RR And Price Group Includes 19,22,25,27,29

In order for me to import into access, it need to be converted somehow into this format as column and rows below:

SOLD TO PARTY	PRICE GROUP	SHIPPING CONDITION	PRICE GROUP INCLUDES
100092131	27	RR	19
100092131	27	RR	22
100092131	27	RR	25
100092131	27	RR	27
100092131	27	RR	29

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

May this is a VBA or Excel formulas functionality that could convert this into the right format for importing instead of a manual process for several thousand records...

 

[xenou]

How did you end up with such a strange data set? Are you trying to reverse engineer something?

 

[Mac1206]

How did you end up with such a strange data set? Are you trying to reverse engineer something?

It came as an Excel file, which is weird indeed as such (Sample Data):

RULE_ID	START_DATE	END_DATE	NAME	COMPONENT	COMPONENTVAL	CURRENCY	UOM	COND
133	8/27/2008		CW Carry Rail Direct Discount W,N	DISCOUNT	.5	CAD	CWT	Sold to party = 100092131 And Price Group = 27 And Shipping condition = RR And Price Group Includes 19,22,25,27,29

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

The desired result is:

RULE_ID	START_DATE	END_DATE	NAME	COMPONENT	COMPONENTVAL	CURRENCY	UOM	SOLD TO PARTY	PRICE GROUP	SHIPPING CONDITION	PRICE GROUP INCLUDES
133	8/27/2008		CW Carry Rail Direct Discount W,N	DISCOUNT	.5	CAD	CWT	100092131	27	RR	19
133	8/27/2008		CW Carry Rail Direct Discount W,N	DISCOUNT	.5	CAD	CWT	100092131	27	RR	22
133	8/27/2008		CW Carry Rail Direct Discount W,N	DISCOUNT	.5	CAD	CWT	100092131	27	RR	25
133	8/27/2008		CW Carry Rail Direct Discount W,N	DISCOUNT	.5	CAD	CWT	100092131	27	RR	27
133	8/27/2008		CW Carry Rail Direct Discount W,N	DISCOUNT	.5	CAD	CWT	100092131	27	RR	29

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Is there anyway to convert that last column so it would pull out the fields and what it equals?

 

[xenou]

Okay but why would you assume this:

100092131 	27 	RR 	19
100092131 	27 	RR 	22
100092131 	27 	RR 	25
100092131 	27 	RR 	27
100092131 	27 	RR 	29

instead of this:

100092131 	27 	RR 	27

which is to say, just because 27 is included in 19,22,25,27 and 29 doesn't mean 27 is 19,22,25,27 and 29 ... it could be just itself (27).

what you are asking for requires some fairly heavy duty data massaging. There is no button to press for that. It remains quite mysterious how you are interpreting this COND column and why the data is coming to you that way.

 

[Mac1206]

Okay but why would you assume this:

100092131     27     RR     19
100092131     27     RR     22
100092131     27     RR     25
100092131     27     RR     27
100092131     27     RR     29

instead of this:

100092131     27     RR     27

which is to say, just because 27 is included in 19,22,25,27 and 29 doesn't mean 27 is 19,22,25,27 and 29 ... it could be just itself (27).

what you are asking for requires some fairly heavy duty data massaging. There is no button to press for that. It remains quite mysterious how you are interpreting this COND column and why the data is coming to you that way.

I understand but that's my directive to see if it can be done with this excel file and you are correct with 100092131 27 RR 27...

 

[xenou]

okay. Do you have any examples of more COND values or do they all follow exactly this kind of structure? For instance, they can probably be split out on the AND values and the = symbols, but that means they must all have the same conditions applied. If they don't, it's even uglier (such as rows that don't have the includes condition or rows that don't have the shipping condition condition).

Approx. how much data is there (not a big deal but 1,000,000 rows would be a lot different than 1,000 rows in terms of building a solution).

 

[Mac1206]

okay. Do you have any examples of more COND values or do they all follow exactly this kind of structure? For instance, they can probably be split out on the AND values and the = symbols, but that means they must all have the same conditions applied. If they don't, it's even uglier (such as rows that don't have the includes condition or rows that don't have the shipping condition condition).

Approx. how much data is there (not a big deal but 1,000,000 rows would be a lot different than 1,000 rows in terms of building a solution).

About 125,000 and the condition is the same...Thanks in advance

 

[Mac1206]

About 125,000 and the condition is the same...Thanks in advance

Good morning Xenou, I had to verify that the code layout above and for each price within the group, it is assigned as follows:

Desired Result Code:
100092131 27 RR 19
100092131 27 RR 22
100092131 27 RR 25
100092131 27 RR 27
100092131 27 RR 29

I hope this doesn't make it more difficult but I know this type of programming is difficult anyway....I'm learning that...