Address Parsing

[wjburke2]

Has anyone had to parsing address fields? I am receiving two files from the USPS one that has the house number, direction, street name, city, state, zip in separate fields and one the has the full address in one field and city, st, zip in another. I need to merge these two files to create one layout which will be the parsed address.

7700 N WILLOW CHASE AVE BLVD APT, LIBERTY MO 64068

"7700", N, WILLOW CHASE, BLVD, APT, "103", LIBERTY, MO, "64068"

If someone has something or can point me in the right direction that would be great.

 

[Brianwarnock]

Functions such as Instr InsrtRev Left Mid Right and Len are usually involved in parsing, although I have also seen SPLIT used in a function when the data allows it.

Brian

 

[wjburke2]

Thanks Brian, I was thinking I would be using those functions. The address field looks like it will be easy because it has fixed length fields. The CSZ field on the other hand requires the ability to strip off parts, and then deal with what is left. I was hoping someone had already invented this wheel. Nice avatar by the way where is that?

 

[Brianwarnock]

Sorry I don't understand the comment on the CSZ field.
There have been posts on parsing on the forum in the past.

The avatar is a picture I took of the Froncysylite aquaduct carrying the LLangollen canal 200 foot above the River Dee in the Vale of LLangollen, North Wales. I'm glad you like it, the scenery round there is stunning, its good walking country.

Brian

 

[vbaInet]

Brian, did you manage to get the pronunciation of Llangollen right?

 

[Brianwarnock]

Of course , but don't ask me to spell it phonetically its something like
clangothlen but not really possible with an English tongue.

Brian

 

[wjburke2]

I figured the Zip will always be the last field, State, the second to the last, then City is all that is left. Sometimes there is only a Zip and no City State but there is always a zip. The City can be any lenght and number of words, Zip is always 5, state is 2. If can get the the ZIP, validate and deleteing it form the orginial field leaving the City, state, then pick off the state, what ever is left is the city. I am a little new to VBA so I am not sure if I can do this or if it is the best way.

 

[vbaInet]

Of course , but don't ask me to spell it phonetically its something like
clangothlen but not really possible with an English tongue.

Brian

Hehe! I know how difficult it could be. My first encounter was a double whammy - Llanelli

wjburke: Not sure your reasons for merging both. Are there cases where one file has data in one field and the other hasn't?

 

[Brianwarnock]

I figured I the Zip will always be the last field, State, the second to the last, then City is all that is left. Sometimes there is only a Zip and no City State but there is always a zip. The City can be any lenght and number of words, Zip is always 5, state is 2. If can get the the ZIP, validate and deleteing it form the orginial field leaving the City, state, then pick off the state, what ever is left is the city. I am a little new to VBA so I am not sure if I can do this or if it is the best way.

That sounds possible Right(fldname,5) will get the zip
then InstrRev(fld," ",len(fld)-7) will give the position of the next blank from the end but counting from the start you can then compare this to len(fld)-5 , the one before the zip to see if you have a state, ie is the diff 3 or more.

I wonder if the split is a better option, I haven't time to consider thisa evening and don't really know your data.

Brian

 

[wjburke2]

This used to be one file. Now it is three different files, one contains address changes and the other non-deliverable addresses, the third is like the original and contains both record types. We have a process in place to handle the old file layout. I was trying to avoid rewriting the system by using access to import the files and export them as one file. Which could then be ran through the old system. The records are different I just used the same address for illustration of what the files look like.

 

[Brianwarnock]

Have you solved this?
If not is this the bit that you need to handle?
LIBERTY MO 64068

and it can be
CSZ
CZ
SZ

Just Z ?
can it be any other? Will Z always be there?

Are you aiming to parse these into separate fields?

Brian

 

[wjburke2]

Thanks for checking back Brian, I decided to spend a couple of days and try to get as close as I could before comming back. This is comming in as a spead sheet. I am importing into a table and then trying to parse it with a query.

Trying to get the state is giving me a headache. I am getting one character or a error. The sample data I have is at: http://ribbs.usps.gov/intelligentma...ta/FullServiceACSCOASampleDataEXCELFormat.zip

Mid([OnPieceCityStateZip],InStrRev([OnPieceCityStateZip]," ",Len([OnPieceCityStateZip])-7),2) AS OldState

Returns

M from LIBERTY MO 64068
G from ATLANTA GA 30308
T from TOMBALL TX 77375
#Error from __37128

