Import data from .txt to excel

[hunna]

Hello, if it's possible to import data from notepad file as shown below to excel worksheet

###FS:    #m/z: 471.93765 #charge   3
###MaxRes:#m/z:  0.00000 #charge-128
###MS: 401/
###MSMS: 400/
BEGIN IONS
TITLE= Cmpd 1, +MSn(qCID 471.93765), 10.5 min
PEPMASS=471.937648    52884640
CHARGE=3+
471.241338    1994913    1+
1043.610977    869664    1+
1243.691115    1882480    1+
1300.713939    3368120    1+
1413.796382    2822475    1+
END IONS

###FS:    #m/z: 784.05656 #charge   3
###MaxRes:#m/z:  0.00000 #charge-128
###MS: 402/405/
###MSMS: 403/407/
BEGIN IONS
TITLE= Cmpd 2, +MSn(qCID 784.05658), 10.6 min
PEPMASS=784.056557    52754752
CHARGE=3+
832.440915    316937    1+
2021.019465    394193    1+
2333.134355    291084    1+
2350.152226    260149    1+
END IONS

###FS:    #m/z: 601.77318 #charge   4
###MaxRes:#m/z:  0.00000 #charge-128
###MS: 405/
###MSMS: 406/408/
BEGIN IONS
TITLE= Cmpd 3, +MSn(qCID 601.77319), 10.7 min
PEPMASS=601.773181    19070720
CHARGE=4+
646.341532    898599    1+
2404.067064    365217    1+
END IONS

In excel worksheet (column A is m/z and D is charge number)

<table height="323" width="288" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:54pt" span="4" width="72"> </colgroup><tbody><tr style="height:14.25pt" height="19"> <td style="height:14.25pt;width:54pt" height="19" width="72" align="center">A</td> <td valign="top" align="center">
</td><td style="width:54pt" width="72" align="center">B
</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td style="width:54pt" width="72" align="center">C
</td> <td style="width:54pt" width="72" align="center">D</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">471.9377</td> <td valign="top" align="center">
</td><td align="center">0</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">0</td> <td align="center">3</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">471.2413</td> <td valign="top" align="center">
</td><td align="center">1994913</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">1+</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">1043.611</td> <td valign="top" align="center">
</td><td align="center">869664</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">1+</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">1243.691</td> <td valign="top" align="center">
</td><td align="center">1882480</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">1+</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">1300.714</td> <td valign="top" align="center">
</td><td align="center">3368120</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">1+</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">1413.796</td> <td valign="top" align="center">
</td><td align="center">2822475</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">1+</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">
</td> <td valign="top" align="center">
</td><td align="center">
</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">
</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">784.0566</td> <td valign="top" align="center">
</td><td align="center">0</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">0</td> <td align="center">3</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">832.4409</td> <td valign="top" align="center">
</td><td align="center">316937</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">1+</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">2021.019</td> <td valign="top" align="center">
</td><td align="center">394193</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">1+</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">2333.134</td> <td valign="top" align="center">
</td><td align="center">291084</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">1+</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">2350.152</td> <td valign="top" align="center">
</td><td align="center">260149</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">1+</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">
</td> <td valign="top" align="center">
</td><td align="center">
</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">
</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">601.7732</td> <td valign="top" align="center">
</td><td align="center">0</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">0</td> <td align="center">4</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">646.3415</td> <td valign="top" align="center">
</td><td align="center">898599</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">1+</td> <td align="center">
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="center">2404.067</td> <td valign="top" align="center">
</td><td align="center">365217</td> <td valign="top" align="center">
</td><td valign="top" align="center">
</td><td align="center">1+</td> <td align="center">
</td> </tr> </tbody></table>

Any help would be appreciated

,

 

[montecarlo2079]

You have to see how the form is delimited and change that in the code.



Also change the directory.

when the box comes up asking for a delimiter, just hit the spacebar and "ok"

 Option Explicit[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]Sub OpenTextFiles()[/FONT]
