Loading database tables from Excel spreadsheets or csv files

Load databases from spreadsheets

I'm often asked to load or to update database tables from source data stored in spreadsheets or comma separated values (csv) files. Often the amounts of data are relatively trivial, or the task is a one-off, so it doesn't seem worth rolling out the big guns - Oracle's SQL*Loader utility or Microsoft's Bulk Copy Program (bcp) used with SQL Server.

It's useful to have an easy-to-use alternative which you can operate with a little typing and a few clicks from a Windows desktop.

This page provides an Excel macro capable of automating the process of generating SQL to load database tables from data stored in an Excel spreadsheet. If you have a csv file you simply need to open it into Excel. Basically, if you can write the SQL to process a single row of spreadsheet data, then this macro can read your template SQL and use it to generate the SQL for the rest.

How it works

As a simple example, suppose we have a database table of employees with two columns. These contain the employee number (empno) and the employee name (ename). We could write template SQL code like:

    INSERT INTO emp (empno, ename) VALUES(%A%,'%B%');

where %A% and %B% are placeholders for the actual data values. The macro would use this template to generate a series of INSERT statements to load the EMP table. The template indicates that for each statement the macro should take the values for the empno and ename columns from columns A and B, respectively, of an Excel worksheet. One INSERT statement would be generated for each row of the source data. Note that the values for the ename column would be embedded in single quotes, exactly as shown by the '%B%' placeholder.

The macro is actually capable of handling data load scenarios which are much more complex than that described above. The spreadsheet available here for download shows examples of:

loading multiple tables from each row of spreadsheet data
updating rows if they exist, and inserting them otherwise
generating scripts for data which must be loaded in multiple stages.

The spreadsheet comes preloaded with some sample data and template SQL which demonstrates these capabilities. In addition to the main macro which generates the SQL, additional macros are provided to clear the generated SQL, save it to the clipboard, and save it to file. These are tied to buttons which are displayed over the generated SQL.

The macros presented here are written in Visual Basic for Applications (VBA) for use with Excel 97 or later. The sample data uses Oracle's dialect of SQL, but because the macros use whatevever template SQL is provided, the dialect used by Microsoft Access or Transact-SQL could equally well be generated.

Important - a word about macro viruses

Excel macros can contain viruses. For your own protection it's recommended that you enable macro virus security in Excel and set it to medium. Excel will then display a warning message whenever you open a workbook that contains macros. You can then decide whether to open the workbook with the macros enabled or whether to open the workbook with the macros disabled so that you can only examine and edit them. A macro virus can be harmful only if it is allowed to run, so disabling the macros allows you to open the workbook safely. The message appears regardless of whether the macro actually contains a virus.

The button below will download the spreadsheet complete with macros and open it in a separate browser window. When given the choice, disable the macros. Examine the macros (use the Tools, Macro and Visual Basic Editor menu options) and satisfy yourself that they do what they say. Use the File and Save As menu options of your browser to save the spreadsheet. You can preview the macro code in the listing below.

Preview the macro code here

  
Attribute VB_Name = "SQLGenerator"
Option Explicit
Private Const mcStart = "%START%"           ' Start of data flag
Private Const mcEnd = "%END%"               ' End of data flag
Private Const mcData = "Data"               ' Sheet name for source data
Private Const mcTemplate = "SQL Template"   ' Sheet name for SQL template
Private Const mcSQL = "SQL"                 ' Sheet name for generated SQL
'
' Clear the generated SQL
'
Sub ClearSQL()
    Sheets(mcSQL).Cells.Clear
End Sub
'
' Generate SQL
'
Sub GenerateSQL()

Dim rData As Range, rSQL As Range, rTemplate As Range, rTest As Range
Dim lDataStartRow As Long
Dim lTemplateStartRow As Long
Dim lLastNonBlankRow As Long

' Check that we have at least one %START% and %END% position in
' the Data and the SQL Template sheets.
If Not bPositionsMarked() Then Exit Sub
Application.Cursor = xlWait

' Find the start row in the Data sheet.
' Start the search 'after' the last row and require the whole row to match.
With Sheets(mcData)
    Set rTest = .Range("A1").EntireColumn.Find(mcStart, .Range("A65536"), xlValues, xlWhole)
End With
lDataStartRow = rTest.Row + 1

' Similarly, find the start row in the SQL Template sheet.
With Sheets(mcTemplate)
    Set rTest = .Range("A1").EntireColumn.Find(mcStart, .Range("A65536"), xlValues, xlWhole)
End With
lTemplateStartRow = rTest.Row + 1
lLastNonBlankRow = Sheets(mcTemplate).Range("A65536").End(xlUp).Row

