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

rg8588

New Member
Joined
Apr 26, 2018
Messages
4
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,
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,474
Office Version
  1. 365
Platform
  1. Windows
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. :)
 
Last edited:

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
One strategy:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,386
Messages
5,636,014
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top