[FONT=Calibri]Dim strFiles() As String[/FONT]
[FONT=Calibri]Dim strFName As String[/FONT]
[FONT=Calibri]Dim strFPath As String[/FONT]
[FONT=Calibri]Dim IntFile As Integer[/FONT]
[FONT=Calibri]Dim Sep As String[/FONT]
[FONT=Calibri]'define the directory[/FONT]
[FONT=Calibri]strFPath = "C:\USERS\"[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]'build a list of files[/FONT]
[FONT=Calibri]strFName = Dir(strFPath & "*.*")[/FONT]
[FONT=Calibri]While strFName <> ""[/FONT]
[FONT=Calibri]IntFile = IntFile + 1[/FONT]
[FONT=Calibri]ReDim Preserve strFiles(1 To IntFile)[/FONT]
[FONT=Calibri]strFiles(IntFile) = strFName[/FONT]
[FONT=Calibri]strFName = Dir()[/FONT]
[FONT=Calibri]Wend[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]'see if any files were found[/FONT]
[FONT=Calibri]If IntFile = 0 Then[/FONT]
[FONT=Calibri]MsgBox "No files found"[/FONT]
[FONT=Calibri]Exit Sub[/FONT]
[FONT=Calibri]End If[/FONT]
[FONT=Calibri]Sep = InputBox("Enter a single delimiter character.", _[/FONT]
[FONT=Calibri]"Import Text File")[/FONT]
[FONT=Calibri]'cycle through the list and import[/FONT]
[FONT=Calibri]For IntFile = 1 To UBound(strFiles)[/FONT]
[FONT=Calibri]ImportTextFile strFPath & CStr(strFiles(IntFile)), Sep[/FONT]
[FONT=Calibri]Next[/FONT]
[FONT=Calibri]End Sub[/FONT]
[FONT=Calibri]Public Sub ImportTextFile(FName As String, Sep As String)[/FONT]
[FONT=Calibri]  Dim StartAt As Long, ColNdx As Long, FileNum As Long, TotalFile As String, Lines() As String[/FONT]
[FONT=Calibri]  ColNdx = 1[/FONT]
[FONT=Calibri]  StartAt = Cells(Rows.Count, ColNdx).End(xlUp).Row + 1[/FONT]
[FONT=Calibri]  FileNum = FreeFile[/FONT]
[FONT=Calibri]  Open FName For Binary As #FileNum[/FONT]
[FONT=Calibri]    TotalFile = Space(LOF(FileNum))[/FONT]
[FONT=Calibri]    Get #FileNum, , TotalFile[/FONT]
[FONT=Calibri]  Close #FileNum[/FONT]
[FONT=Calibri]  Lines = Split(Replace(TotalFile, vbNewLine, Sep & Sep), Sep)[/FONT]
[FONT=Calibri]  Cells(StartAt, ColNdx).Resize(UBound(Lines) + 1).Value = WorksheetFunction.Transpose(Lines)[/FONT]
[FONT=Calibri]End Sub[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]

 

[obiron]

That will simply take the file and split the lines by the delimiter. I think the OP wanted to import the file and ignore the group headers and trailers.

If you look at the desired output you will see that the OP has rounded the first values to 3dp.

Splitting the data is a good start but I think we need to also show how to clean up the file after. I am just leaving work so won't have time to do it today.

@hunna

Yes it is possible. The script below will get the data into Excel (assuming the file is no more than 65500 rows if in XL2003 and 1.4M rows in 2007/2010. You need to proovide us with more information about what data needs to be kept from each line / derived from a previous line (like the charge number)

 

[hunna]

Thank you very much, montecarlo2079 and obiron for your answers.

@ obiron
In this picture http://www.mediafire.com/?1a08ufxepcexq6j is the data that I want to add.

this is the txt file http://www.mediafire.com/?36h714xne1ruyo1,7lv22eu5o8ire0r




Thanks for your kind help

 

[hunna]

@ montecarlo2079

Thank you for the code but it's not yet the one i am looking for. I need to remove and keep just some part of the data.