' Start at cell A1
Set rTemplate = Sheets(mcTemplate).Cells(1, 1)
Set rSQL = Sheets(mcSQL).Cells(1, 1)

' We may re-process the Data sheet several times if there is more than one set
' of %START%, %END% pairs in the SQL Template sheet.
Do
    ' Copy lines from the Template until we find %START%
    Call CopyTemplateLines(rTemplate, rSQL, lLastNonBlankRow)
    
    ' For every line in the "Data" sheet until a cell containing '%END%' is found'
    Set rData = Sheets(mcData).Cells(lDataStartRow, 1)
    Do Until rData = mcEnd
        Call GenerateTemplateLines(rData, rTemplate, rSQL)
    Loop
    
    With Sheets(mcTemplate)
        ' Reposition to the next %END% in the template
        Set rTemplate = .Range("A1").EntireColumn.Find(mcEnd, rTemplate, xlValues, xlWhole)
        Set rTemplate = .Cells(rTemplate.Row + 1, 1)  ' after the %END%
    
        ' Find the next %START% in the template - to check whether we've finished
        Set rTest = .Range("A1").EntireColumn.Find(mcStart, rTemplate, xlValues, xlWhole)
        Set rTest = .Cells(rTest.Row + 1, 1)
    End With
    
' We've finished when we've found the original %START% again
Loop Until rTest.Row = lTemplateStartRow

' Copy any trailing lines from the SQL Template
Call CopyTemplateLines(rTemplate, rSQL, lLastNonBlankRow)
Application.Cursor = xlDefault

End Sub
'
' Save the generated SQL into a file
'
Sub SaveSQLtoFile()

Dim sSaveFileName As String
Static sLastSaveFileName As String
Dim iFile As Integer
Dim lLastSQLRow As Long
Dim lRow As Long
Dim iSaveOK As Integer

lLastSQLRow = Sheets(mcSQL).Range("A65536").End(xlUp).Row

' Get the file name
If sLastSaveFileName = "" Then sLastSaveFileName = "Generated.sql"
sSaveFileName = Application.GetSaveAsFilename(sLastSaveFileName, _
        "SQL Files (*.sql), *.sql", 1, "Save SQL")

If sSaveFileName <> "False" Then
    If Dir$(sSaveFileName) = "" Then
        iSaveOK = vbOK
    Else
        iSaveOK = MsgBox("A file named " & sSaveFileName & _
                  " already exists. Do you want to replace it?", _
                  vbOKCancel)
    End If
    If iSaveOK = vbOK Then
        sLastSaveFileName = sSaveFileName
        iFile = FreeFile
        Open sSaveFileName For Output As iFile
        
        Application.Cursor = xlWait
        For lRow = 1 To lLastSQLRow
            Print #iFile, Sheets(mcSQL).Cells(lRow, 1)
        Next lRow
        
        Close #iFile
    End If
End If
Application.Cursor = xlDefault
End Sub
'
' Save the generated SQL to the clipboard
'
Sub SaveSQLtoClip()
Dim doClip As DataObject
Dim sText As String
Dim lRow As Long
Dim lLastSQLRow As Long

Application.Cursor = xlWait
lLastSQLRow = Sheets(mcSQL).Range("A65536").End(xlUp).Row
Set doClip = New DataObject

For lRow = 1 To lLastSQLRow
    sText = sText & Sheets(mcSQL).Cells(lRow, 1) & vbCrLf
Next lRow

doClip.SetText sText
doClip.PutInClipboard
Set doClip = Nothing
Application.Cursor = xlDefault

End Sub
'
' Copy the text from column A of "SQL Template" sheet to the "SQL" sheet
' until we find a cell containing %START% or until the given row number.
'
Private Sub CopyTemplateLines(rTemplate As Range, rSQL As Range, lLastRow As Long)

' For each line in the SQL template
Do Until rTemplate = mcStart Or rTemplate.Row > lLastRow
    rSQL = Format$(rTemplate)
    
    ' Move on to the next row in the template and in the generated SQL
    Set rTemplate = Sheets(mcTemplate).Cells(rTemplate.Row + 1, 1)
    Set rSQL = Sheets(mcSQL).Cells(rSQL.Row + 1, 1)
Loop
Set rTemplate = Sheets(mcTemplate).Cells(rTemplate.Row + 1, 1)
    
End Sub
'
' Loop thru the "SQL Template" sheet to generate the SQL
' for a single line in the "Data" sheet.
'
Private Sub GenerateTemplateLines(rData As Range, rTemplate As Range, rSQL As Range)
Dim rTemplateVar As Range