So far I have
SELECT FullSvcNixie.ID, FullSvcNixie.OriginalMailerID6, FullSvcNixie.OriginalMailerID9, FullSvcNixie.UserLicenseCode, FullSvcNixie.MaildatJobID, FullSvcNixie.CustomerGroupID, FullSvcNixie.MailingGroupID, FullSvcNixie.OriginalIMB, FullSvcNixie.RecordCreationDate, FullSvcNixie.ActionCode, FullSvcNixie.OnPieceCityStateZip, FullSvcNixie.ReturnedToAddress, FullSvcNixie.ReturnedToCityStateZip, FullSvcNixie.ReasonCode, FullSvcNixie.KeylineFromEDoc, FullSvcNixie.ClassNotificationType, FullSvcNixie.FeeNotification, Mid([ParsedAddressOnPiece],1,10) AS OldPrimaryNumber, Mid([ParsedAddressOnPiece],11,2) AS OldPreDir, Mid([ParsedAddressOnPiece],13,28) AS OldStreetName, Mid([ParsedAddressOnPiece],41,4) AS OldStreetSfx, Mid([ParsedAddressOnPiece],45,2) AS OldPostDir, Mid([ParsedAddressOnPiece],47,4) AS OldUnitDsgn, Mid([ParsedAddressOnPiece],51,10) AS OldSecNumber, Right([FullSvcNixie].[OnPieceCityStateZip],5) AS OldZipCode, Mid([OnPieceCityStateZip],InStrRev([OnPieceCityStateZip]," ",Len([OnPieceCityStateZip])-7),2) AS OldState
FROM FullSvcNixie;

 

[vbaInet]

Had a quick look at your data and I think you should go with the Split() function as Brian was thinking. Handle each record during import and insert into your db the broken bits.

 

[Brianwarnock]

I will try to look at it this weekend. but you didn't answer my questions.
You need a +1 on the start position as the InstrRev is giving the " " not the first letter so you are getting " M" not just "M"
try

Mid([OnPieceCityStateZip],InStrRev([OnPieceCityStateZip]," ",Len([OnPieceCityStateZip])-7)+1,2)

Brian

 

[Brianwarnock]

The data in the excel sheet on the link is parsed.

Brian

 

[wjburke2]

Thank you to everyone that contributed to this thread. I figured it out using the information you provided. The final piece was the +1. I tried to get the link to the sample Nixie file but the USPS site was down.

After adding the +1 (Thank you Brain) it was simple to figure out. If I had the position of the space before the state that mumber was also the length of the City field.

SELECT FullSvcNixie.ID, FullSvcNixie.OriginalMailerID6, FullSvcNixie.OriginalMailerID9, FullSvcNixie.UserLicenseCode, FullSvcNixie.MaildatJobID, FullSvcNixie.CustomerGroupID, FullSvcNixie.MailingGroupID, FullSvcNixie.OriginalIMB, FullSvcNixie.RecordCreationDate, FullSvcNixie.ActionCode, FullSvcNixie.OnPieceCityStateZip, FullSvcNixie.ReturnedToAddress, FullSvcNixie.ReturnedToCityStateZip, FullSvcNixie.ReasonCode, FullSvcNixie.KeylineFromEDoc, FullSvcNixie.ClassNotificationType, FullSvcNixie.FeeNotification,
Mid([ParsedAddressOnPiece],1,10) AS OldPrimaryNumber,
Mid([ParsedAddressOnPiece],11,2) AS OldPreDir,
Mid([ParsedAddressOnPiece],13,28) AS OldStreetName,
Mid([ParsedAddressOnPiece],41,4) AS OldStreetSfx,
Mid([ParsedAddressOnPiece],45,2) AS OldPostDir,
Mid([ParsedAddressOnPiece],47,4) AS OldUnitDsgn,
Mid([ParsedAddressOnPiece],51,10) AS OldSecNumber,
Mid([OnPieceCityStateZip],1,InStrRev([OnPieceCityStateZip]," ",Len([OnPieceCityStateZip])-7)) AS OldCity,
Mid([OnPieceCityStateZip],InStrRev([OnPieceCityStateZip]," ",Len([OnPieceCityStateZip])-7)+1,2) AS OldState,
Right([FullSvcNixie].[OnPieceCityStateZip],5) AS OldZipCode
FROM FullSvcNixie;