How to Split a field from a database in Access (1 Viewer)

ClearQuestion

New member
Local time
Today, 10:58
Joined
Dec 3, 2019
Messages
6
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

I’m here to help
Staff member
Local time
Today, 08:58
Joined
Oct 29, 2018
Messages
21,360
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

Super Moderator
Staff member
Local time
Today, 11:58
Joined
Jan 23, 2006
Messages
15,364
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

New member
Local time
Today, 10:58
Joined
Dec 3, 2019
Messages
6
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
 

ClearQuestion

New member
Local time
Today, 10:58
Joined
Dec 3, 2019
Messages
6
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

I’m here to help
Staff member
Local time
Today, 08:58
Joined
Oct 29, 2018
Messages
21,360
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:
Code:
firstname, lastname, address, city, state, zip, otherdata, somemoredata, etc.
But rather, it just comes like this:
Code:
firstname lastname address city state zip, otherdata, somemoredata, etc.
 

ClearQuestion

New member
Local time
Today, 10:58
Joined
Dec 3, 2019
Messages
6
No, what I meant was in the original data, it doesn't come like this:
Code:
firstname, lastname, address, city, state, zip, otherdata, somemoredata, etc.
But rather, it just comes like this:
Code:
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

I’m here to help
Staff member
Local time
Today, 08:58
Joined
Oct 29, 2018
Messages
21,360
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.
 

Micron

AWF VIP
Local time
Today, 11:58
Joined
Oct 20, 2018
Messages
3,476
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

MVP / VIP
Local time
Today, 15:58
Joined
Jan 14, 2017
Messages
18,186
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

New member
Local time
Today, 10:58
Joined
Dec 3, 2019
Messages
6
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

Banishment Pending
Local time
Today, 10:58
Joined
May 11, 2011
Messages
11,613
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’m here to help
Staff member
Local time
Today, 08:58
Joined
Oct 29, 2018
Messages
21,360
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

MVP / VIP
Local time
Today, 15:58
Joined
Jan 14, 2017
Messages
18,186
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

Super Moderator
Staff member
Local time
Today, 11:58
Joined
Jan 23, 2006
Messages
15,364
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_

Longboard on the internet
Local time
Today, 08:58
Joined
Sep 12, 2017
Messages
2,111
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

Enthusiastic Amateur
Local time
Today, 15:58
Joined
Sep 21, 2011
Messages
14,059
I'd be going back to whoever supplied that and ask for some decent data to work with.?
 

isladogs

MVP / VIP
Local time
Today, 15:58
Joined
Jan 14, 2017
Messages
18,186
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
 

Users who are viewing this thread

Top Bottom