Spreadsheet to .bib

An obscure problem with a hairy solution

I was asked 15 years ago (ish) about converting to BibTeX from database or .csv formats. There are many elegant ways to solve this problem, (for example JabRef will link to an SQL server) but I wanted a quick and dirty one that everybody could understand and didn't take any setting up.

To this end I have written an Excel macro that does the job in a simple-headed sort of way and can be hacked by anyone. Just import your .csv in to an Excel sheet (first row is assumed to contain headings that are not used) , add the text below as a macro, edit the macro to (a) specify the name of the .bib file you want to create, and (b) to have the right number of rows and columns. Then run it.

First column *must* be the unique ID (the key) in my version but of course this can be changed.

'
'   =========================================
'   almost the quickest and dirtiest
'   solution to csv -> bibtex problem
'   =========================================
'
Sub toBibTeX()
    '
    '   an excel macro which takes a sheet
    '   with database style content and turns
    '   it in to a BibTeX .bib file
    '
    '   you can create the excel sheet by importing
    '   csv data from a database or bibliographic
    '   software
    '
    '   N.B.
    '   this is not a smart routine so
    '   you will need to edit the bits inside
    '   asterisks to match your requirements
    '
    '   *****************************************
    '   substitute however many rows and columns
    '   in the source sheet the unique id *must*
    '   be the first column and no blank columns
    '   use standard BibTeX field names
    Const maxRows = 7
    Const maxCols = 5
    Dim heading(1 To maxCols) As String
        heading(1) = "unique id"
        heading(2) = "title"
        heading(3) = "author"
        heading(4) = "journal"
        heading(5) = "month"
    '   heading(6) = ... and so on up to maxCols
    '   decide what you want the .bib file to be
    '   called and where you want it saved - NB
    '   not all locations work due to file
    '   permissions so try a few
    Const opFile = "c:\martin\biblio.bib"
    
    '   *****************************************
    
    '   dont change anything from here down!!
    '   =========================================
    Dim currentVal As String
    ' move to first cell of source data
    Range("A2").Select
    
    ' open or create .bib file
    Open opFile For Output As #1
    
    ' double loop for rows and columns
    For Row = 1 To maxRows
        For col = 1 To maxCols
        
            ' cursor is in correct position so easy-peasy
            currentVal = ActiveCell.FormulaR1C1
        
            ' there are three types of lines written to the output record
            ' first lines, last lines, and all other lines
            If col = 1 Then
            ' as a first record line
                currentVal = "@MISC{" + currentVal + ","
            End If
            
            If col > 1 Then
            ' an all other record line
                currentVal = heading(col) + "={" + currentVal + "}"
                If col < maxCols Then
                ' add comma for all but last line
                    currentVal = currentVal + ","
                End If
            End If
                       
            ' append current value to output file
            Print #1, currentVal
                
            ' move cursor to next column
            ActiveCell.Offset(0, 1).Range("A1").Select
        Next
        ' end of record
         
        ' append closing curly to record and leave a line
        currentVal = "}"
        Print #1, currentVal
        Print #1, ""
        
        ' move cursor to next row first column
        ActiveCell.Offset(0, -maxCols).Range("A1").Select
        ActiveCell.Offset(1, 0).Range("A1").Select
        
    Next
    ' end of all records
    
    ' close file
    Close #1
    
' done
End Sub