Pomodoro Timer
Written by: James Climer (http://www.climers.com)

• Pomodoro.exe (Recommended)
• Source code
• Promodoro Worksheet (Excel .xls spreadsheet that I use as a template)

I've decided to start using the Pomodoro technique in hopes of making better use of my time.  After trying it out for a couple of days I've found it actually helps quite a bit in getting stuff done.  I decided to make a little timer for windows because I couldn't find a simple one to download (at least not from a site that I trust).

The Pomodoro Technique™ was developed by Francesco Cirillo and can be found online at http://www.pomodorotechnique.com/.  The Pomodoro Timer is a Microsoft Windows Application written in .NET (3.0 Framework) to be used with the Pomodoro technique as a tool to help track time.  It is a very simple application but serves its purpose well.  The time and audio can be configured via the Promodoro.exe.config file included with the application.  The config file also includes instructions in the comments to help make these modifications. 

I hope you enjoy this tool and that it helps you make better use of your time.

-James

This problem would have been a really good homework assignment for beginning programming.  It requires a little explination, though I will try to brief:

I received an Excel spreadsheet containing various forms of revenue for a company.  I was also given a summary report from a different system which was supposed to represent the same data.  However, the categorizations of the data on the report were different from those on the spreadsheets and the totals weren't adding up.  So I had to take the totals from the report and figure out which cells on the revenue spreadsheet were used to make that total.

To do this, I had to come up with every combination of the cells, get a sum of each combination, and see which ones matched the value I was looking for.

The VBA code below will allow you to select a column of cells on a spreadsheet, right click to activate the program, enter a value to be searched for, and it will return all combinations of the cells that can be summed to make that value.

What was most difficult about this, was coming up with an algorithm for giving me all possible combinations of the cells.  Plus, I wanted to search for the smallest number of returning cells first before searching for combinations containing all cells.  The GetComb function in the code at the bottom of this article works in the following manor:

Given the Cells: A, B, C, D, E, I started with looking for the all single items (in the first column) which required just 1 pointer value. I moved that pointer from left to right (follow it going down the column).  Next, I found all combinations of 2 items (in the second column) which required 2 pointers.  I moved the last pointer (labeled 2) step-by-step to the right.  When the last pointer was all the way to the right, I stepped back to the first pointer and stepped it one to the right, and moved the last pointer to the next consecutive place (row:5 column:2).  I then moved the last pointer again, step-by-step to the right.

This is the algorithm, repeated until all combinations are found.

Here is the VBA code I wrote to do this, Enjoy!

More...

I had a situation at work where a coworker needed to export a spreadsheet to a fixed width pnr file.  The resulting file was truncated at 240 characters, which I believe is part of the pnr standard (I haven't actually looked that up or anything).  So I wrote a script to export a spreadsheet to a fixed width .pnr file regardless of the number of columns.

To make it easier to code, I prompt for the number of columns and rows and it uses a common dialog control to get the destination file path.  If you don't have the common dialog control you'll get an ActiveX error.  The whole section prompting for file location can be replaced with an InputBox function, but that is neither here nor there.

Also, for your Excelling pleasure, I added a PadRight function since I couldn't seem to find one in Excel 2003. 

Just add the following script to your worksheet (Alt+F11 and double-click Sheet1 usually, paste and click the "play" button)

Enjoy: More...

Today I encountered several data files that were created from a mainframe.  The files were in ASCII format, but contained several non-printable ASCII characters (those with values from 0 to 31).  So I wrote the following uitility to replace those invalid characters with spaces.  It seemed like a really good utility, so I thought I would share.

Download  (10Kb - Requires the .NET 3.5 Framework)

Source Code:

More...

I needed to find some data quickly in a database that had around 300 tables in it.  I knew the tables I was looking for would have a lot of rows in them so I wrote the following script to return a list of all of the tables with their row counts.  I found it useful, so I thought I would share.

-- Create variables to hold table info
DECLARE @tableName varchar(255),
        @rowCount int;

-- Create a temp table to hold the results
CREATE TABLE #TableCounts
    (TableName VARCHAR(255),
    TableRowCount int)

-- Step through each table using a cursor
DECLARE tableName_Cursor CURSOR FOR
    SELECT table_name FROM information_schema.tables ORDER BY table_name;

OPEN tableName_Cursor;

FETCH NEXT FROM tableName_Cursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN

   -- Create a dynamic SQL statement to insert a
   -- record into the temp table
   EXEC ('INSERT INTO #TableCounts (TableName, TableRowCount) (SELECT ''' + @tableName + ''' TableName, Count(*) TableRowCount FROM ' +  @tableName + ')');
   FETCH NEXT FROM tableName_Cursor INTO @tableName
END

CLOSE TableName_Cursor;
DEALLOCATE TableName_Cursor;

-- Display the contents of the temp table
SELECT * FROM #TableCounts ORDER BY TableRowCount DESC;
DROP TABLE #TableCounts

I ran into an issue trying to restore an MS SQL Server 2005 database on a different server from the one that was originally backed-up.  Using the Microsoft SQL Server Manager (MSSM), I just kept getting an error when I would try.  So I used the following script:

--Note, replace MyDatabase with the database name.  If you don't know it, try restoring using the MSSM, it will tell you the databases in the back-up, it just won't let you restore it

USE master

GO

RESTORE DATABASE MyDatabase
    FROM DISK = 'c:\Temp\MyDatabase_Backup.bak' WITH
    MOVE 'MyDatabase' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDatabase.mdf',
    MOVE 'MyDatabase_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDatabase_log.ldf',
    REPLACE

GO

I was sent a webpage from a coworker who was stumped as to why the page displayed correctly in IE 7+ and FireFox but not in IE6.  After looking around I noticed the problem was around a FORM tag.  I found the following solution on Snipplr.com.

The FORM tag seems to have a bug in IE6 where it doesn't automatically resize to fit the parent container.  So I added a style tag to force the display to be inline:

<FORM style="display:inline;">

Hope this helps.

I just had a request to do this and it required me to look up a couple of things, so I thought I would share.

There are two ways to conditionally change the background color in an Excel spreadsheet (possibly more but I can only think of two).  One is to use a VBA macro, which I like but it makes it difficult to share with non-techie folks.  The other is using built-in Excel functions which makes everybody happy, so I went that route. 

The following is a way to change the cell background color based on the value of a different cell:

I was asked to evaluate a cell based on its value being 0 to 2.5 is "Low Risk", 2.5 to 3.5 is "Medium Risk", and 3.5 to 5 is "High Risk".  The Low Risk cell should have a yellow background, the Medium Risk should be green, and the High Risk should be red.

I accomplished this in two parts.  First, figure out the string to display based on the value (which was stored in the A1):

=IF(A1 <= 2.5, "Low Risk", IF(A1 > 3.5, "High Risk", "Medium Risk"))

Next, the background color.  Excel has a built-in conditional formatting for changing this, the only problem is it is limited to three conditions.  It just so happens three is all I need.  If I had more than three, I would have had to go the VBA macro route. 

Using the Format > Conditional Formatting wizard I setup three conditions where the value of the cell containing the formula was either "Low Risk", "Medium Risk" or "High Risk", each one having the respective background color.

Recently at work I was given an MS Access 2003 database and was asked if I could extract a list of table names and field names from it.  So I wrote the following script in a module and called it from the immediate window and was able to get the list.  It was a rather simple script, but I thought it might help somebody else. 

This also demonstrates how to print to a file from MS Access.

So here it is:

Public Sub GetTablesAndFields(Optional OutputFile As String)
    Dim TableIndex As Integer
    Dim FieldIndex As Integer
    Dim TableName As String
    Dim FieldName As String
    Dim bOutputToFile As Boolean
    Dim fnum As Integer
       
    If Not IsEmpty(OutputFile) And OutputFile <> "" Then bOutputToFile = True
   
    If bOutputToFile Then
        'Setup the output file
        fnum = FreeFile
       
        Open OutputFile For Output As #fnum
    End If
   
    For TableIndex = 0 To (CurrentDb.TableDefs.Count - 1)
        'Get the table name
        TableName = CurrentDb.TableDefs(TableIndex).Name & vbCrLf & "======================="
       
        If bOutputToFile Then
            Print #fnum, TableName
        Else
            Debug.Print TableName
        End If
       
        'Display the fieldnames
        For FieldIndex = 0 To (CurrentDb.TableDefs(TableIndex).Fields.Count - 1)
            FieldName = CurrentDb.TableDefs(TableIndex).Fields(FieldIndex).Name
           
            If bOutputToFile Then
                Print #fnum, FieldName
            Else
                Debug.Print FieldName
            End If
        Next
       
        'Place space between tables
        If bOutputToFile Then
            Print #fnum, vbCrLf
        Else
            Debug.Print vbCrLf
        End If
    Next
   
    MsgBox "Done"
   
CleanUp:
   
    If bOutputToFile Then
        Close #fnum
    End If
End Sub

I must first clarify that I have not found a good solution for this.  But since I can't find one, I thought I'd start a blog entry to address this issue.

Oracle has many tutorials in their Oracle By Example (OBE) web pages that use the Schema Samples: HR, OE, ...  .  These schemas are installed by default on the Enterprise Install of Oracle.  However, with the Express Edition (XE) only HR is installed.

Nine times out of ten this is fine.  But recently I needed to learn Oracle BI, which requires the other schemas to go through the tutorials.  This means I need to install the other schemas on the express edition.  Well, Google let me down, so I'm writing this to keep up with my experiences.

Currently, I'm installing the Enterprise Edition.  This does not seem to me to be a reasonable solution.  Supposedly the scripts are on the companion cd to the Oracle Universal Install, but I couldn't find it on there, and I couldn't install the companion tools on the XE version.

Anybody reading this have a solution?  And if not, if you find one can you come back and leave a solution in the comments?

-James