Solved Splitting Up Text That Uses Varying Formats

[EzGoingKev]

I have some data in a field. It is basically Make and Model data. Some fields have vehicle type added in:

AUDI-RS5 / RS5 SPORTBACK
VOLVO - TRUCKS - MEDIUM / HEAVY DUTY-VNM SERIES
WHITE - TRACTORS-2-110
CASE - EXCAVATORS-688B CK

I would like them to be separated into two different Make | Model fields:

AUDI-RS5 / RS5 SPORTBACK -> Audi | RS5 / RS5 SPORTBACK
VOLVO - TRUCKS - MEDIUM / HEAVY DUTY-VNM SERIES -> VOLVO - TRUCKS - MEDIUM / HEAVY DUTY | VNM SERIES
WHITE - TRACTORS-2-110 -> WHITE - TRACTORS | 2-110
CASE - EXCAVATORS-688B CK -> CASE - EXCAVATORS | 688B CK

Some of the model names have a dash in them so that kind of screws things up for me.

Any ideas on how to handle this?

 

[plog]

First, make a backup table. Then add a Model and Make field to your table. Then you just need to find patterns and process records that fit those patterns. Given the small sample size I would do this:

1. Handle all the cases that have a "|" in them. Make a query to identify those (Like "*|*"), then make a field to split out the Model and a field to split out the make (using Mid and InStr functions). Run it and verify it works. Then convert that query to an UPDATE--take the Model and Make logic and use it to update the Make and Model fields.

2. Make a query to find the records with Null Make fields. These are the ones you need to still process. I would look for a way to identify a group--perhaps a different identifing character--and process it as in step #1.

3. Keep doing this until you can't see any more patterns and finish the rest manually.

You gave this as an example:

WHITE - TRACTORS-2-110

I see no way to have a query be able to determine the make/model of that so it would have to be processed manually.

 

[EzGoingKev]

I am all set.

I did not figure out how to split them up but I did figure out how to link the data to our Make and Model tables and get the data that way.

 

[theDBguy]

Hi. I think this is possible only if you can determine a pretty consistent pattern to split the data.

 

[EzGoingKev]

Hi. I think this is possible only if you can determine a pretty consistent pattern to split the data.

It is not consistent and as we add more data the patterns could keep changing so I figured out another way to link to our Make and Model tables.

 

[plog]

It is not consistent and as we add more data the patterns could keep changing..

Wait--your allowing bad data to continue to enter the system? The best solutions to problems are ones that avoid the problems. How exactly is this data coming in?

 

[EzGoingKev]

Wait--your allowing bad data to continue to enter the system? The best solutions to problems are ones that avoid the problems. How exactly is this data coming in?

It is not bad data.

These are vehicle makes and models. It is also includes offroad/aggricultural/industrial stuff such as generators, combines, backhoes, whatever. It is a pretty wide array of stuff so there are going to be variances in the model names. We also use our own custom names at times.

The AUDI-RS5 / RS5 SPORTBACK is a perfect example of that. Audi is the manufacturer. Audi produces two models, an RS5 and an RS5 Sportback. We decided to combine them in to what can be referred to as a vehicle block. This is done for our E-CAT system, print catalog, etc. It can make it easier to map parts to those applications.

The automotive aftermarket uses what is called the ACES standard for application data exchange. In that standard the RS5 and the RS5 are two separate models. When I setup the RS5 / RS5 Sportback I map that model name to the two individual models in the standard so the parts go out under two separate models.

 

[plog]

Semantics. Let's use the term "poorly input" data then. You didn't describe the method of how this data is poorly input, you just described your data.

To truly solve this issue and eliminate this periodic manual procedure you need to swim upstream and figure out a way to get the data into your system in the manner you need it. Are people using a form? Are you receiving an Excel spreadsheet? How is this string of text which encodes the make and model getting into your database?

 

[arnelgp]

use IBM() function, it never fails, regardless of how varying/complex the split maybe.