Help parsing text field in access

[Gary H]

Hi, I'm wonder how I need to go about parsing a text field in Access
2007. I have never done anything like this before.

I have a text field which looks something like this: (I'm guessing
there is some type of "return" character embedded in there to create
the separate lines). This data was exported from Crystal Reports.

1234 ABC Lane North
Suite 123
MN - 55121

I want to parse that out into 4 separate fields... Address1, address2,
State and Zip.

I'm hoping I can do this with a make-table query.. so I can dump my
raw data into a master table, and then run the query to create a new
table which has that address text field broken out into the individual
fields

Any help would be GREATLY appreciated!

-Gary

 

[Tom van Stiphout]

On Mon, 10 Nov 2008 15:44:32 -0800 (PST), Gary H <[email protected]>
wrote:

You probably could, with smart use of InStr and Mid.
Personally I would call a custom VBA function to do that:
select GetAddress1(MyConcatenatedField)
from MyTable

Before you start parsing, take the time to write down the rules, in
UNAMBIGUOUS language. For example one rule may be:
Address1 = the text before the first CRLF.

For address2:
Address2 = the text after the first CRLF and before the second CRLF.
Careful now: what if there is no suite number or other address2 info?
1234 ABC Lane North
MN - 55121
or:
1234 ABC Lane North
<empty line>
MN - 55121
Also decide whether you want to store a NULL value if no address2 (I
prefer that), or an empty string.

For State:
The text after the second CRLF and before the " - " substring. Also
lookup against the 50 US states and reject any text not found. Write
to exception log.

Then run the rules by your boss, and get buy-in.
Then implement it.
Miss one of these steps, and you may get burned.

-Tom.
Microsoft Access MVP

 

[Tushar Mandlekar]

Hi, I'm wonder how I need to go about parsing a text field in Access
2007. I have never done anything like this before.

I have a text field which looks something like this: (I'm guessing
there is some type of "return" character embedded in there to create
the separate lines). This data was exported from Crystal Reports.

1234 ABC Lane North
Suite 123
MN - 55121

I want to parse that out into 4 separate fields... Address1, address2,
State and Zip.

I'm hoping I can do this with a make-table query.. so I can dump my
raw data into a master table, and then run the query to create a new
table which has that address text field broken out into the individual
fields

Any help would be GREATLY appreciated!

-Gary

 

[Gary H]

You probably could, with smart use of InStr and Mid.
Personally I would call a custom VBA function to do that:
select GetAddress1(MyConcatenatedField)
  from MyTable

Before you start parsing, take the time to write down the rules, in
UNAMBIGUOUS language. For example one rule may be:
Address1 = the text before the first CRLF.

For address2:
Address2 = the text after the first CRLF and before the second CRLF.
Careful now: what if there is no suite number or other address2 info?
1234 ABC Lane North
MN - 55121
or:
1234 ABC Lane North
<empty line>
MN - 55121
Also decide whether you want to store a NULL value if no address2 (I
prefer that), or an empty string.

For State:
The text after the second CRLF and before the " - " substring. Also
lookup against the 50 US states and reject any text not found. Write
to exception log.

Then run the rules by your boss, and get buy-in.
Then implement it.
Miss one of these steps, and you may get burned.

-Tom.
Microsoft Access MVP










- Show quoted text -

Tom, thank you for your reply! This is very helpful.

Luckily the system that the data is coming out of creates an empty
line for row 2, so it's always going to be 4 rows of data. I will use
null for that line if empty. Also the state is always going to be 2
chars, and the hyphen always in the same spot and the zip is always 5
characters.

I will run this by the boss this morning and have a go at it this
afternoon!

-Gary