How to Split a field from a database in Access

[jdraw]

Thanks Colin. I hope the OP hasn't given up. He/she did ask for help.

 

[Deleted member 147267]

ClearQuestion, are we to assume you are done with this thread?
If it's going to be a repeating problem and/or there's something on the order of 1 or 2 thousand records, then maybe you're best doing it manually. I don't know where the line is drawn with respect to it being worth while.

I was thinking that if you could get a table of IA cities from somewhere (or if your problem is limited to a customer base it would be smaller), you'd have a chance to split Des Moines from 8. As for what seems to be the other half of the issue, only the 2nd or 3rd chunk seems to need splitting, and only if it contains a number. If chunk 2 doesn't contain a number, it's an initial. If it does, it needs to be split. Other chunks that contain numbers don't seem to need splitting (11th). I have no idea what is to be done with the split string if it ever gets this far. Hopefully it's not to break it up into fields.
Sorry if I missed something in the data and am way out in left field. The suggestion to get the source fixed is sometimes best, but not always doable.

 

[The_Doc_Man]

I would have to say that if this were me, I would talk to whoever is in charge of the source of the data and see if they have some other format available that doesn't merge those fields. I have a darned good fine-level text parser but as gusty as I am in building a parser automaton, I wouldn't touch this as is was shown.

My advice here might be a little bit bizarre to some folks, but ... when faced with an intractable problem, sometimes the best solution is to change the problem.

 

[Gasman]

Well I would have hoped that the person who supplied the data would have checked it first, but testing seems not to be as good as it used to be.

By the same token, if someone is not told something is wrong, how will they ever know.?
I am always happy for someone to tell me my mistakes, so I can correct them and hopefully remember that for the future.

Even if they knew that was the format, it should not be hard for them to alter it, to make your life easier, surely.?

 

[jdraw]

ClearQuestion,
I'm not sure where you stand with this thread. You did ask for help and I agree with Doc and others who have suggested that your first action should be to request your source for an alternate, more useful format of the data.
I have looked at parsing your existing set up, and although it can/could be done, any slight variation in the incoming data structure could play havoc with any string parsing algorithm(s).

I have looked at some test data:

    raws(0) = "Paula P Chenowith906 Linden StBaxter IA 50028"
20        raws(1) = "Erika N Wolf5154 S Ovid AvenueDes Moines IA 50317"
30        raws(2) = "Jane H WReagan430 NW 2nd StEarlham IA 50072"
40        raws(3) = "Rubi K Owen Jr9514 Woodland Ave Apt 8Des Moines IA 50312"
50        raws(4) = "Michael Tubbman800 S 11th St Apt 1Norwalk IA 50211"
60        raws(5) = "Ethyl O'Ether347 Orange Grove Blvd Suite 32St. Augustine FL 32080"
70        raws(6) = "Mickey L Mouse2111 NE 18th St Unit 3Grimes IA 51111"
80        raws(7) = "Jack DRipper220 Saint Bolivar CrescentSt Olaf IA 52286"
90        raws(8) = "Polly GDactyll24 ManyFingers Lane Suite 92Sioux City IA 52222"
100       raws(9) = "Ima Dangerfield234411 Big Round CirclePanama City IA 52122"
110       raws(10) = "Bob Jones23A Meskeramiki RdEly IA 52222"

which were parsed to (with a few glitches)

Paula P Chenowith
906 Linden St
Baxter IA 50028

Erika N Wolf
5154 S Ovid Avenue
Des Moines IA 50317

Jane H W Reagan
430 NW 2nd St
Earlham IA 50072

Rubi K Owen Jr
9514 Woodland Ave Apt 8
Des Moines  IA 50312

Michael Tubbman
800 S 11th St Apt 1
Norwalk  IA 50211

Ethyl O'Ether
347 Orange Grove Blvd Suite 32
St. Augustine  FL 32080

Mickey L Mouse
2111 NE 18th St Unit 3
Grimes  IA 51111

Jack D Ripper
220 Saint Bolivar Crescent
St Olaf IA 52286

Polly G Dactyll
24 ManyFingers Lane Suite 92
Sioux City  IA 52222

Ima Dangerfield
234411 Big Round Circle
Panama City IA 52122

Bob Jones
23A Meskeramiki Rd
Ely IA 52222

an interesting exercise, but dependent on the sample data.
I suggest you contact the source of your data and request a comma separated, quote delimited file with individual fields exposed.

Good luck with your project.

 

[fertooos]

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.