By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,589 Members | 1,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,589 IT Pros & Developers. It's quick & easy.

Parsing some text data into records...

P: n/a
MLH
A fairly long text blurb below lists cities and local exchanges.
Each line is in a separate record in an Access table consisting
of a single memo field. I would like to parse it into many more
records in another table containing 2 fields:
[City] and [Exchange] that would look something like this:

[City] [Exchange]
Advance 940
Advance 941
Advance 993
Anderson 421
Asheboro 216
Asheboro 267
Asheboro 301
Asheboro 302
Asheboro 308
....

All suggestions appreciated!
Now, here is the source list I'm working from:
Advance: 940, 941, 993
Anderson: 421
Asheboro: 216, 267, 301, 302, 308, 318, 328, 460, 465, 483, 588, 610,
625, 626, 629, 633, 636, 653, 672, 683, 736, 953, 963, 964
Badin Lake: 461
Baldwin: 876, 877, 977
Beulah: 352
Boomer: 921
Boonville: 367
Brooks: 467, 468, 469, 466
Burlington: 212, 213, 214, 221, 222, 223, 226, 227, 228, 229, 260,
261, 263, 264, 266, 269, 270, 278, 290, 329, 343, 380, 395, 396, 417,
436, 437, 438, 494, 506, 512, 513, 516, 524, 532, 534, 535, 536, 538,
539, 557, 567, 570, 578, 582, 584, 585, 586, 639, 646, 675, 684,693,
792
Champion: 973
Churchland: 220, 752
Clingman: 984
Courtney: 463
Creston: 385
Danbury: 859
Denton: 593
Dobson: 386
East Bend: 699
Eden: 589, 632, 627, 635
Elkin: 244, 428, 526, 527, 835
Farmer: 857
Forbush: 961
Gibsonville: 446, 447, 449, 603
Glade Creek: 657
Greensboro: 202, 206, 207, 209, 210, 215, 217, 218, 230, 232, 235,
252, 253, 254, 255, 256, 268, 271, 272, 273, 274, 275, 279, 282, 285,
286, 288, 291, 292, 294, 297, 299, 303, 312, 314, 315, 316, 317, 319,
321, 323, 324, 327, 332, 333, 334, 335, 337, 338, 339, 340, 346, 358,
362, 369, 370, 373, 375, 378, 379, 382, 383, 387, 389, 392, 393, 398,
402, 404, 412, 420, 424, 430, 433, 451, 456, 457, 458, 478, 482, 507,
508, 509, 510, 517, 533, 540, 541, 542, 543, 544, 545, 547, 549, 550,
553, 554, 556, 558, 574, 579, 580, 587, 601, 605, 615, 621, 632, 641,
662, 664, 665, 668, 669, 674, 676, 678, 680, 681, 686, 691, 697, 698,
706, 707, 708, 709, 790, 805, 808, 809, 832, 834, 851, 852, 854, 855,
856, 907, 908, 913, 931, 954, 965, 987, 988
Hays: 696
High Point: 410, 431, 434, 442, 454, 471, 491, 801, 802, 803, 804,
812, 819, 821, 822, 841, 845, 847, 848, 861, 862, 869, 871, 878, 880,
881, 882, 883, 884, 885, 886, 887, 888, 889
Jackson Creek: 241
Julian: 685
Kernersville: 310, 423, 497, 564, 992, 993, 996
Kimesville: 565
King: 983, 985
Lansing: 384
Level Cross: 371, 374, 401
Lewisville: 945, 946
Lexington: 224, 225, 236, 237, 238, 239, 240, 242, 243, 247, 248, 249,
250, 309, 357, 470, 479, 746, 853, 956
Liberty: 622
Lomax: 927, 928, 957
Madison: 427, 445, 453, 548
Monticello: 295, 484, 656
Mount Airy: 710, 719, 755, 783, 786, 789
Mulberry: 670
NathansCreek: 982
North Wilksboro: 262, 566, 651, 658, 667, 838, 902, 903, 981
Oldtown: 922, 923, 924
Pilot Mountain: 368
Pisgah: 381
Prospect Hill: 562
Quaker Gap: 994
Ramseur: 824
Randleman: 495, 498
Red Brush: 320
Reeds: 787, 797
Reidsville: 205, 280, 342, 344, 347, 348, 349, 361, 365, 394, 432,
496, 552, 613, 616, 634, 637, 791, 932, 951
Roaring Gap: 200, 363
Roxboro: 322, 330, 502, 503, 504, 583, 592, 597, 598, 599, 628
Ruffin: 939
Rural Hall: 969
Sandy Ridge: 871
Saxapahaw: 376
Scottville: 359
Seagrove: 873
Shoals: 325, 326
Southmont: 798
Sparta: 372, 572
Stanleyville: 377
State Road: 874
Stoneville: 573
Summerfield: 598, 485, 560, 643, 644
Thomasville: 472, 474, 475, 476
Timberlake: 364
Troy: 220, 571, 572, 576
Walkertown: 595
Walnut Cove: 591
Welcome: 731
West End: 466, 673
West Jefferson: 291, 246, 846
Westfield: 351
Winston Salem: 201, 208, 231, 245, 251, 277, 283, 287, 293, 306, 331,
345, 354, 391, 397, 399, 403, 406, 407, 408, 409, 431, 414, 416, 418,
435, 462, 464, 473, 480, 499, 519, 531, 575, 677, 607, 614, 624, 631,
647, 650, 655, 659, 660, 661, 671, 682, 692, 703, 705, 712, 713, 714,
715, 716, 717, 718, 720, 721, 722, 723, 724, 725, 726, 727, 728, 729,
730, 732, 733, 734, 735, 741, 744, 745, 747, 748, 749, 750, 757, 758,
759, 760, 761, 764, 765, 766, 767, 768, 769, 770, 771, 773, 774, 775,
776, 777, 778, 779, 782, 784, 785, 788, 793, 794, 796, 806, 813, 816,
817, 829, 830, 831, 837, 896, 912, 917, 918, 926, 970, 971, 972, 978,
986, 995, 997
Yadkinville: 677, 679, 849
Yanceyville: 514, 694
Zephyr: 353, 366

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
MLH
OK, I have gotten this far with my source data,
but can't get any further. Its now separated
into 4 fields, the last of which is a Memo field...
AreaID AreaCode City Notes
1 336 Advance 940, 941, 993
2 336 Anderson 421
3 336 Asheboro 216, 267, 301, 302, 308, 318, 328,
460, 465, 483, 588, 610, 625, 626, 629, 633, 636, 653, 672, 683, 736,
953, 963, 964
4 336 Badin Lake 461
5 336 Baldwin 876, 877, 977
6 336 Beulah 352
7 336 Boomer 921
8 336 Boonville 367
9 336 Brooks 467, 468, 469, 466
10 336 Burlington 212, 213, 214, 221, 222, 223, 226,
227, 228, 229, 260, 261, 263, 264, 266, 269, 270, 278, 290, 329, 343,
380, 395, 396, 417, 436, 437, 438, 494, 506, 512, 513, 516, 524, 532,
534, 535, 536, 538, 539, 557, 567, 570, 578, 582, 584, 585, 586, 639,
646, 675, 684,693, 792
11 336 Champion 973
12 336 Churchland 220, 752
13 336 Clingman 984
14 336 Courtney 463
15 336 Creston 385
16 336 Danbury 859
17 336 Denton 593
18 336 Dobson 386
19 336 East Bend 699
20 336 Eden 589, 632, 627, 635
21 336 Elkin 244, 428, 526, 527, 835
22 336 Farmer 857
23 336 Forbush 961
24 336 Gibsonville 446, 447, 449, 603
25 336 Glade Creek 657
26 336 Greensboro 202, 206, 207, 209, 210, 215, 217,
218, 230, 232, 235, 252, 253, 254, 255, 256, 268, 271, 272, 273, 274,
275, 279, 282, 285, 286, 288, 291, 292, 294, 297, 299, 303, 312, 314,
315, 316, 317, 319, 321, 323, 324, 327, 332, 333, 334, 335, 337, 338,
339, 340, 346, 358, 362, 369, 370, 373, 375, 378, 379, 382, 383, 387,
389, 392, 393, 398, 402, 404, 412, 420, 424, 430, 433, 451, 456, 457,
458, 478, 482, 507, 508, 509, 510, 517, 533, 540, 541, 542, 543, 544,
545, 547, 549, 550, 553, 554, 556, 558, 574, 579, 580, 587, 601, 605,
615, 621, 632, 641, 662, 664, 665, 668, 669, 674, 676, 678, 680, 681,
686, 691, 697, 698, 706, 707, 708, 709, 790, 805, 808, 809, 832, 834,
851, 852, 854, 855, 856, 907, 908, 913, 931, 954, 965, 987, 988
27 336 Hays 696
28 336 High Point 410, 431, 434, 442, 454, 471, 491,
801, 802, 803, 804, 812, 819, 821, 822, 841, 845, 847, 848, 861, 862,
869, 871, 878, 880, 881, 882, 883, 884, 885, 886, 887, 888, 889
29 336 Jackson Creek 241
30 336 Julian 685
31 336 Kernersville 310, 423, 497, 564, 992, 993, 996
32 336 Kimesville 565
33 336 King 983, 985
34 336 Lansing 384
35 336 Level Cross 371, 374, 401
36 336 Lewisville 945, 946
37 336 Lexington 224, 225, 236, 237, 238, 239, 240,
242, 243, 247, 248, 249, 250, 309, 357, 470, 479, 746, 853, 956
38 336 Liberty 622
39 336 Lomax 927, 928, 957
40 336 Madison 427, 445, 453, 548
41 336 Monticello 295, 484, 656
42 336 Mount Airy 710, 719, 755, 783, 786, 789
43 336 Mulberry 670
44 336 NathansCreek 982
45 336 North Wilksboro 262, 566, 651, 658, 667, 838, 902,
903, 981
46 336 Oldtown 922, 923, 924
47 336 Pilot Mountain 368
48 336 Pisgah 381
49 336 Prospect Hill 562
50 336 Quaker Gap 994
51 336 Ramseur 824
52 336 Randleman 495, 498
53 336 Red Brush 320
54 336 Reeds 787, 797
55 336 Reidsville 205, 280, 342, 344, 347, 348, 349,
361, 365, 394, 432, 496, 552, 613, 616, 634, 637, 791, 932, 951
56 336 Roaring Gap 200, 363
57 336 Roxboro 322, 330, 502, 503, 504, 583, 592, 597, 598,
599, 628
58 336 Ruffin 939
59 336 Rural Hall 969
60 336 Sandy Ridge 871
61 336 Saxapahaw 376
62 336 Scottville 359
63 336 Seagrove 873
64 336 Shoals 325, 326
65 336 Southmont 798
66 336 Sparta 372, 572
67 336 Stanleyville 377
68 336 State Road 874
69 336 Stoneville 573
70 336 Summerfield 598, 485, 560, 643, 644
71 336 Thomasville 472, 474, 475, 476
72 336 Timberlake 364
73 336 Troy 220, 571, 572, 576
74 336 Walkertown 595
75 336 Walnut Cove 591
76 336 Welcome 731
77 336 West End 466, 673
78 336 West Jefferson 291, 246, 846
79 336 Westfield 351
80 336 Winston Salem 201, 208, 231, 245, 251, 277, 283,
287, 293, 306, 331, 345, 354, 391, 397, 399, 403, 406, 407, 408, 409,
431, 414, 416, 418, 435, 462, 464, 473, 480, 499, 519, 531, 575, 677,
607, 614, 624, 631, 647, 650, 655, 659, 660, 661, 671, 682, 692, 703,
705, 712, 713, 714, 715, 716, 717, 718, 720, 721, 722, 723, 724, 725,
726, 727, 728, 729, 730, 732, 733, 734, 735, 741, 744, 745, 747, 748,
749, 750, 757, 758, 759, 760, 761, 764, 765, 766, 767, 768, 769, 770,
771, 773, 774, 775, 776, 777, 778, 779, 782, 784, 785, 788, 793, 794,
796, 806, 813, 816, 817, 829, 830, 831, 837, 896, 912, 917, 918, 926,
970, 971, 972, 978, 986, 995, 997
81 336 Yadkinville 677, 679, 849
82 336 Yanceyville 514, 694
83 336 Zephyr 353, 366
85 919 Mebane 304, 563, 568
87 910 Biscoe 428, 468, 569
88 910 Candor 974, 975
89 910 Mount Gilead 439
Nov 12 '05 #2

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have you tried the VBA Split() function (Access 2000 & greater). It
can move items from a comma-delimited string into an array. E.g.:

