Category Archives: Pragmatic Programming

A Simple Trick for Referencing Data Between Worksheets In Microsoft Excel

Posted on by Posted in Pragmatic Programming Leave a comment

pastelinkI don’t work in Microsoft Excel often, and when I do, the documents tend to be pretty simple. Most of my work with Excel involves collating tabulated data and then publishing it a a Web-compatible format (the HTML <table> still has its uses).  I’ve never had any actual training in Excel (or any other spreadsheet software), but I’ve picked up enough to do the job at hand, and every now and again, I pick up a trick that makes working with data across Excel worksheets much easier.

One of the most tedious activities I’ve faced when working with Excel is to reference data between worksheets. I usually do this manually typing out the reference in the cell.  A typical cross-worksheet reference might look like this:

=’Another Worksheet’!$E$29

As you might have guessed from reading other posts in this blog, I have a tendency to commit typographical errors about every other sentence, so manually typing out a reference is a tricky proposition.

Fortunately, I picked up a new trick (and several others) for copying and pasting data references between sheets.

Rather than typing out the reference by hand, you can speed up your cross-worksheet functionality by using the following tip:

  1. Go to the sheet containing the data you want to carry over to the new sheet.
  2. Highlight the cell containing your target data.
  3. Press CTRL+C (or right click and select Copy).
  4. Go back to the sheet where you want a copy of the data to appear.
  5. Select the cell where you want to “paste” the data.
  6. Press ALT+E to bring up a floating dialogue that titled: “Office 20xx access key: ALT, E”.
  7. Press S to bring up the Paste Special menu.
  8. Press L to paste the link into the the cell.

The same task can also be accomplished (after coping the target to the clipboard) by highlighting the cell where you want referenced data to appear, clicking Edit, clicking Paste Special, and then clicking the Paste Link button.

I think the keyboard “hotkey” method is faster, but both methods are much faster than typing out the reference manually.

If you want to create references to a bulk of cells instead of copy-pasting all the values this is the option for you. Just use ALT+E S L to create an automatic reference to copied range of cells.

Troubleshooting As A Development Technique

Posted on by Posted in Blog, Pragmatic Programming Leave a comment

Pretty much all of my development activities start out with the question: “Can the website (or PC, or Smart Phone or toothbrush) do this form me?” My mind’s initial response is flood of brainstorming, ranging across choosing the best programming language and platform, which development philosophies to utilize, how this project fits into the organization’s business needs, how the project is funded, what is the ROI, and on, and on, and on.

To bring myself back in check, and to begin actually developing a solution, I’ve learned to simply rephrase the question as a a problem report.”Can the website (or PC, or Smart Phone or toothbrush) do this form me?” becomes: “When I do this to the website (computer, Smart Phone, etc.), it doesn’t do this.”

Rephrasing this “functional requirement” as a problem allows me to better identify the inputs the user intends to provide, and the outputs the user intends to receive.  Of course, there are usually many different inputs and outputs, but by framing to project in terms of a problem to troubleshoot, I can begin working the various input/output pairings (or clusters, as the case may prove to be).

The most simple an effective troubleshooting technique is isolate a problem into a series of events that should work, and then to work from each end, alternating every few steps, towards the middle of the series until the problem is found.

In the case of writing code, the first steps are pretty easy: I start with one expected input and one expected output, and then I create the necessary series in the middle to create the expected output when the input is provided. I progressively add inputs and outputs to the code, increasing the complexity until it gives all of the expected outputs based on the expected inputs (and doesn’t come crashing down when unexpected inputs are provided, but that’s a different article).  Each link in the development process will have its own problem cases that need to be resolved, but by working progressively to resolve each failure, the resulting code should be extremely robust and resistant to errors.

If you’re familiar with test-driven development, then this is probably starting to sound familiar. Test-driven development requires developers to create a test for a new feature before beginning development. Approaching coding as a troubleshooting process automatically requires the developer to create a test case by stating the failure state. From there, the coding is simply a matter of preventing the failure case from occurring.

Broken Background Images In Internet Explorer

Posted on by Posted in Blog, Pragmatic Programming Leave a comment

I should take it for granted that I’m going to have to resort to some sort of chicanery to get my CSS to work in Internet Explorer. Today, my background images weren’t appearing in certain elements on IE but were working beautifully on Chrome, Firefox, Safari, Opera, and every flavor of Webkit I could find.

