Specific text to excel conversion in a desired format

[Amit1]

Dear Friends,
I have a specific problem where the .txt file is as provided in the input file.txt and would like to have an VB excel script which should provide the output as provided in the desired output.
I have to perform this for more than 1000 .txt files. Any kind of help is appreciated.

Input format in .txt file:
>gi|1234|ref|

Query = 1 - 65, Target = 1677 - 1733
Score = 8.38, E = 0.6529, P = 0.0001513, GC = 46
fd sdfsdfsdfsdf
fsdfdsfdfdfdfdfdf
dfdfdfdfedfsdfsdfdsf
dfdfdfsdfsdfsdfsdfdsf
sdfdsfdfdfdfdsfdfdfdfd

>gi|54367|ref|

fd sdfsdfsdfsdf
fsdfdsfdfdfdfdfdf
dfdfdfdfedfsdfsdfdsf
dfdfdfsdfsdfsdfsdfdsf
sdfdsfdfdfdfdsfdfdfdfd

Query = 1 - 65, Target = 6780 - 6812
Score = 8.13, E = 0.7692, P = 0.0001782, GC = 36

................................EOF................................
where as the output should be like the following table format:

Serial	Query start	Query End	Target start	Target End	Score	E	P	GC
>gi|1234|ref|</SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> </SPAN> <TBODY> </TBODY><COLGROUP><COL><COL><COL><COL span=6></COLGROUP>	1	65	1677	1733	8.38	0.6529	0.000151	46
>gi|54367|ref|</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	1	65	6780	6812	8.13	0.7692	0.000178	36

<TBODY>
</TBODY>

Looking forward for any kind of help in this regard.

Best regards,
Amit

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Rick Rothstein]

Some questions...

1) Are the 1000 text files all in the same directory?

2) If the answer to #1 above is "yes", then what is the path to that directory?

3) Again, if the answer to #1 above is "yes", then will there be any other text files in that directory?

4) Should the output for the 1000 text files all be placed in the same table, one file's worth of data under the other?

5) You show the "P" value in the table as being one digit shorter than in the file... is that digit removed by truncating or rounding?

 

[Amit1]

Dear Sir,
Thanks for your kind response. The answers of your questions are as follows:
1.No, all the 1000 files will be in 7 different directory.
2. The path is C:\Documents and Settings\AMIT\Desktop\AB\(different subdirectories with respective files).
3. Other text files are there which can be removed
4. Different tables (7) will be generated.
5.Yeah, it has been removed due to the truncation.

N.B: The answer could be yes for your first question where i can do all importing in a single Excel table. Only i have to do it 7 times in that case for 7 different directories, that will also ease my task profoundly.
looking forward for your support in this regard.

Thanks and Best regards,
Amit
Amit