タグ: METs table of Physical Activities

The text file revised edition of “METs table of Physical Activities”

I have asked National Institute of Health and Nutrition if I could publish the text file revised edition of “METs table of Physical Activities” on this blog on July 3rd, 2012. I have received e-mail from staff on July 12, 2012, they consent that I publish the text file.

The file has 4 columns and 826 rows. First row shows data structure. First column shows code, second column METS value, third column major heading, and fourth column specific activities. I couldn’t separate English from Japanese.

METS2011

The source is this PDF file. Although you don’t have to contact National Institute of Health and Nutrition when you would like to use for yourself, you would be asked to contact National Institute of Health and Nutrition when you would like to publish the file or create a web service with the file.

Extract from text revised edition of “METs table of Physical Activities”

National Institute of Health and Nutrition has revised ‘METs table of Physical Activities’ 2011 edition. Because it’s difficult to use as PDF file, I have extracted text data from it. I have asked if I could publish the file on this blog on July 3rd, 2012.

1. Open the file and copy all text.

2. Create new EXCEL book and paste with ‘Text File Wizard’. In the second tab, you have to remove all check mark of delimiters. In the third tab, select ‘String’ data type of column.

3. Press ‘Alt’ key and ‘F11’ key to launch VBE, insert module, and run the following code.

Option Explicit
Sub METS()
Dim mySht   As Worksheet
Dim myRng   As Range
Dim myAr    As Variant
Dim i       As Long
Dim j       As Long
Dim myReg   As Object
Dim myMatches   As Object
Dim myMatch     As Object
Const strReg    As String = "^[0-9]{5}$"
Dim CODE()  As String
Dim METS()  As Single
Dim MajorHeading()  As String
Dim SpecificActivities()    As String
Dim myArray()   As Variant
Set myReg = CreateObject("VBScript.RegExp")
With myReg
    .Pattern = strReg
    .IgnoreCase = True
    .Global = True
End With

Set mySht = ActiveSheet
Set myRng = mySht.UsedRange
myAr = myRng
j = 0
For i = LBound(myAr) To UBound(myAr)
    If myReg.Test(myAr(i, 1)) Then
        Set myMatches = myReg.Execute(myAr(i, 1))
        ReDim Preserve CODE(j)
        ReDim Preserve METS(j)
        ReDim Preserve MajorHeading(j)
        ReDim Preserve SpecificActivities(j)
        CODE(j) = myAr(i, 1)
        METS(j) = myAr(i + 1, 1)
        MajorHeading(j) = myAr(i + 2, 1)
        SpecificActivities(j) = myAr(i + 3, 1)
        j = j + 1
    End If
Next i
ReDim myArray(j, 3)
For j = LBound(myArray) To UBound(myArray) - 1
    myArray(j, 0) = CODE(j)
    myArray(j, 1) = METS(j)
    myArray(j, 2) = MajorHeading(j)
    myArray(j, 3) = SpecificActivities(j)
Next j
Set mySht = Worksheets.Add
mySht.Range("A1").Value = "CODE"
mySht.Range("B1").Value = "METS"
mySht.Range("C1").Value = "MajorHeading"
mySht.Range("D1").Value = "SpecificActivities"
mySht.Range("A2:D827") = myArray
End Sub