Extract data from a text file (2002/SP3)

Topic

New Reply

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.

Reply | Quote

Replies

Yes it is possible to do. I’ve done something similar myself. What is critical is that all files have a similar layout.

Reply | Quote

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.

Reply | Quote

arrrgh! not fast enough again. This darn work keeps interfering with my lounging!

Reply | Quote

Thank you as well Catharine

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote