How do I import log info from an email into Excel 2013?

**mwatkins** · 04-21-2014, 06:58 PM

I receive the below log info from an email. What would be the best way using a VBA script or Macro to import this info into Excel 2013 with appropriate columns? I have copied and pasted the info into a text file and used "Get External Data from file to import into Excel 2013. I'm lost in figuring a way for VBA script or a Macro to enter data into columns with headings across the top. I would like to be able to "flag" some of the data. Like "bytes"..lets say there is a 5%+/- increase/decrease in bytes. I would like for that data to be highlighted in Red as an alert.

When I used the Get External Data, it worked but the data looks pretty much like what you see below.

Columns I want to create across top of worksheet.(not necessarily) in the below order:
Start | End | bytes | files | duration | cluster | sites | script

Subject: db_backup_pt1_summary

cluster: dbclust10 (ha10a)
script: db_backup_pt1 3.3
sites: 10 selected, 10 rsynced
start: 2014-04-18 01:30:02
end: 2014-04-18 03:45:52
duration: 2.26 hours
files: 64438 counted, 8945 updated
bytes: 129.09G counted, 96.51G transferred error lines: 0
---------------------------------------------------------------------
cluster: dbclust05 (ha07b)
script: db_backup_pt1 3.3
sites: 77 selected, 77 rsynced
start: 2014-04-18 00:45:01
end: 2014-04-18 04:55:23
duration: 4.17 hours
files: 479746 counted, 17778 updated
bytes: 214.68G counted, 171.33G transferred error lines: 0
---------------------------------------------------------------------
cluster: dbclust06 (ha08a)
script: db_backup_pt1 3.3
sites: 61 selected, 61 rsynced
start: 2014-04-17 23:00:02
end: 2014-04-18 04:26:28
duration: 5.44 hours
files: 388307 counted, 17266 updated
bytes: 251.41G counted, 198.71G transferred error lines: 0
---------------------------------------------------------------------
cluster: dbclust12 (ha04)
script: db_backup_pt1 3.3
sites: 2 selected, 2 rsynced
start: 2014-04-17 23:00:01
end: 2014-04-17 23:38:50
duration: .64 hours
files: 14933 counted, 852 updated
bytes: 48.10G counted, 29.77G transferred error lines: 0
---------------------------------------------------------------------
cluster: dbclust08 (ha09a)
script: db_backup_pt1 3.3
sites: 67 selected, 67 rsynced
start: 2014-04-17 23:00:01
end: 2014-04-18 03:58:20
duration: 4.97 hours
files: 428251 counted, 17042 updated
bytes: 207.56G counted, 130.59G transferred error lines: 0
---------------------------------------------------------------------
cluster: dbclust04 (ha07a)
script: db_backup_pt1 3.3
sites: 74 selected, 74 rsynced
start: 2014-04-17 23:00:01
end: 2014-04-18 06:10:02
duration: 7.16 hours
files: 464192 counted, 18622 updated
bytes: 347.42G counted, 279.11G transferred error lines: 0
---------------------------------------------------------------------
cluster: virtdb02 (ha03)
script: db_backup_pt1 3.3
sites: 58 selected, 58 rsynced
start: 2014-04-17 22:30:02
end: 2014-04-18 02:31:34
duration: 4.02 hours
files: 368836 counted, 14567 updated
bytes: 171.42G counted, 135.23G transferred error lines: 0
---------------------------------------------------------------------
cluster: dbclust13 (ha11b)
script: db_backup_pt1 3.3
sites: 2 selected, 2 rsynced
start: 2014-04-17 22:00:02
end: 2014-04-17 22:13:42
duration: .22 hours
files: 12104 counted, 275 updated
bytes: 29.02G counted, 17.26G transferred error lines: 0
---------------------------------------------------------------------
cluster: dbclust09 (ha09b)
script: db_backup_pt1 3.3
sites: 67 selected, 63 rsynced
start: 2014-04-17 22:00:02
end: 2014-04-18 02:15:49
duration: 4.26 hours
files: 462446 counted, 14348 updated
bytes: 310.91G counted, 195.66G transferred error lines: 0
---------------------------------------------------------------------
cluster: dbclust07 (ha08b)
script: db_backup_pt1 3.3
sites: 49 selected, 49 rsynced
start: 2014-04-17 22:00:01
end: 2014-04-18 03:18:33
duration: 5.30 hours
files: 307453 counted, 16432 updated
bytes: 181.96G counted, 146.45G transferred error lines: 0
---------------------------------------------------------------------
cluster: virtdb03 (ha06)
script: db_backup_pt1 3.3
sites: 74 selected, 74 rsynced
start: 2014-04-17 21:45:05
end: 2014-04-18 05:18:28
duration: 7.55 hours
files: 453662 counted, 18147 updated
bytes: 383.74G counted, 311.78G transferred error lines: 0
---------------------------------------------------------------------

**TMS** · 04-21-2014, 07:32 PM

Why do you copy and paste it into a text file? Why not paste it straight into Excel?

**mwatkins** · 04-22-2014, 01:23 PM

Sorry I'm a newbie! Ok..I'll paste directly into Excel. However I'm still really looking for a solution that will allow me to format the data as stated in the previous thread? Any ideas?

**TMS** · 04-22-2014, 03:34 PM

This works with the sample data in your post, copied and pasted straight into Excel (on Sheet1)

' Module: mTransposeData

'Option Private Module
Option Explicit

Sub sTransposeData()

' Takes raw data on the Input Sheet, transposes
' it, and writes it to the Output Sheet

Dim shInput As Worksheet: Set shInput = Sheet1
Dim shOutput As Worksheet: Set shOutput = Sheet2

Dim awf As WorksheetFunction: Set awf = WorksheetFunction

Dim aHeadings
aHeadings = Array("cluster", "script", "sites", "start", "end", "duration", "files", "bytes")

Dim cell As Range
Dim lLR As Long, lNR As Long
Dim vRowdata, vOutput
Dim lRowCount As Long, lColumn As Long

Const clStartRow As Long = 3    ' start of data

With shInput    ' loop through the Input Data
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
    lRowCount = awf.CountIf(.Range("A1").EntireColumn, aHeadings(LBound(aHeadings)) & "*")
    ReDim vOutput(1 To lRowCount, 1 To UBound(aHeadings) + 1)
    lRowCount = 0
    For Each cell In .Range("A" & clStartRow & ":A" & lLR)
        On Error GoTo lblSkip
        ' determine cell type
        vRowdata = Split(cell, ":")
        Select Case vRowdata(0)
        Case Is = aHeadings(0)
            lRowCount = lRowCount + 1
            lColumn = 1
        Case Is = aHeadings(1): lColumn = 2
        Case Is = aHeadings(2): lColumn = 3
        Case Is = aHeadings(3): lColumn = 4
        Case Is = aHeadings(4): lColumn = 5
        Case Is = aHeadings(5): lColumn = 6
        Case Is = aHeadings(6): lColumn = 7
        Case Is = aHeadings(7): lColumn = 8
        Case Else: GoTo lblSkip
        End Select
        ' store cell data
        vOutput(lRowCount, lColumn) = _
            awf.Substitute(cell, vRowdata(0) & ": ", "")
lblSkip:
    On Error GoTo 0
    Next 'cell
End With

With shOutput   ' Write the array to the Output sheet
    lNR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & lNR).Resize(lRowCount, lColumn) = vOutput
    With .Range("A1").Resize(1, lColumn)
        .Value = aHeadings
        .Font.Bold = True
        .EntireColumn.AutoFit
    End With
End With

End Sub

See the attached example workbook.

Regards, TMS