Here’s my original CSS (yes, I’m working in WordPress again):

.widget-title {
backround-image: url(/wp-content/themes/mytheme/widget-header.jpg);
}

Easy, right?

Over the years, my hat for Internet Explorer has evolved into a warm, constant disdain. I’ve accepted it, accepted that I hate it, and accepted that while it may improve on some of the traits that irk me, it will introduce new traits for me to loathe.

Fortunately, this CSS background image problem as been around since about IE 5.5, so with a quick look on Google, I found a solution using filters. Here’s the new code:

.widget-title {
backround-image: url(/wp-content/themes/mytheme/widget-header.jpg);
filter: progid:DXImageTransform.Microsoft.AlphaImageLoader(
src='c',
sizingMethod='scale');
-ms-filter: "progid:DXImageTransform.Microsoft.AlphaImageLoader(
src='/wp-content/themes/mytheme/widget-header.jpg',
sizingMethod='scale')";
}

So, after only half an hour of banging my head on the wall, my CSS background images work.

Simple Way to Get a Domain from an Email Using T-SQL

Posted on by Posted in Blog, Pragmatic Programming Leave a comment

One of my recent projects required me to create a Microsoft SQL Server view that displayed (among other information) and email address and its underlying domain. Microsoft SQL Server and T-SQL provide a few simple functions that, when used together, make this a fairly easy task.

So here’s the T-SQL example (with the important stuff highlighted):

SELECT EMAIL,
RIGHT(EMAIL, LEN(EMAIL) - CHARINDEX('@', EMAIL)) AS 'DOMAIN' 
FROM MYTABLE

Parsing text inside a SQL Query can slow things down if you’re dealing with a large dataset, so use this code snippet with caution. If the application allows it, it’s generally a better idea to perform this kind of parsing in the application layer. ColdFusion, PHP and JavaScript provide comparable functions to T-SQL’s RIGHT, LEN and CHARINDEX.

If you’re feeling brave, you can also try pure RegEx rather than use native functions.

Replacing Long Strings In SQL Exports and Other Large Text Files

Posted on by Posted in Blog, Pragmatic Programming Leave a comment

I’m migrating a web forum from one MySQL server to another, and it’s usually a straight-forward task consisting of exporting the data from one server importing an other server using phpMyAdmin.  This forum has been particularly active, so the SQL dump file containing the discussion table is almost two gigabytes. This wouldn’t be such a big deal if I were just importing into an identical database, but “business requirements” dictate that I change the name of the database.

Normally I would use Find/Replace in Dreamweaver, but being the bloated application it is, it kept throwing “out of memory” errors at me each time it hit the discussion export file. I suspected Notepad++ wouldn’t fare much better, so I started to Google for a solution.

A lot of options came up for Linux and Perl (which has always been awesome at parsing text), but I really didn’t want to have to move the file off my Windows 7 workstation over to the LAMP virtual server and just add more complexity to the confusion.

So I hit Google again with with a modified search (I just added Windows). I popped open a few tabs and browsed through a couple of options that would require yet-another GUI program installed on my machine. Some looked promising, but shareware always comes with a price, so I started digging into some of the more “hardcore” alternatives.

StackOverflow is one of my absolution favorite developer resources, and when I saw the link to “How can you find and replace text in a file using the Windows command-line environment?“, I knew my chances were good for finding a solution. StackOverflow contributor Mile Schall proposed a solution using Windows Power Shell, which proved to work extremely well.


First, anyone who isn’t familiar with Windows PowerShell should get familiar fast. It’s major upgrade over the old command prompt, gives access to some nifty .NET functionality, and will generally make life easier for anyone working with Windows Servers and Clients.

Following the StackOverflow advice, I launched PowerShell on my Windows 7 machine by going to:

START > All Programs > Accessories > Power Shell > Power Shell

I then navigated to the directory containing my SQL dump file, and entered the following command (of course the target text strings and file names have been changed for privacy):

1

   Get-Content SQLexport.sql | ForEach-Object { $_ -replace "replace this string", "with this string" } | Set-Content SQLexport2.sql

After a few minutes, the PowerShell prompt returned a new “SQLexport2.sql” file appeared in the directory.

This snippet has worked great for my large SQL files, and I suspect it will also work well for other large text-based files.

Now, the next trick is to get by the 2 MB file limit in phpMyAdmin so I can import the updated 2 gigabyte SQL dump file back into MySQL.