MacLochlainns Weblog

A colleague asked how a User Defined Function (UDF) could simplify their grading. They were using a VLOOKUP() function and range component that they’d copy into spreadsheets generated from Blackboard.

You typically want a letter grade before you transcribe it into the student record system. What you usually start with is a percentage and a defined range of percentages that map to letter grades. While you can solve that probelm with a VLOOKUP() function, is it the best way. I’d suggest this type of activity is ideally suited to a UDF solution.

The VLOOKUP() function call in cell C2 is:

=VLOOKUP(B2,$E$2:$G$13,MATCH("Letter Grade",$E$1:$G$1,FALSE),TRUE)

A User Defined Function (UDF) can replace the VLOOKUP() function, the nested MATCH() function, and lookup range. You create a record type, like VirtualLookup, which must be defined outside of a subroutine or function.

The letterGrade() UDF call in cell C2 is much simpler:

=letterGrade(B2)

You can refer to the following blog post for clarification on how to create a UDF. UDFs can simplify our lives for routine operations. If you’re not interested in the programming, just copy it into your Visual Basic for Application library and it should work.

These letterGrade examples require a custom data type. It must be defined before any function or subroutine in a module. It’s generally a good idea to make these available in other modules by making them Public.

' Define a record type (must happen outside of a function or sub block.
Type VirtualLookupRecord
  lowerLimit As Double
  upperLimit As Double
  letterGrade As String * 2    ' A variable length string up to 2 characters.
End Type

For those interested in understanding how to program this UDF. You leverage that User Defined Type (UDT) when you define the function. In this example, you create a fifteen element array of the record structure. The example defines an array with 1-based numbering index. The default for Microsoft Excel is 0-based numbering. You would define a 0-based numbered array of fifteen elements like the one in the example.

Here’s the complete function that you can copy into your spreadsheet.

Dim VirtualLookup(15) As VirtualLookup

Since the formal parameter of the function is optional, you need an if-block to replace a null value with a zero. Then, you need a loop to read through the array and find where the call parameter value is within the lower and upper grade range values. When it finds a match, it assigns the letter grade to a variable and then breaks out (exits) the loop. If you implement this, don’t forget to comment out the debugging MsgBox() function call.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

' Define a function to convert a percentage into a letter grade.
Function letterGrade(Optional percentage As Double = 0)
 
  ' Define a return variable
  Dim grade As String * 2
 
  ' Define a single dimension array of a UDT (record)
  Dim VirtualLookup(1 To 12) As VirtualLookupRecord
 
  ' Record initialization
  VirtualLookup(1).lowerLimit = 0.93
  VirtualLookup(1).upperLimit = 1#
  VirtualLookup(1).letterGrade = "A"
  VirtualLookup(2).lowerLimit = 0.9
  VirtualLookup(2).upperLimit = 0.93
  VirtualLookup(2).letterGrade = "A-"
  VirtualLookup(3).lowerLimit = 0.87
  VirtualLookup(3).upperLimit = 0.9
  VirtualLookup(3).letterGrade = "B+"
  VirtualLookup(4).lowerLimit = 0.83
  VirtualLookup(4).upperLimit = 0.87
  VirtualLookup(4).letterGrade = "B"
  VirtualLookup(5).lowerLimit = 0.8
  VirtualLookup(5).upperLimit = 0.83
  VirtualLookup(5).letterGrade = "B-"
  VirtualLookup(6).lowerLimit = 0.77
  VirtualLookup(6).upperLimit = 0.8
  VirtualLookup(6).letterGrade = "C+"
  VirtualLookup(7).lowerLimit = 0.73
  VirtualLookup(7).upperLimit = 0.77
  VirtualLookup(7).letterGrade = "C"
  VirtualLookup(8).lowerLimit = 0.7
  VirtualLookup(8).upperLimit = 0.73
  VirtualLookup(8).letterGrade = "C-"
  VirtualLookup(9).lowerLimit = 0.67
  VirtualLookup(9).upperLimit = 0.7
  VirtualLookup(9).letterGrade = "D+"
  VirtualLookup(10).lowerLimit = 0.63
  VirtualLookup(10).upperLimit = 0.67
  VirtualLookup(10).letterGrade = "D"
  VirtualLookup(11).lowerLimit = 0.6
  VirtualLookup(11).upperLimit = 0.63
  VirtualLookup(11).letterGrade = "D-"
  VirtualLookup(12).lowerLimit = 0#
  VirtualLookup(12).upperLimit = 0.6
  VirtualLookup(12).letterGrade = "F"
 
  ' Read through the possible lookup array values.
  For i = 1 To (UBound(VirtualLookup) + 1)
 
    ' Assign a grade if the percentage criterion or criteria match.
    If percentage > VirtualLookup(1).lowerLimit Then
      grade = VirtualLookup(1).letterGrade
      ' Exit the loop.
      Exit For
    ElseIf percentage > 0 And _
           percentage >= VirtualLookup(i).lowerLimit And _
           percentage < VirtualLookup(i).upperLimit Then
      grade = VirtualLookup(i).letterGrade
      Exit For
    Else
      grade = "UW"
    End If
 
  Next i
 
  ' A debug message (remark out for deployment).
  MsgBox ("Completed [" + grade + "]")
 
  ' Return the letter grade.
  letterGrade = grade
 
