Excel Macro to Import Specific Data from Txt File

[Niklasfirst]

Dear all, maybe somebody has a way for this, it would help me so much! I calculated several data sets and saved them as .mol2 files, which are basically .txt files. Now I want to import certain lines of the files into Excel.

The file looks like this:

# File written by LEADIT 2.1.2 (12.06.12)
# Input File / No. : Amantadine_001 0
# Sybyl molecule name: Amantadine_001

@<TRIPOS>MOLECULE Amantadine_001 29 31 SMALL FORMAL_CHARGES
@<TRIPOS>ATOM 1 N1 68.3130 47.3340 37.6690 N.4 1 lig 1.000 [...] 29 H29 68.6150 47.4380 38.6170 H 1 lig 0.000 @<TRIPOS>BOND 1 1 2 1 2 1 27 1 [...] 31 1 29 1

#
#<BIOSOLVEIT.HYDE_SCORE kJ/mol>
#-11
#
#<BIOSOLVEIT.HYDE_LIGAND_EFFICIENCY kcal/mol>
#0.24
#
#<BIOSOLVEIT.HYDE_ATOM_SCORES>
# 1; -3.8; 12.7; -13.4; 13.2; -16.3
# 2; 0.0; 0.0; 0.0; 0.0; 0.0
# 3; 4.0; -0.7; 0.0; 4.8; 0.0
# 4; 0.0; 0.0; 0.0; 0.0; 0.0
# 5; -0.9; -0.4; 0.0; -0.6; 0.0
# 6; -1.9; -1.1; 0.0; -0.8; 0.0
# 7; -3.6; -1.5; 0.0; -2.1; 0.0
# 8; -2.9; -1.3; 0.0; -1.6; 0.0
# 9; -0.2; -0.2; 0.0; 0.0; 0.0
#10; -1.5; -0.8; 0.0; -0.6; 0.0
#11; -0.2; -0.2; 0.0; 0.0; 0.0
#
#

In an optimal case the macro would only extract data with the # in front using a ; a delimiter. For the table <BIOSOLVEIT.HYDE_ATOM_SCORES> it would sum the 2nd column. Afterwards a new table would be created with:
The # Input File / No. : Amantadine_001 0 (Which would be in best case just Amantadine) \ The sum of the Atom Scores \ The value of <BIOSOLVEIT.HYDE_LIGAND_EFFICIENCY kcal/mol>

So the table would look like:

Amantadine 11.7 0.24

Finally I need to select nine different files, at best at the same time, and the second file for example is written in a new row like this:

Amantadine 11.7 0.24
Rimantadine 13.5 0.35

Is this possible? I played a little bit around with the macros but finally could not manage to do it. Maybe it would be easier to first import everything and then delete the not need parts? I also have a table with the correct names in an alphabetic order for each set with additional values, maybe one could use these?

I have so many data sets and my Prof asked me to analyse them till end of the week. And I just realized that my manually created table contains an error! I appreciate any help, thanks so much!

[JBeaucaire]

This seems pretty straightforward, except when I sum the values in the "second column" of the table, I get -11, not 11.7.
http://screencast.com/t/7VoGaj6piTnk

If it's not -11, where did you get 11.7 from?


It would be great if you attached a zip file with several of these .mol2 files so I can test with the real thing. The more variety in those few files, the better.

[Niklasfirst]

Hi,

thanks so much for the fast reply!

Yes, you are right, the value is -11.0. In general, the values are calculated to one decimal i.e. -11.7, but the program onlygives you i.e. -11 (see row above). So calculating it this way to get the decimal is crucial for me.

I attached a set of files which would be one table. Thanks so much!


This is the excel tables I already created with different values from another calculation. If the values which are extracted from the files here are not automatically added, it is now problem, then I will do it by hand.

Run: 0ns_Trp21D_Top1

Drug Lead Score
Amantadane -8.1147
Amiloride -24.1803
Benzamil -27.9121
BITT-225 -28.3964
DMA -23.9115
EIPA -21.1288
HMA -24.2934
NNDNJ -8.1795
Rimantadine -8.7693

I'm so grateful for the help, probably will save me two weeks of opening text files!

[Niklasfirst]

Any Idea? I#m very excited for your solution!

[JBeaucaire]

Sorry, I didn't notice your prior response. Sure, I'll work on this a bit today.

[JBeaucaire]

Whew, a bit of a bugger. Those files are an interesting format. Edit the fPATH to the folder where you store your MOL2 files.

Please Login or Register  to view this content.

[Niklasfirst]

Wow, thank you so much! This is really amazing. Thanks for your help!

People like you make the internet work! I will try to share my knowledge in a same way! The files are interesting, this happens when several different programms from different projects are pressed into one suite. It is for calculating binding affinities of drugs to computer model of proteins.

I analysed now my data and will continue with another set. I will maybe have a similar problem soon, but I want to learn and solve it. I understand the script, can you just tell me: how did you select the values to be written in the table? Is there a tutorial for this? (I know a little bit programming in Pyhton, i.e., If, Elif and so on.)

Thanks

[JBeaucaire]

No, no tutorial I can recommend, trial and error is how I figure out how to manipulate any one set of unique data.

Your file is "one long line of text", not individual rows like you'd think.
So I read in the file, then split the lines of text in the string into separate fields splitting by the carriage return in the file.
Then I pull the name of the drug from the last word in the 3rd line, strip out anything after an "_" character.

Then I start reading each line looking for the words "HYDE_LIGAND_EFFICIENCY"... when found we write out the next row minus the "#" character.

Next, we continue searching til we find "HYDE_LIGAND_EFFICIENCY". Once found, we start looping through the data in the rows that follow. For each line, we check and make sure it's not a "short" string (in which case we're done), and if it's not, we split that row into an array splitting on the ";". You want the second value in each of those rows, so we "MySum" up the value in that position each time. When we finally encounter a "short" string, we write out the sum.

Lather... Rinse... Repeat.