How to Split a field from a database in Access

[ClearQuestion]

Good morning.
I have a field that contains name, address, CSZ all in 1 field and I need to split the field into 3 to 5 fields. Below is how the field appears, since the name can be any length I cannot use the Left function since it will be different. Any other suggestions on how to split the field up? The file comes over as a CSV and then we are importing it into Access.

Mickey L Mouse2111 NE 18th St Unit 3Grimes IA 51111
Pluto Klouse1411 Meskwaki RdTama IA 52222

I am looking to have the following fields from this one:
Name
Address
City/State/Zip
or
Name
Address
City
State
Zip

 

[theDBguy]

Hi. Welcome to AWF! What is the delimiter for the CSV file? It might be easier to split the field during the import process rather than after.

 

[jdraw]

Can you post 4 or 5 sample records as they come in from the csv?
Trying to determine the pattern(s) in your raw data. That is, how consistent is the data regarding format, missing fields etc.

 

[ClearQuestion]

The File is a Comma delimited File.

 

[ClearQuestion]

Here is the raw data with a few letters and numbers changed.

Paula P Chenowith906 Linden StBaxter IA 50028
Erika N Wolf5154 S Ovid AvenueDes Moines IA 50317
Jane H WReagan430 NW 2nd StEarlham IA 50072
Rubi K Owen Jr9514 Woodland Ave Apt 8Des Moines IA 50312
Michael Tubbman800 S 11th St Apt 1Norwalk IA 50211

 

[theDBguy]

The File is a Comma delimited File.

So, you're saying, for some reason, the source data is missing the commas between the names and addresses? That's a bummer.

 

[ClearQuestion]

So, you're saying, for some reason, the source data is missing the commas between the names and addresses? That's a bummer.

No the field contains all of the data it this one field. There is a comma before and after, I need to split it up.

 

[theDBguy]

No the field contains all of the data it this one field. There is a comma before and after, I need to split it up.

No, what I meant was in the original data, it doesn't come like this:

firstname, lastname, address, city, state, zip, otherdata, somemoredata, etc.

But rather, it just comes like this:

firstname lastname address city state zip, otherdata, somemoredata, etc.

 

[ClearQuestion]

No, what I meant was in the original data, it doesn't come like this:

firstname, lastname, address, city, state, zip, otherdata, somemoredata, etc.

But rather, it just comes like this:

firstname lastname address city state zip, otherdata, somemoredata, etc.

Got it! Yes it does suck they are not already separated. Now I need to find the best way to separate them.

 

[theDBguy]

Got it! Yes it does suck they are not already separated. Now I need to find the best way to separate them.

Well, you could try using the Split() function to split the field using maybe the space character as a delimiter, which I think is what jdraw is going to try for you, but that would leave for a lot of interpretation, which is why he was asking for some sample data to see if it's possible to determine a logic for it.

 

[Deleted member 147267]

Here is the raw data with a few letters and numbers changed.

That will quite possibly lead us down the wrong path. You should post exactly what the data looks like.

 

[isladogs]

Here is the raw data with a few letters and numbers changed.

Paula P Chenowith906 Linden StBaxter IA 50028
Erika N Wolf5154 S Ovid AvenueDes Moines IA 50317
Jane H WReagan430 NW 2nd StEarlham IA 50072
Rubi K Owen Jr9514 Woodland Ave Apt 8Des Moines IA 50312
Michael Tubbman800 S 11th St Apt 1Norwalk IA 50211

Something like this would be better:
Paula P Chenowith 906 Linden St Baxter IA 50028

1. Do the addresses ALWAYS start with a house number?
i.e. No e.g. Flat 1A or house names
2. Are there really no spaces between the name and house number?
3. Similarly no spaces between the street name and town?
If the answer to 1 is NO and /or the answers to 2 & 3 are YES, this is going to be a very difficult task

 

[ClearQuestion]

Something like this would be better:
Paula P Chenowith 906 Linden St Baxter IA 50028

1. Do the addresses ALWAYS start with a house number?
i.e. No e.g. Flat 1A or house names
2. Are there really no spaces between the name and house number?
3. Similarly no spaces between the street name and town?
If the answer to 1 is NO and /or the answers to 2 & 3 are YES, this is going to be a very difficult task

I only changed the data as it is health information, all the letters and numbers are in the spot as they are in the file just different than the file. For example I changed one 6 letter last name with a different 6 letter last name. Otherwise it is exactly as it in the file.

 

[plog]

With the data as you have it you are what we called "screwed". Even with a few shortcuts that I can show you this is going to take a lot of manual work on your part to fix.

So, can you get this data fixed? Why aren't the fields delimited in the file? Surely they aren't storing all that data in one field on their end. If not, divide and conquer:

Create a new table to do your work in. It should have these fields:

ID, RawData, First, Middle, Last, Addr, City, State, Zip

Your data goes into [RawData] and ID is an autonumber. So now you have a table with only the [RawData] and here's one record:

Erika N Wolf5154 S Ovid AvenueDes Moines IA 50317

1. Make an UPDATE query to move the last 5 characters of [RawData] to [Zip].
2. Remove the last 6 characters from [RawData].
3. Make an UPDATE query to move the last 2 characters of [RawData] to [State].
4. Remove the last 3 chatacters from [RawData].
5. Move all the data up to the first space in [RawData] to [First].
6. Remove all the data up to and including the first space of [RawData].

Now [RawData] has this:

N Wolf5154 S Ovid AvenueDes Moines

And now you spend the next year of your life cleaning this up. There's no way a computer knows that "Des" is part of the city and not part of the address. You can program to split "Wolf5154" into two fields based on where the first number appears, but what about an address with a PO Box? The last name would include "PO Box".

And you can't use word position to determine what piece of data it belongs to:

John Smith 123 Main Springfield IL --> 5th word is city
Mary Ann De La Garza 14 Broadway Avenue West Little Rock AR-->5th word is last name

This task requires a human, not a computer. I'd try to get good data first.

 

[theDBguy]

I only changed the data as it is health information, all the letters and numbers are in the spot as they are in the file just different than the file. For example I changed one 6 letter last name with a different 6 letter last name. Otherwise it is exactly as it in the file.

Hi. Colin asked some valid questions. Can you please answer his questions 1 to 3? Thanks.

 

[isladogs]

OK - in that case I'm going to drop out as I don't have the time to devote to this.
I think that devising logic that will work in all cases will be almost impossible.

However, good luck

 

[jdraw]

Hmmm?? Do you have an option to request the data as a csv along the lines that theDBGuy and Isladogs have suggested? I asked because if this is sensitive data, you shouldn't have to rely on parsing with some educated guessing to understand the raw data.

 

[Mark_]

Paula P Chenowith906 Linden StBaxter IA 50028
Erika N Wolf5154 S Ovid AvenueDes Moines IA 50317
Jane H WReagan430 NW 2nd StEarlham IA 50072
Rubi K Owen Jr9514 Woodland Ave Apt 8Des Moines IA 50312
Michael Tubbman800 S 11th St Apt 1Norwalk IA 50211

OK, problems I already see in your data. You have a first name followed by a space. This is followed EITHER by a single character middle initial OR the last name. Not too bad. You can check after the last name for where numbers start for the "Street address". This is a variable number of characters and groups of characters (street number, street name (one or more words), optional additional routing). The ONLY deliminator telling you where "Address" ends and "City" begins is a capitalized character within a string that isn't the first character. IF, and this is a GIANT IF, this is consistent then you can figure out where to break for "City". State and zip though should be rather easy as they will always be the last 8 characters.

So you get to start by figuring out how long the string is. Subtract 8 from it and save this as YourLength (you'll know that the last You can easily fill State/Zip with "Mid(YourString, YourLength+1, 2)" and "Mid(YourString, YourLength+4, 5)").

Then you get to work through each piece starting from the left.

And remember, this will fail horribly if the exports don't keep to the same case sensitive output.

 

[Gasman]

I'd be going back to whoever supplied that and ask for some decent data to work with.?

 

[isladogs]

To the OP
It is standard forum policy that threads with answers should not be deleted as the contents may be useful to others.
I have therefore restored it as a courtesy to those members who responded and to others.
Please don't delete it again