PDA

View Full Version : Read multiple .txt files and copy specific text to excel



symplystyc
10-10-2017, 11:29 AM
Hello,
I have thousands of text files that I need to copy specific data from and put into columns in an excel spreadsheet. In the past I've used a variation of the code below and it has worked fine however the text files I now have have duplicate words that I need to search for. Sample Analysis Time/Date: Benezen etc.. What I would like to do is return a value for each searched term in a corresponding column.

current code


Row = 2
'label columns
ActiveSheet.Cells(Row, 1).Value = "Analysis Date/Time"
ActiveSheet.Cells(Row, 2).Value = "Lims#"
ActiveSheet.Cells(Row, 3).Value = "Benzene"
ActiveSheet.Cells(Row, 4).Value = "Arom"
ActiveSheet.Cells(Row, 5).Value = "RVP"
ActiveSheet.Cells(Row, 6).Value = "Ron"
ActiveSheet.Cells(Row, 7).Value = "Mon"
ActiveSheet.Cells(Row, 8).Value = "R+M/2"
ActiveSheet.Cells(Row, 9).Value = "T10"
ActiveSheet.Cells(Row, 10).Value = "T50"
ActiveSheet.Cells(Row, 11).Value = "T90"


Set fso = CreateObject("Scripting.FileSystemObject")
For Each f In fso.GetFolder("C:\Desktop\NIR\temp").Files
If LCase(fso.GetExtensionName(f.Name)) = "txt" Then
Row = Row + 1
Set stream = f.OpenAsTextStream
Do Until stream.AtEndOfStream
Line = stream.ReadLine

'search line for text, return value to right of text
If Left(Line, 32) = " Sample Analysis Time/Date:" Then
ActiveSheet.Cells(Row, 1).Value = Right(Line, Len(Line) - 39)
ElseIf Left(Line, 13) = " Comment" Then
ActiveSheet.Cells(Row, 2).Value = Right(Line, Len(Line) - 45)
ElseIf Left(Line, 13) = " Benzene" Then
ActiveSheet.Cells(Row, 3).Value = Right(Line, Len(Line) - 39)
'continue on for each search term


Exit Do
End If
Loop
stream.Close
End If
Next


Text file (could not upload this for some reason)

Analyzer Description, Configuration 1 Configuration Name CONFIGURATION 1
Model MB160D
Serial Number SZM48067
Sampling Accessory Liquid Cell
Sampling Device Serial Number NONE
Detector InAs
Detector Serial Number SZM480PK
####1
Analysis Results
.
Sample Analysis Time/Date: 7:31 AM 1/1/14
Spectrum: FAG35315
Procedure: Reg 2009
Comment LIMS# 0939
Benzene (wt%) .61
Arom. (GCMS vol.%) 16.44
RVP (psi) 8.88
RON (Regular) 91.13
MON (Regular) 83.76
(R+M)/2 (Regular)200 87.32
T10 (F) 130.83
T50 (F) 213.62
T90 (F) 327.52
End Point 415.11
. Total Samples: 1
.0 0 0 1 3825 1.00E+04 C:\AIRS\all\c2014_01\samples\FAg35315
Sample Analysis Time/Date: 7:35 AM 1/1/14
Spectrum: FAG35316
Procedure: Reg 2009
Comment LIMS# 061
Benzene (wt%) .84
Arom. (GCMS vol.%) 15.17
RVP (psi) 14.74
RON (Regular) 88.59
MON (Regular) 80.41
(R+M)/2 (Regular)200 84.63
T10 (F) 104.00
T50 (F) 173.95
T90 (F) 301.64
End Point 403.94
. Total Samples: 2
.0 0 0 1 3825 1.00E+04 C:\AIRS\all\c2014_01\samples\FAg35316



Hoped for end result


Analysis Date/Time
Lims#
Benzene
Arom
RVP
Ron
Mon
R+M/2
T10
T50
T90


1/1/2014 7:31
939
0.61
16.44
8.88
91.13
83.76
87.32
130.83
213.62
327.52


1/1/2014 7:35
061
0.84
15.17
14.74
88.59
80.41
84.63
104
173.95
301.64

SamT
10-11-2017, 11:22 AM
bump