How to Split a field from a database in Access (1 Viewer)
- Thread starter ClearQuestion
- Start date
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.
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.
- Local time
- Today, 12:14
- Joined
- Feb 28, 2001
- Messages
- 27,127
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.
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
Enthusiastic Amateur
- Local time
- Today, 18:14
- Joined
- Sep 21, 2011
- Messages
- 14,217
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.?
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.?
Last edited:
- Local time
- Today, 13:14
- Joined
- Jan 23, 2006
- Messages
- 15,380
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:
which were parsed to (with a few glitches)
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.
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:
Code:
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)
Code:
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 52222an 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.
Last edited:
