Middle Drivers License

[Richard Corso]

Hello All

I need a little help with “Middle”. I am trying to use a magnetic card swipe to retrieve First Name, Last Name, DOB and Address from a drivers license. I have been able to write the Middle scripts and all but the “Name_Last” and “City1” work just fine. There is no brake between them and with out knowing the length of the last name or city I have not been able to find a way to separate them. I have attached a sample file with the scripting and the info the card swipe gets for the strip. Any help would be greatly appreciated.

%[color:red]FLPORT ST. LUCIJOHNSON$FRANK$GERALD^186 NW PEACOCK BLVD. APT. 501^ ?

;6666666666666666666=1107195899290=?

%! 349863486 E B 1602 ECCECC00000?

DL_Swipe.zip

Edited August 7, 2010 by Guest

[LaRetta]

Hi Richard,

There is no way a computer can split Port St. Luci from Johnson unless you have a table of US cities (usually contained within a zip code table). Three potential ideas:

1) Use the zip to find a match. Can you guarantee that the first 5 characters in your zip are correct? You would need to purchase a zip code database and they aren't cheap. There is more than one 5-digit zip for this Florida city.

If you did get the zip codes AND if you can guarantee the first five characters of your zip then you would have the city automatically. And you could use the city to substitute out the city (by pattern) and replace it from between the first % and first $ (using theory in calculations I've provided in revised file attached). However, a misspelled city would cause issues (which you have misspelled city).

2) You could attempt to find the city by pattern alone. However, it would be less dependable since, even in your example, the city is either spelled incorrectly or is truncated at 13 characters. It is spelled Port St. Luci[color:blue]e so you couldn't get a pattern match. You would then have to use the state and partial city (such as first 8 characters and maybe first 4 of zip to attempt to find exact city.

3) See about having the source data come in cleaner format.

I have modified your file (see attached). I didn't handle it via script but rather calculation so you can see my approach more clearly. You can incorporate the concept into script after you are clear on how you might proceed.

