Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract data from a text file (2002/SP3)

    I have a text file that I'd like to extract information from. The text file is organized in a certain way but is not built in such a way where I can import the text into Excel using one of its delimiters. The text is sturctured in the following way:

    .....some text....
    Sumbodel Name: CORE1
    .....some text....
    T 1= ##.### T 2= ##.### T 3= ##.### T 4= ...
    .....some text....
    Sumbodel Name: CORE2
    .....some text....
    T 1= ##.### T 2= ##.### T 3= ##.### T 4= ...
    .....some text....
    **
    ** Format repeated till the end of file

    I've attached a copy of this file.

    Is there a way to create a VBA to read each line from the text file and search for two occurances:
    1) Search for the string "CORE1"
    2) Copy this string to cell A2
    3) Search for the string "T 2="
    4) Copy the "##.###" value next to it to cell B2
    5) Repeat steps 1 to 4 till the end of file

    I have hundreds of such files, each with over 100,000 lines. That's why a extracting data from such files would be more efficient than importing each file directly to Excel and munipulating the data there.

    Any help would be great.
    Attached Files Attached Files

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Extract data from a text file (2002/SP3)

    Yes it is possible to do. I've done something similar myself. What is critical is that all files have a similar layout.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract data from a text file (2002/SP3)

    Welcome to Woody's Lounge!

    I have attached a text file with a macro that will loop through all text (.txt) files within a folder and extract the information that you want into the active sheet.

    Notes:
    - The text file you attached has a different structure than what you describe in your post. I originally posted code based on your description, I have since modified it to match the attached file.
    - You must change the constant strFolder to the path of the folder with the text files; the value must end in a backslash .
    - The macro will clear the active worksheet except for row 2, so be careful.
    Attached Files Attached Files

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Extract data from a text file (2002/SP3)

    arrrgh! not fast enough again. This darn work keeps interfering with my lounging!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract data from a text file (2002/SP3)

    Thanks for the quick reply Hans. Very kind of you.

    The code is a bit confusing to me. Can you take a minute to explain the code, specifically the If/End If condition?

  6. #6
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract data from a text file (2002/SP3)

    Thank you as well Catharine :-)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract data from a text file (2002/SP3)

    The code uses a Boolean variable blnVal to keep track of whether it's looking for a name or for a value. This variable starts out False to indicate that we're looking for a name.
    Initially, the first If is skipped because blnVal is False, so the code enters the Else part.
    IntPos is used to determine whether the line from the file contains the text SUBMODEL NAME. If so, the part after this text is extracted using the Mid function, leading and trailing spaces are trimmed, and the resulting name is placed in column A. blnVal is set to True to indicate that we're now looking for a value.
    For the next line, the code enters the If part. IntPos is used to determine whether the line contains T 2= (with the correct number of spaces in between), and if so, the position of T 3= is also determined. Mid and Trim extract the text in between, CDbl converts to a Double precision number and this is placed in column B.
    If neither condition is true, the code skips to the next line in the file.

  8. #8
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract data from a text file (2002/SP3)

    Thanks, it makes more sense.

    One more question. For the following text:

    .....some text....
    Sumbodel Name: CORE1
    .....some text....
    T 1= 20.000 T 2= 22.000 T 3= 21.000 T 4=
    .....some text....
    Sumbodel Name: CORE2
    .....some text....
    T 1= 25.000 T 2= 27.000 T 3= 26.000 T 4=
    .....some text....
    Sumbodel Name: CORE1
    .....some text....
    T 1= 40.000 T 2= 42.000 T 3= 41.000 T 4=
    .....some text....
    Sumbodel Name: CORE2
    .....some text....
    T 1= 10.000 T 2= 12.000 T 3= 11.000 T 4=
    .....some text....

    The program outputs this:

    CORE1 22
    CORE2 27
    CORE1 42
    CORE2 12

    Is there a way to just output CORE1 rather than CORE2?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract data from a text file (2002/SP3)

    You could check for the exact line SUBMODEL NAME = CORE1 if you're on;y interested in CORE1.

Posting Permissions

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