' For each line in the SQL template
Set rTemplateVar = rTemplate
Do Until rTemplateVar = mcEnd

    ' Replace any parameters of the form %*% with a column from the Data sheet
    rSQL = "'" & ReplaceParameters(Format$(rTemplateVar), rData.EntireRow)
    
    ' Move on to the next row in the template and in the generated SQL
    Set rTemplateVar = Sheets(mcTemplate).Cells(rTemplateVar.Row + 1, 1)
    Set rSQL = Sheets(mcSQL).Cells(rSQL.Row + 1, 1)
Loop

' Move on to the next row in the data
Set rData = Sheets(mcData).Cells(rData.Row + 1, 1)
    
End Sub
'
' Return the input sSource string with all occurrences of strings like
' %A%, %B% etc replaced by the data in the appropriate column of rReplace
'
Private Function ReplaceParameters(sSource As String, rReplace As Range) As String

Dim iStart As Integer, iFound As Integer
Dim sReturn As String, sCell As String, sFound As String

' Initialise
iStart = 1
sReturn = ""
FindReplacePoint iStart, sSource, iFound, sFound

' Until there are no more occurrences of the search string
Do Until iFound = 0

    If iFound > iStart Then sReturn = sReturn & Mid$(sSource, iStart, iFound - iStart)
    ' Note: the row and column generated in the following is
    ' relative to the rReplace range. It's correct to use row 1.
    sCell = rReplace.Range(sFound & "1")
    sReturn = sReturn & RemoveQuotes(sCell)
    
    ' Start next time where we left off
    iStart = iFound + Len(sFound) + 2
    FindReplacePoint iStart, sSource, iFound, sFound

Loop

If iStart <= Len(sSource) Then sReturn = sReturn & Mid$(sSource, iStart)
ReplaceParameters = sReturn
End Function
'
' Search for strings like %A% or %AA%
'
Private Sub FindReplacePoint(iStart As Integer, sSource As String, _
                         iFound As Integer, sFound As String)
Dim iFound1 As Integer, iFound2 As Integer

iFound1 = InstrLike(iStart, UCase$(sSource), "%[A-Z]%*")
iFound2 = InstrLike(iStart, UCase$(sSource), "%[A-Z][A-Z]%*")
If iFound1 <> 0 And (iFound1 < iFound2 Or iFound2 = 0) Then
    iFound = iFound1
    sFound = Mid$(sSource, iFound + 1, 1) ' Found string like %A%
Else
    iFound = iFound2
    If iFound2 <> 0 Then sFound = Mid$(sSource, iFound + 1, 2) ' Found string like %AA%
End If
        
End Sub

'
' Return the start position of a string like sSearch in string sSource
' starting from character position iStart.
'
Private Function InstrLike(iStart As Integer, sSource As String, sSearch As String) As Long
Dim iChar As Integer

For iChar = iStart To Len(sSource)
    If Mid$(sSource, iChar) Like sSearch & "*" Then
        InstrLike = iChar
        Exit Function
    End If
Next iChar
 
End Function
'
' Check that %START% and %END% strings are present in the Data and SQL Template sheets
'
Function bPositionsMarked() As Boolean

bPositionsMarked = False

If Sheets(mcData).Cells(1, 1).EntireColumn.Find(mcStart, , xlValues, xlWhole) Is Nothing Then
    MsgBox mcStart & " position not found in Data sheet"
    Exit Function
End If

If Sheets(mcData).Cells(1, 1).EntireColumn.Find(mcEnd, , xlValues, xlWhole) Is Nothing Then
    MsgBox mcEnd & " position not found in Data sheet"
    Exit Function
End If

If Sheets(mcTemplate).Cells(1, 1).EntireColumn.Find(mcStart, , xlValues, xlWhole) Is Nothing Then
    MsgBox mcStart & " position not found in SQL Template sheet"
    Exit Function
End If

If Sheets(mcTemplate).Cells(1, 1).EntireColumn.Find(mcEnd, , xlValues, xlWhole) Is Nothing Then
    MsgBox mcEnd & " position not found in SQL Template sheet"
    Exit Function
End If

bPositionsMarked = True
End Function
'
' Replace single quotes with two single quotes
'
Public Function RemoveQuotes(sSql As String) As String
    Dim sReply As String
    Dim iStart As Integer, iQuote As Integer
    
    sReply = ""
    iStart = 1
    iQuote = InStr(1, sSql, "'")
    
    While iQuote <> 0 And iStart < Len(sSql)
        sReply = sReply & (Mid$(sSql, iStart, iQuote - iStart + 1) & "'")
        iStart = iQuote + 1
        iQuote = InStr(iStart, sSql, "'")
    Wend
    
    RemoveQuotes = RTrim$(sReply & Mid$(sSql, iStart))
End Function