Notice that I created a zips table. I also misspelled Luci so we could get a match to the zip table. The yellow fields are calculations of 3-character zip and 7 character city which I used as the parent side in the match to zip table (where I also truncated the zip to 3 characters and the city to 7.

The blue fields show first the removal attempt of the city from between the % & $ (including remove 2-character state). And c_lastName_left then grabs anything left between % & $. This, of course, can be turned into one calculation.

It will not provide 100% perfection since spelling may be an issue. But if there is a relationship to zips then you at least know you have a match, even though the calculation may not properly substitute the city (because of spelling error); on those, you could manually correct the last name. By using calculation in conjunction with relationship, I believe you could be 90% there. One record really isn't enough of a sample to get a clear picture of the data patterns but I hope it moves you forward.

DL_SwipeREV.zip

[comment]

It would be helpful to have more examples, if not the actual rules. However, considering that:

(a) no one in his right mind (even at the DMV) would design a system that does not allow the separation of city and last name, and

( the actual name of the city is "Port St. Luci[color:red]e",

and a quick Google search leads to:

City =

Middle ( text & "^" ; 4 ; Min ( Position ( text ; "^" ; 4 ; 1 ) - 4 ; 13 ) )

Extracting the last name is left as an exercise for the reader.

[LaRetta]

Oh. You imply that recognizing the misspelling of Port St. Lucie (your item 2) triggered a thought to Google Search for something. Is that correct?

So the city was truncated (I mentioned that possibility in #2 above) but then went about my merry way trying to resolve it instead of waiting to find out more information or properly Googling for whatever I missed that you didn't.

I Google searched for Port St. Lucie and zips and ... but what 'quick Google search' did you perform to know that the data in this string was fixed length?

Gee whiz! Well, there ya go, Richard! It was fun anyway!

UPDATE: I just Googled 'drivers license swipe' and followed a link as to why some companies are swiping customers' cards and one guy responded with this, "I'm sorry, I don't have my driver's license on me, how about my concealed carry permit, would that work for ya?!?"

ROFLMAO! Yeah, c'mon, make my day! :giggle:

[RodSierra]

I think you'll find the details for the mag stripe info in this AAMVA document.

http://www.aamva.org/aamva/DocumentDisplay.aspx?id=%7B66260AD6-64B9-45E9-A253-B8AA32241BE0%7D

[comment]

You imply that recognizing the misspelling of Port St. Lucie (your item 2) triggered a thought to Google Search for something. Is that correct?

Yes (well, almost: I thought I said so explicitly, not implied ).

what 'quick Google search' did you perform to know that the data in this string was fixed length?

I searched for "magnetic stripe" and the first hit had this:

http://en.wikipedia.org/wiki/Magnetic_stripe_card#United_States_driver.27s_licenses

But RodSierra's link is much better.

Note that it's not fixed length, but truncated to maximum length.

---

A note about your method (in theory) you should have used Replace() or Middle() instead of Substitute() - consider Denver, Jackson, Reno - Renoir, etc.

Edited August 7, 2010 by Guest

[LaRetta]

Hi Michael,

I now see the logic of it and (I believe) your thinking progression:

a) Who in their right mind would provide a text string with city and last name together?

Port St. Lucie dropped the 'e' then it might be truncated (which also reinforced the theory of item a ... that there was probably a rule being applied).

I didn't connect my own observations of 'couldn't a better format be provided and 2) Luci might not be misspelled but truncated. Right over my head, it went!

I do not care about knowing the answers ... I care about understanding the thinking behind the trail which led to those answers. Thank you for explaining it!

And Rod, thank you for the link! I won't be caught on this issue again!! :wink2:

[Richard Corso]

I would like to thank the ones that responded. LeRetta, thanks for your code, I now need to find someone with a city name shorter than 13 characters. As in the document that RodSierra gave us, “This field shall be truncated with a field separator ^ if less than 13 characters long. If the city is exactly 13 characters long then no field separator is used”. I believe the field separator will be a space or .-' because in this track (track 1) they are using the ^ to separate the street. If I make the city staurt (staurt^johnson) the street middle will not work properly. So I am going to have to swipe a license with a shorter city to see what separator is used. I will let you all know how it go's. Thank a lot.

[RodSierra]

I think you can simulate other instances by interpreting the specification, its pretty clear. Take a look at Pg 61, Section F.6.1 Track 1, field three comment states when the separate is applied, and give the maximum characters for fields 3,4,5 in note i on the next page. From this info you can simulate different instances to test your calculation without hunting down drivers licenses.

[Richard Corso]

That is true, however I need to know what separator is used between the city and last name if the city is not 13 characters long. I do not think it could be the ^. That is use in that same track for the street address. As it stats in F.6.1. Track 1 Field 3 – Allowable Characters (A-Z) (.-') and space. Or is the a way to skip a separator?

[RodSierra]

Quoted from F.6.1

This field shall be truncated with a field separator ^ if less than 13 characters long. If the city is exactly 13 characters long then no field separator is used (see i).

I think your under the assumption the separator cannot be the same, it can. I interpret the separator as being as referenced above.

Hope this helps.

[Richard Corso]

I see what you are saying and I agree. But, if I shorten the city to say Stuart and add the ^ (Stuart^Johnson) the street pulls “Johnson$Frank$Gerald” instead of “186 NW PEACOCK BLVD. APT. 501”. I have attached the new file so you can play with the city and name and see what I and talking about.

DL_SwipeNew.zip

Edited August 8, 2010 by Guest

[comment]

There was a typo in my code above. I have fixed this some time after posting - apparently not soon enough.

[Richard Corso]

Apparently there is an "^" between the City and Last Name and this is what I was able to complete. Is there a way to start at the second "^" and go to the third "^" bypassing the first?

DL_Swipe8_8.zip

[comment]

Is there a way to start at the second "^" and go to the third "^" bypassing the first?

Yes, but you cannot do that because the first "^" is conditional - it's there only when City has less than 13 characters.

[Richard Corso]

That's stinks. If the city is less than 13, than it places the "^" after city and before last name. That causes the street to pickup "Last Name$First Name$Middle Name" not the street address that is between the second and third "^". Can I tell the Middle to start at a point after 16 spaces, I know form the link that RodSierra gave that the city field can only be 13 spaces and if it is shorter is the only time it would produce the "^".

[Richard Corso]

Ok, I think that I got the Street figured out. I created a field “Street1_First” the script:

Middle (Contacts::DL_Swipe; Position(Contacts::DL_Swipe; "$" ; 1; 1) + 1; Position (Contacts::DL_Swipe; "^"; 1; 2) + Position (Contacts::DL_Swipe; "^"; 1; 1) – 1)

Then the script to Middle this field:

Middle (Contacts::Street1_First; Position(Contacts::Street1_First; "^" ; 1; 1) + 1; Position (Contacts::Street1_First; "^"; 1; 2) - Position (Contacts::Street1_First; "^"; 1; 1) – 1)

And that seems to work on whatever street address I us. So I'm guessing the trick is to brake down the big ugly field to smaller ugly fields. Now on to the “Last Name”.

DL_Swipe8_8_REV.zip

[comment]

Why not do it all at once, as suggested in my post above? The idea here is to look for the end of city: this is either at the first "^" or after 13 characters - whichever comes first.

[Richard Corso]

I did it the way you showed me. This is for the street address. When the city is less than 13 characters it has the "^" at the end. The street address is separated by "^" also and at that time there is three instances of the "^" and my Middle Scrip for the street address end up with "Last Name$First Name$Meddle Name" and not the street.

[comment]

I believe you are making this even more complicated than it is already. The address starts at the first occurrence of "^" AFTER the city. The city ends at the very first occurrence of "^", or at 16 - whichever comes first.

Assuming that city and name combined will never be shorter than 13, you could use this shortcut to find the start of address =

Position ( text ; "^" ; 17 ; 1 ) + 1

[Richard Corso]

The Middle function is not a strong point for me and I know that I need a lot of work in that area. Thank you for your all your help and I will keep trying to get the "Position ( text ; "^" ; 17 ; 1 ) + 1" working as I trust that it will or you would not suggested it. It may be an extra step I took, but it gets me the street every time. So now it is off to the "Last Name" field.

[Richard Corso]

For anyone that is following this, I have finished it with the help of all that given me their input. And for that I thank you much. I hope that this helps someone else. I know that some of the steps that I have taken might be the long way, but I think that if it work right and gets you what you are looking for, then it must be the right way.

DL_Swipe8_8_New_REV.zip