End Function

Somebody asked for a variable array set by the input parameters. That’s a bit more programming and requires understanding the two types of array initialization and the difference between 0-based and 1-based numbering systems. Since this program assigns a constructed Array type to a Variant data type variable, 0-based arrays are consistently used in the example. That’s a change from the foregoing example.

Here is a function that lets you set the hundredth limits to something other than the traditional .x3 and .x7. It substitutes default values if you attempt to enter something greater than a tenth for either limit.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166

' Define a function to convert a percentage into a letter grade.
Function letterGrade(Optional percentage As Double = 0, _
                     Optional minusTenth As Double = 0.03, _
                     Optional plusTenth As Double = 0.07)
 
  ' Define a variable length string variable.
  Dim grade As String * 2
 
  ' Define local variables.
  Dim minusInverse As Double
  Dim plusInverse As Double
  Dim tenth As Double
 
  ' Assign value to counter.
  Dim counter As Double
 
  ' Define a single dimension array of a UDT (record).
  Dim LetterGrades As Variant
  Dim VirtualLookup(0 To 11) As VirtualLookupRecord
 
  ' Fix incorrect numeric data entry of lower bound.
  If minusTenth >= 0.1 Then
    minusTenth = 0.03
  End If
 
  ' Fix incorrect numeric data entry of upper bound.
  If plusTenth >= 0.1 Then
    plusTenth = 0.07
  End If
 
  ' Assign values to local variables.
  counter = 1#
  minusInverse = 0.1 - minusTenth
  plusInverse = 0.1 - plusTenth
  tenth = 0.1
 
  ' Initialize letter array.
  LetterGrades = Array("A", "A-", "B+", "B", "B-", "C+", "C", "C-", "D+", "D", "D-", "F")
 
  ' Use the array of letter grades to dynamically assign lower and upper bounds.
  For i = 0 To UBound(LetterGrades)
 
    ' There are three models for grades.
    ' -------------------------------------------------------------------
    '  [If]    =[A]     Has two possible values, a + or unadorned.
    '  [ElseIf]=[B,C,D] Have three possible values, a +, -, or unadorned.
    '  [ElseIf]=[F]     Has only an unadorned.
    ' -------------------------------------------------------------------
    If Left(LetterGrades(i), 1) = "A" Then
 
      ' The grade is one character for an unadorned grade.
      If Len(LetterGrades(i)) = 1 Then
        VirtualLookup(i).lowerLimit = counter - minusInverse
        VirtualLookup(i).upperLimit = counter
        VirtualLookup(i).letterGrade = LetterGrades(i)
 
      ' The grade is more than one character and second character a minus.
      ElseIf Len(LetterGrades(i)) > 1 And Mid(LetterGrades(i), 2, 1) = "-" Then
        VirtualLookup(i).lowerLimit = counter - tenth
        VirtualLookup(i).upperLimit = counter - minusInverse
        VirtualLookup(i).letterGrade = LetterGrades(i)
      End If
 
    ElseIf Left(LetterGrades(i), 1) = "B" Or _
           Left(LetterGrades(i), 1) = "C" Or _
           Left(LetterGrades(i), 1) = "D" Then
 
      ' The grade is one character for an unadorned grade.
      If Len(LetterGrades(i)) = 1 Then
        VirtualLookup(i).lowerLimit = counter - minusInverse
        VirtualLookup(i).upperLimit = counter - plusInverse
        VirtualLookup(i).letterGrade = LetterGrades(i)
 
      ' The grade is more than one character.
      ElseIf Len(LetterGrades(i)) > 1 Then
 
        ' The second character is a plus.
        If Mid(LetterGrades(i), 2, 1) = "+" Then
          VirtualLookup(i).lowerLimit = counter - plusInverse
          VirtualLookup(i).upperLimit = counter
          VirtualLookup(i).letterGrade = LetterGrades(i)
 
        ' The second character is a minus.
        ElseIf Mid(LetterGrades(i), 2, 1) = "-" Then
          VirtualLookup(i).lowerLimit = counter - tenth
          VirtualLookup(i).upperLimit = counter - minusInverse
          VirtualLookup(i).letterGrade = LetterGrades(i)
        End If
 
      End If
 
    ElseIf Left(LetterGrades(i), 1) = "F" Then
 
      VirtualLookup(i).lowerLimit = 0#
      VirtualLookup(i).upperLimit = counter
      VirtualLookup(i).letterGrade = LetterGrades(i)
 
    End If
 
    ' Debug message demonstrating the changed values of the dynamically
    ' constructed array (remark out for deployment).
    ' MsgBox (" Counter [" + CStr(counter) + _
    '         "] Index [" + CStr(i) + _
    '         "] Grade [" + CStr(LetterGrades(i)) + _
    '         "] LLimit [" + CStr(VirtualLookup(i).lowerLimit) + _
    '         "] ULimit [" + CStr(VirtualLookup(i).upperLimit) + "]")
 
    ' Increment the tenth value when letter grades change, like A to B, et cetera.
    If LetterGrades(i) = LetterGrades(UBound(LetterGrades)) Then
 
      ' Force a loop exit to avoid reading past the last index value.
      Exit For
 
    ElseIf Not Left(LetterGrades(i), 1) = Left(LetterGrades(i + 1), 1) Then
 
      ' Decrement the tenth placeholder.
      counter = counter - tenth
 
    End If
 
  Next i
 
  ' Read through the possible lookup array values.
  For i = 0 To UBound(VirtualLookup)
 
    ' Assign a grade if the percentage criterion or criteria match, and
    ' force loop exits until the last element of the array is read.
    ' -------------------------------------------------------------------
    '  [If]     Assumes an A is always the first letter.
    '  [ElseIf] Handles all numbers greater than zero.
    '  [Else]   Assumes a zero or null indicate an unofficial withdrawal.
    ' -------------------------------------------------------------------
    If percentage > VirtualLookup(0).lowerLimit Then
 
      ' Assign grade, alsways the first element of the array.
      grade = VirtualLookup(0).letterGrade
 
      ' Force a loop exit when match found.
      Exit For
 
    ElseIf percentage > 0 And _
           percentage >= VirtualLookup(i).lowerLimit And _
           percentage < VirtualLookup(i).upperLimit Then
 
      ' Assign grade.
      grade = VirtualLookup(i).letterGrade
 
      ' Force a loop exit.
      Exit For
 
    Else
 
      ' Assign grade.
      grade = "UW"
 
    End If
 
  Next i
 
  ' A debug message (remark out for deployment).
  ' MsgBox ("Completed [" + grade + "]")
 
  ' Return the letter grade.
  letterGrade = grade
 
