Parsing Inconsistent Patern Text Strings

rfulky

I am working with text strings that contain banking transactions and each string contains the date, place of purchase, city of purchase, and state of purchase along with other non-important information. I want to store these components of each string in their corresponding fields in a database.

The strings below are listed below exactly how they are coming into my code. It would be easy if ever string had the information in the same order, but this is not the case. I am looking for advice on how to tackle this not actual code on what to do. I thought about having a loop that evaluates each string individually with conditional statements build in. For example the date: use regular expression to pull out “1028” (October 28th) if it is in this format if not look for “10/28” if it is separated by a “/”.. Does anyone have any idea how I would pull out the place of purchase? For example: How could I pull out “Amoco Oil” , “Famous Chinese Restaura” , and Mcdonald’s with the same code and not pull out other text strings such as “Smyrna” or “checkcard”?

TEXT STRING #1: CHECKCARD 1028 AMOCO OIL 05015201 SMYRNA TN 24164076302683301710141
TEXT STRING #2: CHECKCARD 1027 FAMOUS CHINESE RESTAURA SMYRNA TN 24158136302004911880051
TEXT STRING #3: MCDONALD’S F23 10/28 #000031093 PURCHASE MCDONALD’S F23148 NASHVILLE

Thanks for your help,
rfulky

MarkR

Sounds extremely like your data are not in a usable format.

Would it be too much trouble for the producer of them to give them in a consistent and/or usable format?

A space separated list of items which can contain embedded spaces themselves isn't clever, even if you knew which order to expect the columns in. I can see that it's likely that the format is ambiguous- in which case you can't expect a computer to be able to interpret it correctly.

Mark

NogDog

IF you can define the criteria you use to parse them yourself, then theoretically you could write code to do so, but from your above examples it looks like it could get quite complicated. For instance, what if the second example was:

CHECKCARD 1027 FAMOUS CHINESE RESTAURA NORTH SMYRNA TN 24158136302004911880051

How would you know if the location was "FAMOUS CHINESE RESTAURA NORTH" and the city was "SMYRNA", or it was "FAMOUS CHINESE RESTAURA" in "NORTH SMYRNA"?

rfulky

What if I delimited the phrase with spaces and have a database that contained store names. Then I could use each feild and query the database until I found a partial match and used that field to popultate my main database. If you guys see any problems or have a better Idea I am open to anything.

Thanks for your help so far.

rfulky

MarkR

I would never use such a scheme in a production system. Demand better quality data instead.

There is too much chance that such code could make an error. What happens if a store name appears that you don't recognise? Are you going to end up putting junk in your database?

This sort of guesswork is not suitable for use in a robust production system.

Mark

Weedpacket

I'd have to agree with MarkR; looking at those samples I suspect I'd have a hard time parsing them (which MCDONALD'S do you want? The first one or the second one? And is the F23 relevant? What about the F23148?) - and I'm more intelligent than a machine (No, really! Stop laughing!). Trying to explain to a machine how to do it if I can't figure out how to do it would be futile.

IF, as NogDog says, you can establish definitive and consistent criteria that all strings satisfy and allow you to extract the relevant data from all of the strings that match those criteria (without going crazy when some corrupted data comes along), then you'd be in a position to make sense of the things. Throwing a bunch of heuristics (like looking for "/" and hoping that it's part of a date) at the problem is way too brittle for real life.

Coming up with a distinct set of criteria for each possible format is also brittle; for one thing, you need to ensure that any given record satisfies no more than one such set (none, if it's faulty). For another, this looks like the sort of situation where anyone sending you stuff is sending it to you in any old format they want: you'll be constantly patching and repatching the code to deal with every piffling small change - it'll turn into a big ball of mud, and then it will turn into a train wreck.

They want an automated system here, surely they can meet you halfway with consistent formatting. A well-crafted XML format would allow the data to be flexible enough to cope with what are obviously wildly varying conditions at the various data sources, while still being rigid enough to be machine-readable (the fact that the job of parsing an XML file is already a solved problem is just a bonus).