Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a text file (see attached) that I need to convert to a meaningful table in Access 2003. Before I accept the project, I would like to know if it is possible to loop through the imported table and get the data into a format that users can read.
    Instead of all data in one column, I need the data to be shown in multiple fields and records.
    I am hoping somebody can point me in the right direction.
    Thanks a lot

    [attachment=87161:LQuery.txt]
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Most of the text file has a consistent structure:
    - If the first two or three characters on a line aren't spaces, they indicate a field name.
    - Text from position 5 on the line forms the field value, together with the text on subsequent lines that start with spaces.
    - A line that only consists of three spaces indicates that the next line starts a new record.
    It's possible to write code to convert this to a table with fields L22, AN, TI, IN etc.

    But there are lines with a completely different structure, such as

    Code:
    FAN.CNT 1
         PATENT NO.          KIND   DATE        APPLICATION NO.        DATE
         ---------------     ----   --------    --------------------   --------
    PI   US 7594994           B1    20090929    US 2005-244589         20051006
    PRAI US 2005-244589             20051006     
    ASSIGNMENT HISTORY FOR US PATENT AVAILABLE IN LSUS DISPLAY FORMAT
    How should these be converted?

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Most of the text file has a consistent structure:
    - If the first two or three characters on a line aren't spaces, they indicate a field name.
    - Text from position 5 on the line forms the field value, together with the text on subsequent lines that start with spaces.
    - A line that only consists of three spaces indicates that the next line starts a new record.
    It's possible to write code to convert this to a table with fields L22, AN, TI, IN etc.

    But there are lines with a completely different structure, such as

    Code:
    FAN.CNT 1
         PATENT NO.          KIND   DATE        APPLICATION NO.        DATE
         ---------------     ----   --------    --------------------   --------
    PI   US 7594994           B1    20090929    US 2005-244589         20051006
    PRAI US 2005-244589             20051006     
    ASSIGNMENT HISTORY FOR US PATENT AVAILABLE IN LSUS DISPLAY FORMAT
    How should these be converted?
    Thanks, Hans
    I am not sure where to go from here, but have some ideas after reading your reply. I will play with this and see if I have any more questions.

Posting Permissions

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