Translating an Interview Transcript to HAML with Excel Macros

Here's another Excel spreadsheet + macro solution to a recent problem.

Problem

Take a written transcript of something (maybe an interview, screenplay, podcast, webinar, etc.) from a Word document (in this particular case received from a transcription service like Rev.com):

and have it appear on your website:

If you're using a CSS framework like Boostrap, the HAML code to generate something that looks like the above image might look like this:

.row
  .col-md-3
    %h4
      Host
  .col-md-9
    %p
      Proin porta, risus a mollis consequat, ligula arcu sodales lectus, et imperdiet ipsum leo vel purus. Quisque at ipsum sed sem cursus eleifend non quis nunc. Etiam sit amet auctor lorem. Duis ut semper augue. Nunc facilisis tempor lacus. Ut et pellentesque turpis, eu mollis nibh. Integer id urna ac nisl porta commodo. Maecenas hendrerit dolor vitae ligula vehicula, vitae varius nunc facilisis. Etiam scelerisque dapibus neque ut euismod. Nullam scelerisque lacinia diam.
.row
  .col-md-3
    %h4
      Guest
  .col-md-9
    %p
      Ut quis hendrerit arcu, sit amet tincidunt tellus. Aliquam finibus ante sed enim facilisis, in eleifend nulla gravida.
.row
  .col-md-3
    %h4
      Host
  .col-md-9
    %p
      Suspendisse egestas vitae nisl in tempor. Proin in laoreet neque, et suscipit est. Sed congue accumsan sollicitudin. Vivamus eu sem sodales, congue tortor sed, facilisis ex. In suscipit gravida odio, vel hendrerit turpis. Vestibulum at elit volutpat, faucibus justo pretium, venenatis nisl.
    %p
      Donec sed lectus eget nibh placerat malesuada. Duis suscipit nunc eu sapien ornare, ac egestas metus imperdiet. Morbi rhoncus nisl sit amet tristique semper. Sed sit amet mattis lorem. Sed nec quam non tortor tristique placerat. Vestibulum at purus orci.

Solution

You could probably do this using a (complicated) regex replacement in a text editor. But, the advantages of a standalone, purpose-built tool include easier configuration changes, and easier extension in the future if we need to handle more complex output.

First, notice we can copy and paste the transcript from Word into an Excel spreadsheet and get a nice breakdown of the speaker names in one column and content in another:

Next, add another sheet with some configuration settings. Some CSS frameworks are similar enough that changing these cells is enough to generate the appropriate HAML (Boostrap vs. Foundation, for example):

And now, add a simple macro to write the HAML code to a file. This macro reads down the sheet where the transcript was pasted and uses the settings to generate the HAML code. Note the addition of appropriate whitespace to the output.

Option Explicit

Sub do_all()
  Dim fn As Long, i As Long

  fn = FreeFile
  i = 0
  
  Dim out As String
  out = Range("out_file").Value

  Dim row As String, scolumn As String, sheader As String, tcolumn As String, paragraph As String

  row = Range("row_class").Value
  scolumn = "  " & Range("scolumn").Value
  sheader = "    " & Range("sheader").Value
  tcolumn = "  " & Range("tcolumn").Value
  paragraph = "    " & Range("paragraph").Value
  
  Open out For Append As #fn
    While Range("contents").Offset(i, 1).Value <> ""
      If Range("contents").Offset(i, 0).Value <> "" Then
        Print #fn, row
        Print #fn, scolumn
        Print #fn, sheader
        Print #fn, "      " & Replace(Trim(Range("contents").Offset(i, 0).Value), ":", "")
        Print #fn, tcolumn
      End If

      Print #fn, paragraph
      Print #fn, "      " & Trim(Range("contents").Offset(i, 1).Value)

      i = i + 1
    Wend
  Close #fn
End Sub

Copy paste the result from the output file into your website source code, and you're all set. Easily run it again by pasting in a new transcript to the spreadsheet and re-running the macro.

The code above is available on Github.