a = split(Notes, ",")
a(0) = "Advance"
a(1) = "940"
a(2) = "941"
a(3) = "993"

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP9ugvIechKqOuFEgEQKudwCg7u+4jBvgLTHBqWJGONP8Cq eAeH4AoK0o
726k0Y2kIoA7msQpivHM3GcM
=7UCZ
-----END PGP SIGNATURE-----

MLH wrote:
OK, I have gotten this far with my source data,
but can't get any further. Its now separated
into 4 fields, the last of which is a Memo field...
AreaID AreaCode City Notes
1 336 Advance 940, 941, 993
2 336 Anderson 421
3 336 Asheboro 216, 267, 301, 302, 308, 318, 328,
460, 465, 483, 588, 610, 625, 626, 629, 633, 636, 653, 672, 683, 736,
953, 963, 964
4 336 Badin Lake 461


< SNIP >

Nov 12 '05 #3

P: n/a
MLH
Sure haven't. Is function available in A97?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Sat, 13 Dec 2003 23:28:49 GMT, MGFoster <me@privacy.com> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have you tried the VBA Split() function (Access 2000 & greater). It
can move items from a comma-delimited string into an array. E.g.:

a = split(Notes, ",")
a(0) = "Advance"
a(1) = "940"
a(2) = "941"
a(3) = "993"

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP9ugvIechKqOuFEgEQKudwCg7u+4jBvgLTHBqWJGONP8Cq eAeH4AoK0o
726k0Y2kIoA7msQpivHM3GcM
=7UCZ
-----END PGP SIGNATURE-----

MLH wrote:
OK, I have gotten this far with my source data,
but can't get any further. Its now separated
into 4 fields, the last of which is a Memo field...
AreaID AreaCode City Notes
1 336 Advance 940, 941, 993
2 336 Anderson 421
3 336 Asheboro 216, 267, 301, 302, 308, 318, 328,
460, 465, 483, 588, 610, 625, 626, 629, 633, 636, 653, 672, 683, 736,
953, 963, 964
4 336 Badin Lake 461


< SNIP >


Nov 12 '05 #4

P: n/a
MLH
Thx. Will look into this.

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.