Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    2

    Unanswered: Extracting Text from Field

    I am importing a text file in which one field is actually a grouping of several "sub-fields". A sample field looks like this:

    {A1} BUNG {C1} ALUM {D1} CRAWL {E12} WOOD {I1} PW {J2} PRIV {L9} HWH {O2} NO {P1} YES {W1} ELB {W2} CLO *

    The formatting is always the same - the "sub-field" name is in curly braces followed by the value. The end of the record is denoted by an *. The different "sub-fields" may or may not appear in each record, and each record may have a different number of "sub-fields"

    Is there a way using Access or SQL to extract these "sub-fields" for a report? The records are actually homes for sale, and the "sub-fields" are feature such as {A1} is building type, such as Bungalow, Ranch, etc. I would like a report that allows me to print "Building Style: Bungalow" or whatever.

    Any help would be greatly appreciated.

    Thanks
    Tom

  2. #2
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    I think I would be tempted to import the file using code and to split the field up into its constituent parts on the import. That way I could create a true relational database. It would then be much easier to produce your reports.
    Roger Hampson
    XI - ecs (UK) Ltd

  3. #3
    Join Date
    Sep 2002
    Posts
    2
    Originally posted by xiecsuk
    I think I would be tempted to import the file using code and to split the field up into its constituent parts on the import. That way I could create a true relational database. It would then be much easier to produce your reports.
    Sounds good to me, but I've never tried that before. Can you point me in the right direction? I'm assuming this is something I really need to know VBA to do....

    Thanks,
    Tom

  4. #4
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    Sorry I have been so long replying.

    There are 2 ways you can go - the old fashioned way and the new way

    The Old Fashioned Way - Access 97, but it also works in 2000, 2002

    You need to look at the following commands

    Freefile, Open filename for Input as #filenum, Input #filenum, a, b, c
    Input (n, #filenum)

    Freefile is used to get a free filehandle which you then use in the other commands prefixing it with a # as above.

    The Open command will open the text file to allow you to read it. You could also open another with a different file handle to write to if you so wish

    Having opened the file, you use Input #filenum, a, b, c to read each comma separated field into the list of variables.

    This won't help you though; you need to use the other form Input(n, #filenum). This will read n characters at a time. You would read the file character by character checking each one and then storing the result. In other words you would look for a { and then read until you find the corresponding }. You would then store the sub-field.

    You need to handle , " Cr and Lf characters when you use this method as it reads all characters. Your program will therefore consist of a series of Do loops looking for CrLf to identify each record and , to identify each field and { } to identify your sub fields.

    If you read the help text you will get much more information.

    The New Way - 2000 and 2002

    You need to look at the following new objects

    FileSystemObject TextStreamObject

    and the various methods assocaited with them such as

    CreateTextFile Read ReadLine

    Again the help text is very good at explaining their use, but simply, after creating the textfile, Read will allow you to read character by character and ReadLine to read a complete line. There doesn't appear to be a way of reading a discrete number of fields this way.

    Personally, I would stick with the old way, but that is my preference. I should progress and use the new objects

    Sorry this has been so long, but I hope it helps you to progress.
    Roger Hampson
    XI - ecs (UK) Ltd

Posting Permissions

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