Reading daily text logs and importing numeric data to excel with VBA macro

[rg8588]

Hi all,

Every day I generate a text log from a software into a folder on my computer. Folder contains PDFs along with the text logs from each day. The text file name has the date and time stamp on it. The same date and time stamp is also contained within the first line of the file content. I've stuck a snippet of the file below this post. The sample text file can be downloaded from https://ufile.io/y9qur As you can see, there are comma delimiters in many places however they are quite consistent.

I'm basically looking to read the certain numeric values from the text file into excel matching a given date. Let me would breakdown what I want to do .

1) From a given excel spreadsheet, I would like to extract numeric data from a text files. I would like to scan through only text files and the condition must whether today's date in Cell A2 matches the date stamp in the text filename of all the filenames. Once condition is met, only that text file is read for reading data.

2) The numeric values I need are many, however I present a simple example. Please offer me guidance with extracting these values and I'll work off of this for other fields. The text file contains two samples for each attribute so it must correctly look for numeric data pertaining to Sample 1 and Sample 2.

Here is an "PRE" image of my sheet (pls click).



Once I click the command button, here are the POST results I'm looking for (pls click) :




Appreciate your kind support for a suitable VBA code for the command button.

HRV ANALYSIS RESULTS - 11-May-2018 14:48:59
Kubios HRV Standard
3.0.0
released in January 2017


Analyzed by: John Smith - - Athlete


File name: C:\Users\John.Smith\Downloads\rr-Smith_John_2018-05-11_04-18-38.txt
Measurement date: xx/xx/xx xx:xx:xx
File type: asciiRR
Channel label: RR data
Data length: 00:06:31 (h:min:s)
Measurement rate: -


Parameters
Number of samples: 2
Detrending method: Smoothn priors (lambda: 500)
Min/Max HR as average of: 5 beats
Threshold for NNxx/pNNxx: 50 ms
Frequency bands
VLF: 0 - 0.04 Hz
LF: 0.04 - 0.15 Hz
HF: 0.15 - 0.4 Hz
Interpolation rate: 4 Hz
Points in frequency-domain: 300 points/Hz
FFT spectrum options
Window width: 300 s
Window overlap: 50 %
AR spectrum options
AR model order: 16
Use factorization: No
Entropy, embedding dimension: 2
Entropy, tolerance: 0.2 x SD
DFA, short-term fluctuations: 4-12 beats
DFA, long-term fluctuations: 13-64 beats


RR Interval Samples Selected for Analysis
, Sample 1, Sample 2,
Sample limits (hh:mm:ss): ,00:00:38-00:02:38,00:04:12-00:06:12,
Sample Analysis Type: Single samples
Artifact correction: Threshold (very low)
Artifacts corrected (%): , 0.758, 4.67,




RESULTS FOR SINGLE SAMPLES
, SAMPLE 1, , SAMPLE 2, ,
Time-Domain Results ,
Statistical parameters ,
Mean RR (ms): , 907.0345, , 779.9576, ,
STD RR (ms): , 110.8779, , 144.9122, ,
Mean HR (beats/min): , 67.3497, , 79.7153, ,
STD HR (beats/min): , 7.7320, , 14.3143, ,
Min HR (beats/min): , 50.9362, , 59.5053, ,
Max HR (beats/min): , 81.9822, , 100.5155, ,
RMSSD (ms): , 90.6346, , 95.0359, ,
NNxx (beats): , 56, , 79, ,
pNNxx (%): , 42.7481, , 53.0201, ,
SDANN (ms): , , , , ,
SDNN index (ms): , , , , ,
Geometric parameters ,
RR tri index: , 16.500000, , 21.428571, ,
TINN (ms): , 451.0000, , 784.0000, ,


Frequency-Domain Results ,FFT spectrum, AR spectrum,FFT spectrum, AR spectrum,
Peak frequencies ,
VLF (Hz): , 0.030000, 0.040000, 0.033333, 0.040000,
LF (Hz): , 0.106667, 0.096667, 0.103333, 0.110000,
HF (Hz): , 0.150000, 0.150000, 0.153333, 0.150000,
Absolute powers ,
VLF (ms^2): , 471.4203, 517.1179, 165.0186, 272.7055,
LF (ms^2): , 7483.5213, 10541.7854, 19017.5467, 18865.7360,
HF (ms^2): , 1397.9024, 1837.9283, 1927.6855, 1753.8160,
VLF (log): , 6.1557, 6.2483, 5.1061, 5.6084,
LF (log): , 8.9205, 9.2631, 9.8531, 9.8451,
HF (log): , 7.2427, 7.5164, 7.5641, 7.4695,
Relative powers ,
VLF (%): , 5.0385, 4.0086, 0.7817, 1.3052,
LF (%): , 79.9827, 81.7187, 90.0840, 90.2913,
HF (%): , 14.9406, 14.2474, 9.1312, 8.3937,
Normalized powers ,
LF (n.u.): , 84.2264, 85.1313, 90.7937, 91.4853,
HF (n.u.): , 15.7333, 14.8424, 9.2032, 8.5048,
Total power (ms^2): , 9356.4259, 12900.0863, 21110.9135, 20894.3105,
LF/HF ratio: , 5.3534, 5.7357, 9.8655, 10.7570,

 

[Peter_SSs]

I'm not sure that I would be able to help you anyway, but your linked images are unreadable to me.

Edit: Never mind, the image problem was at my end.

 

[rlv01]

One strategy:

Sub TestHRV()
    Dim infile As String

    infile = "C:\Users\John.Smith\Downloads\rr-Smith_John_2018-05-11_04-18-38.txt"
    GetHRVData infile
End Sub

Sub GetHRVData(infile As String)
    Const Marker_1 = "RMSSD (ms):"
    Const Marker_2 = "HF (Hz):"
    Const Marker_3 = "LF (Hz):"

    Dim TextLine As String, Line As String
    Dim WS As Worksheet
    Dim SA As Variant
    Dim FDate As Date                                 'file date
    Dim CellDate As Date                              'spreadsheet date

    Set WS = ActiveSheet

    CellDate = Int(WS.Range("A1").Value)
    FDate = Int(FileDateTime(infile))

    If FDate = CellDate Then
        Open infile For Input Access Read As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]        'Open text file for read only.

        Do While Not EOF(1)                           'Loop until end of file.
            Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , TextLine                   'Read line into variable.
            Line = Application.Trim(TextLine)

            If InStr(Line, Marker_1) = 1 Then         'RMSSD
                SA = Split(Line, ",")
                WS.Range("C2") = SA(0)
                WS.Range("D2") = SA(1)
                WS.Range("E2") = SA(3)
            End If
            If InStr(Line, Marker_2) = 1 Then         'HF (Hz)
                SA = Split(Line, ",")
                WS.Range("C4") = SA(0)
                WS.Range("D4") = SA(1)
                WS.Range("E4") = SA(3)
                WS.Range("C6") = SA(0)
                WS.Range("D6") = SA(2)
                WS.Range("E6") = SA(4)
            End If
            If InStr(Line, Marker_3) = 1 Then         'LF (Hz)
                Debug.Print Line
                SA = Split(Line, ",")
                WS.Range("C3") = SA(0)
                WS.Range("D3") = SA(1)
                WS.Range("E3") = SA(3)
                WS.Range("C5") = SA(0)
                WS.Range("D5") = SA(2)
                WS.Range("E5") = SA(4)
            End If
        Loop
        Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]                                       'Close file.
    End If
End Sub