End Function

I hope this helps folks that have to perform grading activities in Excel.

Microsoft Excel supports macros but it also supports library functions, known as User Defined Functions (UDF). Library functions are less risky than macros because they must return a value to a cell. This is a quick tutorial, mostly for my students, but as usual for anybody who’s interested.

Microsoft Excel User Defined Functions (UDFs) are different than standard VBA macros. They’re behavior is restricted. You can’t access other cells in a workbook, and may only return a value (also known as an expression) to the cell that uses the formula. That having been said, they can dramatically hide the complexity of mega-formulas and remove them from the editing control of users.

Here are the steps to configure your Excel 2007 environment to work with Visual Basic for Applications (VBA) and UDFs. They show you how to display the developer ribbon, open a module for editing, save VBA code into a library (*.xlam file), and add the library file as an “Add-in” library to your Excel 2007 installation. The name of the library will be the same as the Workbook where you created it.

1. Displaying Excel’s Developer Tab

2. Click the Developer ribbon tab. Click the left most icon to launch the Visual Basic Editor screen.

3. Open a Module in the Visual Basic Editor by clicking the Insert menu item and choosing the Module element.

4. Copy the following function definition into the open Module. The name of the UDF will be the case-sensistive name of the function in the VBA module. That means you’ll now have a newSerialDate function in your list of functions when you click the insert function button.

=monthEnd(cell_reference)

Public Function newSerialDate(dateIn)
 
  ' Define local variables.
  Dim day As String
  Dim month As String
  Dim year As String
  Dim startPosition As Integer
  Dim endPosition As Integer
  Dim length As Integer
 
  ' Initialize local variables and parse the month from the left.
  startPosition = 1
  endPosition = InStr(startPosition, dateIn, "/")
  month = Left(dateIn, endPosition - 1)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Month [" + month + "]")
 
  ' Shift the start position and parse the day from the middle.
  startPosition = endPosition + 1
  endPosition = InStr(startPosition, dateIn, "/")
  day = Mid(dateIn, startPosition, endPosition - startPosition)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Day [" + day + "]")
 
  ' Get the remainder of the string.
  startPosition = endPosition
  length = Len(dateIn) - startPosition
  year = Right(dateIn, length)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Year [" + year + "]")
 
  ' Return a value from a User Defined Function (UDF) by using
  ' the function name as the return variable.
  newSerialDate = DateSerial(year, month, day)
 
End Function

Function newSerialDate(dateIn As Double)
  newSerialDate = dateIn
End Function

Function newSerialDate(dateIn)
  newSerialDate = DateValue(dateIn)
End Function

5. Create the following spreadsheet, the formula values are noted below the screen shot. You should be able to copy and past them into the spreadsheet. After you’ve created the spreadsheet, entering a new date in cell A1 causes the UDF to run. When the UDF runs “as-is”, you’ll see message dialog boxes that show values set during runtime.

6. You can now save this as an Add In library but comment out those debug MsgBox() function calls. Click the Office Button and click SaveAs in the menu, then accept Excel Workbook initially. When you get to the SaveAs dialog, choose Excel Add-In as the file type. Below is a screen capture of the drop down selection box.

7. Open a new Excel Workbook. Click the Excel Options button. Click the Add-Ins tab. Inside the right side pane, make sure the Manage drop down says Excel Add-ins before you click the Go button.

8. Check the Samplefunction check box as shown below. Samplefunction is the name of the Workbook that contains the module, and it is saved as an *.xlam file. Click the OK button to add the library. You’ve now created, and added an Add-In library to your new spreadsheet. It will create a SampleFunction.xlam file in the users directory.

The file is found by default in:

C:\Users\UserName\AppData\Roaming\Microsoft\AddIns

Ranjit asked how you could call a UDF from inside a module. In the answer noted below, I show how to do it with the Unary function:

Function Unary(number As Integer)
  Unary = number + 1
End Function

Then, enter the following ordinary Increment macro:

Sub Increment()
  ActiveCell.FormulaR1C1 = Unary(ActiveCell.Value())
End Sub

Enter a number in a cell, then navigate to Tools -> Macro -> Macros… and choose the increment macro, which increments the value previously in the cell by calling the Unary function.

As always, I hope this is helpful to a few folks.

I popped for QuickTime Pro for Windows 7 ($29.99). The reason for doing so, was to create native screen capture that would integrate with Mac Adobe Premiere Pro, CS4. That’s because my Camtasia output didn’t work due to a compression mismatch.

Unfortunately, QuickTime Pro on Windows 7 doesn’t support screen capture. Quite a difference between what the product does on a Mac OS X versus a Windows 7 OS. I thought only Microsoft shorted users on the other platform, like Microsoft Excel 2007 versus Microsoft Excel 2008 (a far inferior product). Oops, I was wrong! Apple does it too. 🙁

Over a year ago, I put out a blog page on how to query the contents of a table from an Oracle database through Excel. I meant to get back to provide much more about this. Specifically, I want to cover the XML mechanism used to accomplish the task. However, a blog reader augmented my page with a solution comment. I’m thrilled he did because it tells me to get a basic thing out rather than hang folks up halfway. My thanks go to Vernon.

Here’s a quick addendum to the original post with screen shots because folks tell me they’re very useful. It demonstrates how you write queries in Microsoft Excel against the Oracle database. While I plan a full tutorial on a different URL, this should help everybody in the meantime. This content is dependent on the generic mechanism, which I covered in this older post.

It starts with an alternative to Step #9 in the older blog page, and therefore, I’ve started the numbering as 9 there.

9. The Import Data dialog asks you how and where you want to import it. When you don’t want the contents of a simple table, click the Properties button.

10. Clicking the prior dialog’s Property button brings you to the Connection Properties dialog. The Usage tab is first for a reason but the Definition tab is where you need to go to enter a free form query. Click the Definition tab highlighted in yellow below.

11. In this dialog, the most important boxes are the Command type (as you may guess, Table is the default value) and Command text boxes. The first thing to do, is click on the Command type multiple select widget and choose SQL.

Now, you can write any query that you’d like against the selected Oracle schema. You may reference any tables or views for which your user (schema) has SELECT permissions. You don’t need to limit yourself to a single table because ANSI SQL:89 (comma delimited tables), and ANSI SQL:92 (INNER JOIN, et cetera) are both supported. Don’t forget that Oracle uses a different concatenation method than Microsoft Access or SQL Server, you can find that here. After you’ve entered your query, click the OK button to proceed.

