Specific text to excel conversion in a desired format

Amit1

New Member
Joined
Jul 6, 2012
Messages
23
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:

SerialQuery startQuery EndTarget startTarget EndScoreEPGC
>gi|1234|ref|</SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=6></COLGROUP>
165167717338.380.65290.00015146
>gi|54367|ref|</SPAN>

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

<TBODY>
</TBODY>


Looking forward for any kind of help in this regard.

Best regards,
Amit
</SPAN>

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

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,976
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 6, 2012
Messages
23
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top