PDA

View Full Version : macro to import data from text file to excel worksheet



hunna
11-16-2011, 01:44 AM
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 I want to keep only the red part (column A is m/z and D is charge number)


A B C D
471.9377 0 0 3
471.2413 1994913 1+
1043.611 869664 1+
1243.691 1882480 1+
1300.714 3368120 1+
1413.796 2822475 1+

784.0566 0 0 3
832.4409 316937 1+
2021.019 394193 1+
2333.134 291084 1+
2350.152 260149 1+

601.7732 0 0 4
646.3415 898599 1+
2404.067 365217 1+

I have a lot of data so I need macro to automate this work.

Any help would be appreciated.

mohanvijay
11-16-2011, 06:40 AM
try this




Dim O_Fso As Object
Dim Txt_File As Object
Dim File_Path As String
Dim T_Str As String, Str_Ma4 As String
Dim T_Bool As Boolean
Dim Hld_Data As Variant
Dim Ma_H_Boo As Boolean
Dim Ctr As Long
Dim i As Long
Const LL_Cha As String = "CHARGE="
Const LL_End As String = "END IONS"
Const LL_Pep As String = "PEPMASS="
T_Bool = False
Ma_H_Boo = False
Ctr = 0
i = 1
File_Path = "C:\Documents and Settings\User\Desktop\VBAA\tt.txt" 'change to your text file path

Set O_Fso = CreateObject("Scripting.FileSystemObject")
Set Txt_File = O_Fso.OpenTextFile(File_Path)
With Txt_File
Do Until .AtEndOfStream = True
T_Str = .ReadLine

If T_Str = LL_End Then
T_Bool = False
Ctr = 0
End If

If InStr(T_Str, LL_Pep) <> 0 Then Ma_H_Boo = True

If Ma_H_Boo Then
Hld_Data = Split(T_Str, Chr$(9))
i = i + 1
Cells(i, 1).Value = Replace(CStr(Hld_Data(0)), LL_Pep, "")
Cells(i, 2).Value = 0
Cells(i, 3).Value = 0
i = i + 1
Ma_H_Boo = False
End If

If T_Bool Then
Ctr = Ctr + 1
Hld_Data = Split(T_Str, Chr$(9))

Cells(i, 1).Value = Hld_Data(0)
Cells(i, 2).Value = Hld_Data(1)
Cells(i, 3).Value = Hld_Data(2)

If Ctr = 1 Then
Cells(i - 1, 4).Value = Str_Ma4
End If
i = i + 1



End If

If InStr(T_Str, LL_Cha) <> 0 Then
T_Bool = True
Hld_Data = Split(T_Str, "=")
Str_Ma4 = CStr(Hld_Data(1))
Str_Ma4 = Replace(Str_Ma4, "+", "")
End If

Loop
End With
Txt_File.Close
Set Txt_File = Nothing
Set O_Fso = Nothing

hunna
11-16-2011, 08:30 AM
@ mohanvijay

omg, thank you very much !!!! your code works exactly what I want. ;)

hunna
11-16-2011, 09:53 AM
if I need to change the range(column) that I want to add data, how can I change it?

Thanks, :)

mohanvijay
11-16-2011, 08:23 PM
change the following code




Cells(i, Change column number that you want ).Value

hunna
11-18-2011, 12:45 AM
Thank you again!!! :cloud9: