Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: Text to columns in ACCESS

    What is the best way to extract information from these records:

    Cust ID w/Name
    5226445~SALVATION ARMY YOUTH CENTER~
    5226445~SALVATION ARMY YOUTH CENTER~
    7124954~VS/ULTIMATE ELECTRONICS~
    7006176~CANTEEN/PADDOCK POOLS~
    7122175~CANTEEN/ARIZONA CHILDRENS~
    5217006~CANTEEN/US WEST~
    438606~SAFEWAY 0240~0240


    This is one field in Access that was imported from a text file. Is there a way to separate the CUST ID from the NAME and have them be in two separate fields?

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The easiest way (if you still have the original text file) is to treat it like a delimited file with the ~ as the delimiter. The trick with using a non standard delimiter (usually comma or tab are used) you have to create and save a File Specification.

    To do this choose File/Get External Data/Imoort. Then select the text file and when the Import Text Wizard appears hit the Advance button. The screen that is displayed will allow you to put in ~ as the delimiter and then give the columns names. Save the file spec and finish importing.

    Another way to do it is using the Split function in code.

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    String functions

    If the the id is always 7 characters you can

    left(myfield,7)

    which in essence would do this
    left("5226445~SALVATION ARMY YOUTH CENTER",7)
    and return 5226445
    Dale Houston, TX

  4. #4
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    thanks for all of the replies...

    The length is not always 7 characters so the LEFT() function will not always work.


    The Import doesn't work because there are 9 columns that get imported from the text file. They are all separated by a TAB. I can import the file using the ~ as a delimiter but it will mess up all of the other columns.

    Tell me more about the split function...

Posting Permissions

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