12. When you clicked OK, you’ve instructed Microsoft Excel 2007 to change the connection, it raises this dialog box (click on it if you want to read it). You click Yes to continue and save your query.

13. You’re back to where you began, more or less, this it the Import Data dialog. This is Step #9 but this time it’ll run your query not return a simple table content. Click OK to run the query. Don’t be surprised if you get a message saying no password was provided. It just means you’ll be prompted to enter one. You should never store that password because it’s stored in plain text inside an XML file.

You can find those data source files in this directory:

C:\Document and Settings\<some_user>\My Documents\My Data Sources

It’s always interesting troubleshooting things that students run into when they work with Excel. For example, in Excel when is one not equal to one, or for that matter when is one less than one? If you use the CODE(cell_reference) function, you’d find both cells contain the same ASCII value of 49.

If you’re wondering why the one on the left is less than the one on the right, the one on the right is a text value and the one on the left is a number. If you use the TYPE(cell_reference) function, you’d find that a text field returns a 2 and a number (or date) cell returns a 1. All equality comparisons are identity comparisons in Microsoft Excel, which means they compare type and value. Inequality comparisons, compare type and return a value based on the type number and never compare value when the types differ. There are three other types: (1) A logical value is 4; (2) An error message is 16; and (3) An array is 64.

The answer is when one cell contains a number and the other cell contains a string (text value). That’s done here by putting an apostrophe before the number and right aligning the cell. You solve this by using the VALUE(cell_reference) function to ensure that you’re comparing values not unknown data types because there’s no implicit casting resolution in Microsoft Excel and all comparisons are identity operations.

I haven’t checked Open Office or Numbers, but I’ll do that and update this posting with findings.

I put together a post on how to upload to MySQL from a CSV file with dates. It was more or less for my students but one of them was curious how the mega formula worked. As commented, the easier solution is to create a custom format. Oddly, Open Office does support the MySQL default format natively.

Excel doesn’t support the native MySQL date format as a default format mask, which is YYYY-MM-DD, or 2009-06-02 for June 2, 2009. That means you have to convert it from a scalar date to a string or create a custom format mask (see Dmitri’s comment below). If you just shook your head at the term scalar date, maybe a custom format mask is best. However, if you want a programming solution let me explain that Excel supports only three data types. They’re a string literal, a numeric literal, and a formula. Dates in Excel are merely formatted numbers. When the numbers are integers, the date is a date, but when the number has a fractional component, the date is really a timestamp.

Here’s a brief description of the process required to convert a date in Excel into a MySQL date format string literal in a CSV file. You need the following Excel functions:

Date Functions

• The DAY(date) function returns a 1 or 2 digit numeric value for the day of the month, with ranges of 1 to 28, 1 to 29, 1 to 30, or 1 to 31 dependent on the month and year.
• The MONTH(date) function returns a 1 or 2 digit numeric value for the month of the year.
• The YEAR(date) function returns a 4 digit numeric value for the year.

Logical Functions

• The IF(logical_expression,truth_action,false_action) function returns the truth action when the expression is true, and the false action when the expression isn’t true.

MySQL Server

• CONCATENATE(string_1, string_2, ...) glues strings together.
• LEN(numeric_value) function returns the length of a string or number.

MySQL requires that you return an eight character string of numbers. The first four numbers must be a valid year, the fifth and sixth numbers a valid month, and the seventh and eigth numbers a valid day in the context of the year and month provided. Unfortunately, the DAY() and MONTH() functions may return a 1 or 2 digit value. That can’t happen in the CSV file’s string for a date, so you use the IF() and LEN() functions to guarantee a 2 digit return value.

Here are the examples that guarantee 2 digit day and month values, assuming that the base date is in the A1 cell. The concatenation of a "0" (zero between two double quotes) or the "" (two double quotes or a string null) ensures the number data types become strings.

=IF(LEN(DAY(A1))=1,CONCATENATE("0",DAY(A1)),DAY(A1))
=IF(LEN(MONTH(A1))=1,CONCATENATE("0",MONTH(A1)),MONTH(A1))

A zero is placed before the day or month when the logical condition is met, which means the day or month value is a single digit string. A null is place before the day or month when the logical condition isn’t met, which means the day or month value is a two digit string. There’s only one problem with these mega functions. They return a number.

The year calculation doesn’t require the explicit casting when you concatenate it with the other strings because it is implicitly cast as a string. However, it’s a better practice to include it for clarity (most folks don’t know about the implicit casting behaviors in Excel).

=CONCATENATE(YEAR(A1),"-",IF(LEN(MONTH(A1))=1,CONCATENATE("0",MONTH(A1)),MONTH(A1)),"-",IF(LEN(DAY(A1)) = 1,CONCATENATE("0",DAY(A1)),DAY(A1)))

As Goodwin reported in a comment, there’s an easier way that I missed. You can simply use the TEXT function when the source column is a valid serialized date value.

=TEXT(A1,"YYYYMMDD")

You can see the full MySQL import from CSV in the previous post. Naturally, you may want to copy and paste special the value before creating the CSV file. Also, don’t forget to delete any unused columns to the right or rows beneath because if you don’t your file won’t map to your table definition.

You have one approach to uploading CSV file data into a MySQL database. You use the LOAD statement. There two caveats about using the LOAD command, and they are:

• You must convert dates to the default MySQL format – YYYYMMDD, or the four digit year, two digit month, and two digit day.
• You can’t use AUTO_INCREMENT from the source to the loading table.

It’s untrue that you must input dates as strings and convert them in the database. However, you can do that if you like. I’d suggest you open the file in Microsoft Excel, then create a column with the correct data format. You can do that by creating a custom format, or with a formula. I figure the custom format is easiest but sometimes a formula is necessary for applications.

=CONCATENATE(CONCATENATE("",YEAR(A1)),IF(LEN(MONTH(A1)),CONCATENATE("0",MONTH(A1))),IF(LEN(DAY(A1)) = 1,CONCATENATE("0",DAY(A1)),CONCATENATE("",DAY(A1))))

You should note that loading a date at the end of a line has problems in MySQL. The easiest fix that I’ve found is to place a comma at the end of each line. In a Microsoft world, that eliminates the need for the \r from the LINES TERMINATED BY clause.

While this shows a trivial amount of data, here’s a CSV file to use in the test case. Create a directory like Data off your base logical drive or mount point. Then create a file named transaction_upload.csv and put the following data set in it.

20090102,5
20091231,5
20091128,5
20090616,5

Create an importing.sql file in the C:\Data folder with the contents below. It’s only a recommendation but generally import tables aren’t useful in between uploads, and that’s why I create the table as an in-memory table. In-memory tables are dropped when you recycle (boot) the MySQL instance.

-- Conditionally drop the table.
DROP TABLE IF EXISTS transaction_upload;
 
-- Create the new upload target table.
CREATE TABLE transaction_upload
( transaction_date   DATE
, transaction_amount FLOAT ) ENGINE=MEMORY;
 
-- Load the data from a file, don't forget the \n after the \r on Windows or it won't work.
LOAD DATA INFILE 'c:/Data/mysql/transaction_upload.csv'
INTO TABLE transaction_upload
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';
 
-- Select the uploaded records.
SELECT * FROM transaction_upload;

Before you connect as anything other than the root account, you’ll need to grant the global FILE privilege to the targeted user. This is true because you’ve not qualified a LOCAL file in the import statement. Users that have all privileges on a given database also have read-write access to LOCAL tables, which is read write to client-side files. When the LOCAL key word is omitted, you’re instructing a read from the server by the client tool. You must therefore grant a global permission to enable a connected user can call a server-side file.

If you want to import without granting any additional global permissions, you can rewrite the statement like this:

LOAD DATA LOCAL INFILE 'c:/Data/mysql/transaction_upload.csv'
INTO TABLE transaction_upload
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

If you don’t want to rewrite the statement, you’ll get the following error:

ERROR 1045 (28000): Access denied FOR USER 'student'@'localhost' (USING password: YES)
Empty SET (0.01 sec)

C:\Data> mysql -uroot -pcangetin -P3306

mysql> GRANT FILE ON *.* TO 'student'@'localhost' IDENTIFIED BY 'student';

C:\Data> mysql -ustudent -pstudent -P3306

Run the script:

mysql> \. importing.sql

Then, you should see the following:

+------------------+--------------------+
| transaction_date | transaction_amount |
+------------------+--------------------+
| 2009-01-02       |                  5 |
| 2009-12-31       |                  5 |
| 2009-11-28       |                  5 |
| 2009-06-16       |                  5 |
+------------------+--------------------+
4 rows in set (0.00 sec)

Another quick tidbit, dates can be tricky when they’re the last column in the file. The best solution is to put a comma at the end of each string. If you put the comma at the end of each line, you can also dispense with the \r on the Windows platform.

Somebody tweeted tonight and asked how to left pad a string in Microsoft Excel 2007. I thought it was an interesting question, and replied. Then, I thought oops … it should be on the blog.

You can use a combination of the REPLACE and REPT (believe it or not that’s the repeat function). The first step to left pad requires you to master the REPLACE function. It takes four actual parameters. The first is the old or base string, which is typically a cell reference. The second is the starting position, the third is the length of the base string to replace, and fourth the new string. The following example assumes you want to replace the whole string with a white space before the original string.

=REPLACE(A1,1,LEN(A1)," "&A1)

If you don’t like using the ampersand to concatenate, you can replace it with the CONCATENATE function, like this:

=REPLACE(A1,1,LEN(A1),CONCATENATE(" ",A1))

While more compact, the ampersand does the trick and ultimately saves space for complex mega formulas. The next step requires you learn how to use the REPT function.

The REPT function takes two parameters, which are the string you want to repeat and the number of times you want to repeat it. Assuming that you expect all strings to be smaller than twenty, you can use the REPT function this way to create a dynamic padding of physical white space.

=REPT(" ",20-LEN(A1))

When you put the concepts together, you’d get the following command to dynamically replace a string in cell A1 with a 20 character cell that is right aligned. The new value would be in the cell where you put this formula.

=REPLACE(A1,1,LEN(A1),REPT(" ",20-LEN(A1))&A1)

This lets you left pad a string, which I suppose is great when you want to output a flat position specific file. I’d prefer a CSV and a more robust programming environment but I hope it helps those who want to do it.

As Andy pointed out, you can simplify it with this:

=REPT(" ",20-LEN(A1))&A1

As an update to the original blog post, here’s a Visual Basic for Applications (VBA) User Defined Function (UDF) that would provide you with an add-in function to perform this task. It’s designed to LPAD numbers or text, and always return text. You can find how to develop and deploy Excel VBA UDFs in this more recent blog post of mine.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

' The function takes any input as a cell and manages numbers different than strings.
Function LPAD(cell As Variant _
             , Optional offset As Variant = 0 _
             , Optional padWith As String = "0")
 
  ' Return variable.
  Dim returnString As String
  Dim whiteSpace As String * 1
 
  ' Reports a meaningful error when a non-numeric offset parameter is provided as the offset call parameter.
  If Not IsNumeric(offset) Then
 
    ' This means the function is called incorrectly but suppresses a #VALUE! error.
    MsgBox ("Offset should not be a non-numeric value of [" + offset + "]!")
 
  ' Dynamically assign the offset for the optional offset variable.
  ElseIf offset = 0 Then
    offset = Len(cell)
  Else
    ' This allows you to see the offset value when it is provided as a number.
    ' MsgBox ("offset is [" + CStr(offset) + "]")
  End If
 
  ' Assign default value.
  whiteSpace = " "
 
  ' Ensure size is at least the length of the cell and padding value.
   If IsNumeric(offset) And Len(cell) > offset Then
    offset = Len(cell) + Len(padWith)
   End If
 
  ' Assign default padding value when cell is a number.
  If IsNumeric(cell) And IsNumeric(padWith) Then
    padNumberWith = CInt(padWith)
  Else
    padNumberWith = 0
  End If
 
  ' Convert to string when numeric, use padWith text value when not null and whitespace if null.
  If IsNumeric(cell) Then
    returnString = Application.Rept("0", offset - Len(Application.Text(cell, padNumberWith))) + CStr(cell)
  ElseIf padWith <> "0" Then
    returnString = Application.Rept(padWith, offset - Len(cell)) + cell
  ElseIf padWith = "0" Then
    returnString = Application.Rept(whiteSpace, offset - Len(cell)) + cell
  Else
    returnString = Application.Rept(" ", offset - Len(cell)) + cell
  End If
 
  ' Return formatted string.
  LPAD = returnString
 
End Function

The formula for a number is different than it would be natively in Excel. That’s because some behaviors are implicitly provided. The equivalent function in Excel is:

=REPT("0",20-LEN(TEXT(A6,"0")))&A6

Microsoft Excel performs the LEN() function implicitly if you leave it out. For example, this works the same as the more complete formula above.

