Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    18

    Unanswered: Need Code to split a semicolon delimited field

    I have a table where several fields contain delimited information. There are 25,000 records and I need to split the delimited data into join tables of a M:M relationship.

    Original Table (This is the dirty data table)

    Serial Code 1 Code 2
    12345 AB;AE R-3E3-1234-29; T-ABC-4321-23

    Main Table 1(One Side) (This is the unique main report table)
    Serial
    12345

    Main Table 2(One Side) (This is the unique Code 1 table)
    Code 1
    AB
    AE

    Main Table 3(One Side) (This is the unique Code 2 Table)
    R-3E3-1234-29
    T-ABC-4321-23

    Join Table 1

    Serial Code 1
    12345 AB
    12345 AE

    Join Table 2

    Serial Code 2
    12345 R-3E3-1234-29
    12345 T-ABC-4321-23

    The plan is to export data from a web based database into an excel spreadsheet. Find and replace to ensure that all the data is delimited with a semicolon and remove all spaces. Then import the excel spreadsheet into the access database to a table called Dirty Data. At this point I want a button to press to run the code to split the delimited data into the individual rows of the appropriate tables. I also need to keep the serial number integrity so that the Many:Many tables are auto filled.

    I am a novice at Access so please let me also know where to cut and paste the code. I really appreciate any help I can get on this.
    Last edited by scryma; 01-08-11 at 05:04. Reason: add info

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    I would develop a Public Procedure in a standard module that used the Split() finction to pars the field.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Sep 2003
    Posts
    18
    Thanks for the tip. Unfortunately what I really need is the code so that i can copy and paste it. I have little code writing experience and do not know how to write it nor where to put it in the database. Most of what I can do is structure the DB but I have problems when i venture too far past simple commands.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If this is a consulting request, I'll move this post in the job opportunities forum. If you would like help figuring out where to get started with VBA to accomplish your task, feel free to reach out here.

    What would you like to do?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If this is a regular process then I'd suggest you write a VBA procedure to habdle the import, do the error checking and so on.

    if its a one off you could probably dispense with the VBA and do it as a clunky series of queries and appeend the data to the correct tables.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2003
    Posts
    18
    Consulting request for off the shelf code? Seriously. This forum used to be the place to go where the users helped each other. When did this go corporate? I'm not an idiot, i know the level of effort for this, i just don't know the language. I'm certain i'm not the first person to need delimited fields split into rows.

  7. #7
    Join Date
    Sep 2003
    Posts
    18
    I hadn't thought about the queries. I may give that a try since i am having no luck to date with help on the VBA. It might be ugly, i'll have to read up on that to see if it's possible. Thanks.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •