MacLochlainns Weblog

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.

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, which is YYYYMMDD, or 20090602 for June 2, 2009. That means you have to convert it from a scalar date to a string. If you just shook your head at the term scalar date, 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)),CONCATENATE("",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 implicity 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(CONCATENATE("",YEAR(A1)),IF(LEN(MONTH(A1)),CONCATENATE("0",MONTH(A1))),IF(LEN(DAY(A1)) = 1,CONCATENATE("0",DAY(A1)),CONCATENATE("",DAY(A1))))

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.

Function LPAD( cell As Variant _
             , offset As Integer _
             , Optional style As String = "0")
 
  ' Return variable.
  Dim returnString As String
 
  ' Ensure size is at least the length of the cell and padding value.
  If IsMissing(offset) Or 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.
  If IsNumeric(cell) Then
    returnString = Application.Rept("0", offset - Len(Application.Text(cell, style))) + CStr(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.

How you add an image to a comment isn’t exactly what I’d call easy but it is straightforward. One of my students noted that a tip in Microsoft Office Excel 2007 Bible didn’t provide the magic to insert an image.

I’d have to say that it did but it didn’t. Basically, the general idea is there but a step of magic requires something special. You need to place your cursor on the highlighted edge of an editable comment before right clicking to launch the context menu. If your cursor is on the text portion of the comment, the context menu operates differently. The Format Comment … menu choice launches a different set of options dependent on your location in an editable comment.

Hoping to help my students and others learn the magic, here are some steps.

I remember when the Mac version of the office suite was like the Windows version. It’s so disappointing to find more shortfalls between the two. Here is a dialog that appears in Excel 2008 when you try to open an Excel 2007 workbook that includes the powerful new table components:

A year after the release of Excel 2007, and Microsoft didn’t include structured references, Excel tables, or table styles … What were they thinking about up in Redmond? Were they thinking that they should release something less on a Mac to sell Windows Vista? It couldn’t be that, could it?

I was doing some cross product testing between Excel 2007 (PC) and Excel 2008 (Mac) and got a big surprise! The number representing a date differs by 1,462. Excel 2007 starts using integers at 1 equal to 1-Jan-1900, but Excel 2008 starts with 0 equal to 1-Jan-1904. It clearly means that dates should be uploaded as formatted strings rather than numbers when loading them into a database. That also appears to mean any shared computations linked to dates across a collection of PC and Mac computers fails unless you account for the difference. I’ve updated that earlier post on what’s true with the details.