=REPT("0",20-(TEXT(A6,"0"))&A6

If you forget the LEN() function call in the VBA module, it raises an Excel Error 20150. This error is suppressed by a #VALUE! error. That error is returned because an error message can’t be concatenated with a string. Once you identify where the error occurs you can enclose it in a CStr() function call. The CStr() function explicitly casts the Err.number value to a string, which is then returned to the worksheet in the cell where you’ve called the function.

All that being said, it’s not nearly that complicated. You can provide the LPAD UDF in a simpler function that doesn’t use the built-in workbook functions. All you need to know is how to us the String() function, like this:

Function LPAD(padding AS Variant, character AS String, cell As String)
 
  ' Define and assign a default value, which must be a string data type.
  Dim character_default As String * 1
  character_default = " " 
  Dim number_default As String * 1
  number_default = "0" 
 
  ' Check if you've got adequate values to proceed.
  If IsNumeric(padding) And IsNumeric(cell) Then
    If IsNumeric(character) Then
      LPAD = String(padding, character) + cell
    Else
      LPAD = String(padding, number_default) + cell
    End If
  Else
    If IsNumeric(character) Then
      LPAD = String(padding, character) + cell
    Else
      LPAD = String(padding, character_default) + cell
    End If
  End If
 
End Function

You would call this UDF with any of the following function calls. You skip the last parameter because its an optional parameter.

Parsing strings isn’t the easiest thing to do in Excel 2007 or Excel 2008 but it is an important thing to know how to do. You’ll learn how to parse a set names into first, middle and last names. You can find the data set for these examples at the bottom of the blog post.

=LEFT(A1,7)

=FIND(" ",A1,1)

=LEFT(A1,FIND(" ",A1,1),1)

=IFERROR(FIND(" ",A1,1),A1)

=IFERROR(LEFT(A1,IFERROR(FIND(" ",A1),A1)-1),A1)

=RIGHT(A1,5)

=FIND(" ",A1,1)+1

=FIND(" ",A1,FIND(" ",A1,1)+1)

=LEN(A1)

=LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+1)

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+1))

=IFERROR(RIGHT(A1,LEN(A1)-IFERROR(FIND(" ",A1,FIND(" ",A1)+1),FIND(" ",A1))),A1)

=FIND(" ",A1,1)+1

=FIND(" ",A1,FIND(" ",A1)+1)

=FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)

=MID(A5,FIND(" ",A5)+1,IFERROR(FIND(" ",A5,FIND(" ",A5)+1)-FIND(" ",A5)-1,0))

=IFERROR(MID(A5,FIND(" ",A5)+1,IFERROR(FIND(" ",A5,FIND(" ",A5)+1)-FIND(" ",A5)-1,0)),"")

Original Name         First Name  Middle Name  Last Name  Resorted Name
Joseph Smith          Joseph                   Smith      Smith, Joseph 
Brigham Young	      Brigham                  Young      Young, Brigham 
John Taylor	      John                     Taylor     Taylor, John 
Wilford Woodruff      Wilford                  Woodruff   Woodruff, Wilford 
Lorenzo Snow          Lorenzo	               Snow       Snow, Lorenzo 
Joseph F. Smith       Joseph      F            Smith      Smith, Joseph F
Heber J. Grant        Heber       J            Grant      Grant, Heber J
George Albert Smith   George      Albert       Smith      Smith, George Albert
David O. Mckay        David       O            Mckay      Mckay, David O
Joseph Fielding Smith Joseph      Fielding     Smith      Smith, Joseph Fielding
Harold B. Lee         Harold      B            Lee        Lee, Harold B
Spencer W. Kimball    Spencer     W	       Kimball    Kimball, Spencer W
Ezra Taft Benson      Ezra        Taft         Benson     Benson, Ezra Taft
Howard W. Hunter      Howard      W            Hunter     Hunter, Howard W
Gordon B. Hinckley    Gordon      B            Hinckley   Hinckley, Gordon B
Thomas S. Monson      Thomas      S            Monson     Monson, Thomas S

Since Excel 2007 and 2008 don’t support a direct exclusive or – =XOR() – function, you’d think the logical operators could do it. Something like this:

=AND(NOT(AND(A1,B1)),OR(A1,B1))

It surprised me when it didn’t work. Curious, I figured out why it wouldn’t work. The AND() function doesn’t work as qualified in the help file.

The AND() function only works correctly when both cell values are not null values. It returns a Boolean true when one value is true and the other value is null, which was quite a surprise.

Fortunately, the IF() function works correctly. If you check a cell with a null value, the IF() function finds it false. That means you can wrap the cells in the IF() function, like:

=AND(NOT(AND(IF(A1,TRUE(),FALSE()),IF(B1,TRUE(),FALSE()))),OR(IF(A1,TRUE(),FALSE()),IF(B1,TRUE(),FALSE())))

Naturally, you can substitute the respective string literal for either the TRUE() or FALSE() function calls. You could also rewrite the formula to check for a null value by using two double quotes. By the way, it’s fun to note that the TYPE() function recognizes a null value cell as a number data type.

It’s ashame it doesn’t work the way you’d think it should.