Help with Gas Chromatography Data Processing

[NCase]

Hello Everyone,

In Dec 2018, Chihiro helped me process data from a Gas Chromatography Mass Spectrometer instrument (https://chandoo.org/forum/threads/h...omatography-instrument-data.40450/post-242073)

I was hoping someone could find the time to help me again as we have upgraded our software and your code needs to be edited to help process the new data and then apply some calculations to process the data into a final summary page.

I am including examples of the new results files and an excel spreadsheet of how I want the data to look.

Best,

Francis

 

[Chihiro]

Is number of rows fixed above the Header? i.e. Is it always 7 rows above header?

 

[NCase]

Correct,

I believe this is the default format but could you use the [contents] in row 1 as start and Time= in row 7 as end of header justin case it adds any additional info into the header.

Best,

Francis

Is number of rows fixed above the Header? i.e. Is it always 7 rows above header?

 

[Chihiro]

Haven't fully tested, but try... (replace old ImportData code with below).

Sub ImportData(iFile As String)
Dim intFF As Integer: intFF = FreeFile()
Dim strContent As String
Dim x, y, lRow As Integer

lRow = Cells(Rows.Count, "A").End(xlUp).Row

Open iFile For Input As #intFF
strContent = Input(LOF(intFF), intFF)
Close #intFF

x = Split(strContent, "[PBM Apex]")

y = Split(x(0), Chr(10))
Cells(lRow + 1, "A").Resize(UBound(y)) = Application.Transpose(y)

y = Split("[PBM Apex]" & x(1), Chr(10))
Cells(lRow + 1, "L").Offset(5).Resize(UBound(y)) = Application.Transpose(y)

End Sub

 

[NCase]

Chihiro,

I had some errors at the start but I was able to do slight edits and troubleshoot the code. The only problem is that end of spreadsheet repeats line 274 on the row 275 with out being column to text separated.

I need need to hide columns D-H, L-N and P because it contains redundant information from each file. I have included an example of the type of calculations I am then trying to apply the newly organized data.






See edited code below.
---------------------------------------------

Sub ImportData(iFile As String)

Dim intFF As Integer: intFF = FreeFile()

Dim strContent As String

Dim x, y, lRow As Integer



lRow = Cells(Rows.Count, "A").End(xlUp).Row



Open iFile For Input As #intFF

strContent = Input(LOF(intFF), intFF)

Close #intFF



x = Split(strContent, "[PBM Apex]")



y = Split(x(0), Chr(10))

Cells(lRow + 1, "A").Resize(UBound(y)) = Application.Transpose(y)



y = Split("[PBM Apex]" & x(1), Chr(10))

Cells(lRow + 1, "L").Offset(4).Resize(UBound(y)) = Application.Transpose(y)



End Sub



Sub CheckAllSubFold()

Dim path As String: path = ThisWorkbook.path & "\"

Dim fName As String: fName = "RESULTS.CSV"

Dim cPath As String, coll As New Collection

cPath = Dir(path, vbDirectory)

Do While Len(cPath) > 0

    If Left(cPath, 1) <> "." And _

        (GetAttr(path & cPath) And vbDirectory) = vbDirectory Then

        coll.Add path & cPath & "\"

    End If

    cPath = Dir()

Loop

For I = 1 To coll.Count

    fName = Dir(coll.Item(I) & "RESULTS.csv")

    ImportData coll.Item(I) & fName

    fName = Dir()

Next

Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True

Range("L:L").TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True

Range("L:L").Delete

End Sub

 

[Chihiro]

To get data that match your sample something like...

Sub ImportData(iFile As String)
Dim intFF As Integer: intFF = FreeFile()
Dim strContent As String
Dim x, y, lRow As Integer

lRow = Cells(Rows.Count, "A").End(xlUp).Row

Open iFile For Input As #intFF
strContent = Input(LOF(intFF), intFF)
Close #intFF

x = Split(strContent, "[PBM Apex]")

y = Split(x(0), Chr(10))
Cells(lRow + 1, "A").Resize(UBound(y)) = Application.Transpose(y)

y = Split("[PBM Apex]" & x(1), Chr(10))
Cells(lRow + 1, "L").Offset(5).Resize(UBound(y)) = Application.Transpose(y)

End Sub

Sub CheckAllSubFold()
Dim path As String: path = ThisWorkbook.path & "\"
Dim fName As String: fName = "RESULTS.CSV"
Dim cPath As String, coll As New Collection
cPath = Dir(path, vbDirectory)
Do While Len(cPath) > 0
    If Left(cPath, 1) <> "." And _
        (GetAttr(path & cPath) And vbDirectory) = vbDirectory Then
        coll.Add path & cPath & "\"
    End If
    cPath = Dir()
Loop
For I = 1 To coll.Count
    fName = Dir(coll.Item(I) & "RESULTS.csv")
    ImportData coll.Item(I) & fName
    fName = Dir()
Next
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True
Range("L:L").TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True
Range("1:1").Delete
Range("L:L").Delete
Union(Range("D:H"), Range("L:N"), Range("P:P")).Columns.Hidden = True
End Sub

As for your calculation. With how data is organized currently, it would be bit difficult to code it in.
Also, I'm not sure why some fields are not calculated, but others are...
Ex: Why are U11 & V11 calculated but not other columns for Row 11?

 

[NCase]

Chihiro,

Sorry the confusion. The compounds colored are a calibration standard and we dont need calculate those results. The standards provide the signal/concentration response that is then used to calculate the results for the unknown compound concentrations in each separate file in the spreadsheet.

I was manually doing this and only had time to calculate the results for the first sample.

Best,

Francis

 

[NCase]

The 2nd code does a great job of organizing data and hiding the columns but it makes the header info split into separate columns. See effect below.

[contents]
count=2
Name=	C:\MassHunter\GCMS\1\data\2019-08-15_1317_Sample Test\Blank 1.D
1=	INT TIC: Blank 1.D\data.ms
2=	PBM Apex

[contents]
count=2
Name=	C:	MassHunter
1=	INT TIC: Blank 1.D	data.ms
2=	PBM Apex
[INT TIC: Blank 1.D	data.ms]
Time=	Fri Aug 23 13:03:11 2019

 

[Chihiro]

Huh? Code has no part that would separate based on "\". Unless you have set it on your Excel.

If you need to ensure it only uses comma... make sure other separator arguments are set to False.
https://docs.microsoft.com/en-us/office/vba/api/excel.range.texttocolumns

 

[NCase]

Strange, once i restarted excel. its works perfectly.

 

[NCase]

Chihiro,

I am hoping you might be of assistance again in my Gas Chromatography data processing needs. The company Agilent has upgraded the software and it now provides data in a different format.

I would like to batch process the .csv text files in a folder by merging all the results together into one excel file and complete some calculations on each data file (see attached excel file with an example of how I need to process the data). The calculations that I added are highlighted in yellow.

Best,

Francis

 

[Chihiro]

I'm bit busy this afternoon and over the weekend. Will see if I have time, but likely won't have time to review till next week.