EXCEL Add-ins and MySQL Scripts

I write a lot of update scripts on MySQL and many times the data I update comes from Excel.  In order to get the data I want, I create a table of the data from the spreadsheet.  It ends up looking something like:

Drop Table If Exists _TempTable;
Create Table _TempTable
(value1 varchar(10)
, value2 varchar(100)
, value3 decimal(10,2));
Insert Into _TempTable

And that’s fine but to easily create the inserts for multiple rows I created an Excel Add-in.

The specifications of the add-in are that

  1. You give it a range of cells
  2. It will create the MySQL script complete with single quotes around it
  3. It will take any single quotes from the input and double them (Example: the second line, second value will insert B’C)
  4. It will keep any leading zeroes (Example: the first line, first value will keep 01 instead of making it 1)
  5. It will trim spaces from the beginning and end of each cell’s value

Here’s the script.

Function InsertIntoValues_Range(v As Range)
 If v Is Nothing Then Exit Function
 Dim cell As Range
 InsertIntoValues_Range = ",('"
 For Each cell In v.Cells
    InsertIntoValues_Range = InsertIntoValues_Range + Replace(Trim(cell.Text), "'", "''") + "','"
 Next cell
 InsertIntoValues_Range = Left(InsertIntoValues_Range, Len(InsertIntoValues_Range) - 2) + ")"
End Function

If you’d like to automatically replace empty strings with NULL, add this function:

Function InsertIntoValues_Range_Nullable(v As Range)
 If v Is Nothing Then Exit Function
 Dim cell As Range
 InsertIntoValues_Range_Nullable = ",("
 For Each cell In v.Cells
    If Trim(cell.Text) = "" Then
        InsertIntoValues_Range_Nullable = InsertIntoValues_Range_Nullable + "NULL,"
        InsertIntoValues_Range_Nullable = InsertIntoValues_Range_Nullable + "'" + Replace(Trim(cell.Text), "'", "''") + "',"
    End If
 Next cell
 InsertIntoValues_Range_Nullable = Left(InsertIntoValues_Range_Nullable, Len(InsertIntoValues_Range_Nullable) - 1) + ")"
End Function

To add it to your worksheet, which will make the functions only available to that worksheet

  1. Just hit <Alt>+F11 to open the Visual Basic editor
  2. Right-click your worksheet and choose Insert | Module
  3. In the opened window paste the above function
  4. Close the Visual Basic application (<Alt>+Q)
  5. The worksheet will now have this new function available

To make this an Add-In which makes the functions automatically available to any Excel workbook you open

  1. Add the functions to an empty workbook using steps above
  2. Save the workbook as an .xla file
  3. Choose File | Options, Add-Ins
  4. Manage: Excel Add-ins, “Go..”
  5. Select your Add-In (browse for it if it’s not listed) and hit “OK”

So now all I have to do is type in something like “=InsertIntoValues_Range(A1:C1)” and double-click the bottom right cell corner to apply it all the way down and I’ve got myself the second half of my insert statement.

This entry was posted in Add-ins, Excel, MySQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s