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
4  1424 
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
-----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 >
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 >
Thx. Will look into this.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Artco News |
last post by:
I thought I ask the scripting guru about the following.
I have a file containing records of data with the following format(first
column is the label):
CODE#1^DESCRIPTION^CODE#2^NOTES...
|
by: ralphNOSPAM |
last post by:
Is there a function or otherwise some way to pull out the target text
within an XML tag?
For example, in the XML tag below, I want to pull out 'CALIFORNIA'.
...
|
by: JMCN |
last post by:
hello
i'm trying to parse out some data from the below format. first of all,
i import the text file into access97 table and the try to parse out.i
have a code written but when i run it, it does...
|
by: Pavils Jurjans |
last post by:
Hello,
Is there some class among the thousands of .Net classes, that would do the
tab-delimited text data parsing into, say, jagged array (an array of
arrays). I have some code sources in other...
|
by: M |
last post by:
Hi,
I need to parse text files to extract data records. The files will
consist of a header,
zero or more data records, and a trailer. I can discard the header and
trailer but I must split the...
|
by: ILCSP |
last post by:
Hi, I just started learning Visual Basic (VB.NET 03) and I need to do
this small program that will read a text file we get from another
company that has survey data, parse it and flatten it out and...
|
by: zoltan |
last post by:
Hi,
The scenario is like this :
struct ns_rr {
const u_char* rdata;
};
The rdata field contains some fields such as :
|
by: bfowlkes |
last post by:
Hello,
I am trying to parse two pre-formatted text files and write them to a
different files formatted in a different way. The story about this is I
was hired along with about 20 other people...
|
by: Daniel Nogradi |
last post by:
Hi list,
I have an awk program that parses a text file which I would like to
rewrite in python. The text file has multi-line records separated by
empty lines and each single-line field has two...
|
by: reddyth |
last post by:
Dear All,
I wanted to parse an XML file and print the element's content.
I have the following code for the same. I have printed the ourput too.
The problem is it is printing unwanted spaces and...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
