Missing leading zeroes in XLS

If you are not careful when importing data into Excel (XLS), you often see leading zeroes be removed as XLS tries to be smart about the data it imports. However, this causes issues in Zip codes, SSNs, account numbers, etc. E.g. east coast or Puerto Rico Zip codes start with one or two leading 0s, and suddenly ZIP code 00555 turns into 555. This of course creates all sorts of fun issues in comparison, or if data is used as input into other processes, etc.

To append leading zeroes to a field, simply add a new column with the following formula, and then copy/paste the new column as values to save it. The formula works on any number of leading zeroes missing. Note that “5” in the formula below is the number of digits you expect in the correct length of the field (e.g. 5 for ZIP)

 A       B
ZIP   Fixed_ZIP
555   =RIGHT(“00000” & A2,5)

To add extra protection in case there are blank ZIP codes, simply change the formula to:
=IF(TRIM(A2)="","",RIGHT("00000"&A2,5))

FYI, the simplest way to avoid the leading zeroes being removed is to import the field as text. This is done in the "Text Import Wizard" in XLS that launches automatically when you open a text file. In step 3, simply select set the ZIP fields as text.

Nifty,
Thomas

Grep for value in a column

When doing data analysis, you often need to find records with a given value in a given column, but you want to return the whole line. The grep command will be able to do some of that but has a number of drawbacks. See older post about that at
http://niftypctricks.blogspot.com/2008/08/greping-for-column-value.html

A much simpler (and cleaner) way is to use the awk or gawk command from the command line. Note that the awk/gawk command line may look slightly different depending on whether you are on Linux, Cygwin, MKS toolkit, DOS, etc.

Assuming we have a sample tab delimited text file named in.txt:
line1 joe smith ca 4085551212
line2 joe carlson az 3334445555
line3 carl smith ny 2049998888
line4 joe smith or 5035551234
line5 mike erwin ca 4159876543
line6 mike erwin CA 4159876543

To find all records when column 5 (phone number) contained the value 555, simply use
cat in.txt | gawk -v FS='\t' 'match($5,"555")' > out.txt

The awk / gawk command "match" returns true if pattern1 ($5, meaning column 5) contains pattern2 (555), and by default that prints the whole line, so no need to explicitly put "print $0" in there. So the code sample above will return line 1, 2, and 4.

Nifty #NerdTip,
/T

Pipe to tab

Converting a pipe delimited file to a tab delimited file should be extremely simple. And many times it is if you run under a pure UNIX environment. But if you are under Windows / DOS (using Cygwin) it can be a little tricky, but there is a simple solution.

To convert a pipe delimited file, simply type:
cat FILE | tr "|" '\t' 

The trick here is that the first argument to the tr command has to use double-quotes. The standard single quotes will not work under DOS if the character you are converting is a pipe.

Nifty,
/T

Text editor tab in browser

This is such a cool and simple trick. Creates a simple text editor / notepad in a browser tab. This is useful for storing various kinds of temporarily data, e.g. when copy / pasting multiple items, etc.

To instantiate, simply create a new tab (in an HTML 5 compatible browser), and type in the following in the URL address bar:
data:text/html, <title>Text Editor</title><body contenteditable>

Store as a bookmark so you have easy access in the future.

Or if you want a more permanent version (uses browser's LocalStorage), type:
a5.gg

Much more details, and many "interesting" incarnations at:
https://coderwall.com/p/lhsrcq

Nifty,
Thomas

DOS prompt output to clip board

This trick is so simple, yet so powerful if you dabble in the DOS Prompt a lot. You can pipe the output from any command to the clipboard simply by piping it to "clip". That;s it. No more copy paste from the line wrapped DOS prompt output.

Example:
dir | clip
will give you the output of the DIR command to the clipboard.

Or if you are running Cygwin or MKS toolkit, and want to get the counts of instances in column 3 of a file
cut -f3 myfile.txt | sort | uniq -c | clip

Nifty,

Mailto fix for Gmail

I love Google. Well, in a nerdy kind-of way. I love search, Chrome, Android, Gmail, Docs, Calendar, Goggles, flight search, Wallet, etc. If it says Google, there is a good chance that I will like the product.

So when on my Windows machine at work (no, we have not upgraded to chrome OS yet), it is handy to be able to click an email address link (i.e. a mailto: link) and have a browser with my Gmail pop up instead of my Outlook popping up. Notice that this will switch to a web browser Gmail for all mailto links whether they are on the web, or in an email inside Outlook.

Two ways to skin the cat: 1) You can either download the Gmail Notifier application and set the default there (look at this Gmail help) , or 2) (even better) you can simply edit one key in the registry yourself. Simply open your favorite text editor (e.g. TextPad), copy the following 3 lines (including quotes and backslashes), and save the file with a .reg extension, e.g. Gmail.reg. Then simply execute the file. You can also manual update the registry using RegEdit, but that is a tad more error prone. Please note that you need to replace USERNAME with your own Windows login name.

For Windows XP, use:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\mailto\shell\open\command]
@="\"C:\\Documents and Settings\\USERNAME\\Local Settings\\Application Data\\Google\\Chrome\\Application\\chrome.exe\" https://mail.google.com/mail?extsrc=mailto&url=%1"

For Windows 7, use:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\mailto\shell\open\command]
@="\"C:\\Users\\USERNAME\\AppData\\Local\\Google\\Chrome\\Application\\chrome.exe\" https://mail.google.com/mail?extsrc=mailto&url=%1"

Now, any email (mailto) link you click on will open up your gmail web.
Nifty.

Searching in Excel and Word

Searching and replacing text and special characters in Microsoft Excel and Word can get, uhmm, a tad complicated. Even for people very familiar with standard regular expressions (RegExp) since Microsoft unfortunately decided to build their own standard rather than using the normal regular expressions. And to complicate things, Microsoft Word and Excel don't use the same wildcards.

So here is a quick recap on how to search and replace in Microsoft Excel and Word. Please ignore all the double quotes as they are only to make the terms stand out.

For both Word and Excel:

• Use "?" (without quotes) to find a single character (a letter, a digit, etc). So to find the words "sat" or "sit", you can search for "s?t". Note that it will not find "shoot" as "?" can only be a single character. The "?" is the same as "." (period) in regular expressions.
• Use "*" (without quotes) to find a any number of characters (letters, digits, spaces, tabs, newlines, etc). So to find the words "shot" or "shoot", you can search for "sh*t". Note that it will also find the string "shot" in the word "gunshot", and it will even find the phrase "show up at" since it starts with "sh" and ends with "t". The is the same as ".*" in regular expressions.

The following only works in Word (not Excel)

• Use "<" and ">" (without quotes) to mark the beginning and ending of a word. So if we only wanted to find the word "show" and not "gunshow" then we can use "<show" to look for a starting word boundary, but of course it would also fine "showing". To only find the word "show" simply use "<show>"  Note that "<sh*t>" may find phrases too as * includes spaces, so it may find "shot" or even "show up at".
• Use "^t" (without quotes) to search for the tab character.  The is the same as "\t" in regular expressions.
• Use "^p" (without quotes) to search for a paragraph mark (i.e. newline).  The is the same as "\n" in regular expressions.
• Use "@" (without quotes) to search for one or more occurrences of the previous character.  So "f@" will find both "of" and "off". The is the same as "+" in regular expressions. 

To search for the wildcard characters themselves (e.g. wanting to find a *), please refer to earlier post.

So thanks to Microsoft for another straight forward implementation :-)

(Somewhat) Nifty

Picture date-time stamp editing

I use Google's Picasa to edit / track my pictures, but I was very disappointed to learn that it does not have a basic tool to edit the date/time stamp of a JPEG.

Simple scenario: Your not-so-technical friend sends you 200 pics taken with their brand new camera. But of course the camera date was set to Jan 01 2001. So now you need to update all the pictures with the correct date in the header.

Solution: Go download a small free command line tool named jhead.
To change the date simply go:
jhead -da NEWDATE-OLDDATE *.JPG

(Looks like their documentation incorrectly specifies the opposite). And try it on a single file first. Their full documentation is here. It calculates the difference between OLDDATE and NEWDATE and applies that difference to all the pictures. So simple and so powerful.

To have Picasa notice the updated JPEG header, you may have to change the date stamp of the file too (not only the JPEG header). To do that:
jhead -ft *.JPG

Nifty.

Vista Command Prompt

So my new Dell Studio 15 laptop is a (great) step down from my old trusted Dell Latitude X300, but it was unfortunately a matter of funds this time :-( Anyway, long story, but I needed to get the "Create a command prompt here" functionality working on my new (Asta La) Vista system. This is the functionality that allows you to right click on any folder icon in Explorer and open a DOS Command Prompt in that folder.

In an earlier blog entry I showed a simple registry hack to do this on Windows XP and it is just as simple to do it on Windows Vista. Simply open your favorite text editor (e.g. TextPad), copy the following 5 lines, and save the file with a .reg extension, e.g. CommandPromptVista.reg. Then simply execute the file. You could also manual update the registry using RegEdit, but that is much more error prone (and slow).

Windows Registry Editor Version 5.00
[HKEY_CLASSES_ROOT\Directory\shell\CommandPrompt]
@="Command Prompt:"
[HKEY_CLASSES_ROOT\Directory\shell\CommandPrompt\Command]
@="cmd.exe /k cd %1″

Nifty.

Links in emails

OK, Joe in sales has just sent you and 16 others in your department yet another 13 MB PowerPoint presentation for everyone to review. And to make things worse, you now keep getting those annoying "your mailbox is over quota" messages from IT. Just another day in the office?

Well, you know that the best way to share documents is to use a link to a shared drive where people can review or modify the documents, but getting those pesky hyperlinks in to your email document just seems like more trouble than it is worth. Easy to see why Joe likes to email the whole presentation every time. On a side note, you can of course use the Microsoft Office feature called document reviewing, but that would just ruin the whole point of my story :-)

So let's assume that you want to to share a document named CoolPres.ppt that lives on the public Z: network drive in your company and that it has a path of "Z:\Sales\Firm A". If you use MS Word as your email editor, then there are 3 ways of getting a link to the presentation included in your email.

1. Use AutoCorrect. Set up an AutoCorrect entry for the Z: drive, so each time you type in Z: it expands to \\myserver\myshare (or whatever your server + share is called). See previous blog entry on how to do this. This is useful if you often refer to the very same network share. However, it is a little more work if the path you want to share has a space in it. To create a link simply type in the link, but as soon as you hit a space, Word will create a hyperlink. Since our example has a space in "Firm A" press undo right after the hyperlink is created and keep typing the rest. Then put quotes around the whole thing, and go to the end of it and press space. In the end, your link will look something like this: \\myserver\myshare\Sales\Firm A\CoolPres.ppt

Yes, as I said AutoCorrect is useful for creating the same link all the time, not so much for typical linking.

2. Use the file:\\ prefix. Simply type in "file:\\" in front of the path to the file. Note that sometimes you will need to put double quotes around the whole string when you have spaces in your file name or path name. So type the following: file:\\Z:\Sales\Firm A\CoolPres.ppt . As you finish typing the file name, Word will automatically turn your string into a hyperlink.

3. Use MS Word hyperlinking. However, the simplest way create a hyperlink to a file in an email, is to use the built-in Word hyperlink feature. Simply press Ctrl-K to get the hyperlink pop-up, locate your document, e.g. Z:\Sales\Firm A\CoolPres.ppt, and press enter. Done.

Please note that the email recipients do not need to have the Z: drive mapped to the same place as you in any of the cases above. The hyperlink you create is actually not to the Z: drive itself, but to the mapped resource (e.g. \\myserver\myshare) instead. You can see this if you mouse over the link you created. However, the email recipients do of course need permissions to read the folder where the shared document lives.

Nifty.

I like either for grep'ing

There are times when you want to grep for multiple different lines from a file. For example, the text file below could be an example of a log file and I'd like to see all the CmdStat entries I had and what Value line followed each of them.

log.txt:
CmdStat=InFlow
Info=More Data and Values
CmdStat=OutStat
Value=-3
CmdStat=Done
Value=42
Notes=end

Well, the first guess of running "grep CmdStat log.txt" followed by "grep Value log.txt" will certainly generate all the right lines, but it will not tell me the where the Value lines are compare to the CmdStat lines. There could be CmdStat lines without Value lines and vice versa.

The correct solution is to use the very useful -e (either) option. Doing a "grep -eCmdStat= -eValue= log.txt" will yield:
CmdStat=InFlow
CmdStat=OutStat
Value=-3
CmdStat=Done
Value=42

which shows me all the CmdStat and Value entries. Note that I used Value= instead of Value so we do not accidentally get lines with the word Value. Always a good idea to grep for a word as unique as possible.

You may of course also combine -e with other useful options, such as -i (case insensitive) and -v (except). So if I want to weed out all the Info and Notes lines from the file, I could do a "grep -v -eNotes= -eInfo= log.txt"

Nifty.

Grep'ing for a column value

A few days ago I needed to get those lines from a file that only contained a certain value in a certain field. To simplify the example, let's say that I have a file containing id, first name, last name, state, and phone number, and that I am only interested in those entries containing a state of California (e.g. ca or CA).

Here is the sample file named in.txt:
line1 joe smith ca 4085551212
line2 joe carlson az 3334445555
line3 carl smith ny 2049998888
line4 joe smith or 5035551234
line5 mike erwin ca 4159876543
line6 mike erwin CA 4159876543

I can not simply do a "grep -i ca in.txt" as I would get all lines containing "ca" even if "ca" occurred in the name fields (e.g. like in carl or carlson). In the above case I would get all lines, except line 4, which is of course incorrect.

If I did "cut -f4 in.txt | grep -i ca" I would get the correct number of results (i.e. 3), but I would only get the 3 "ca" values and not the whole line as I cut everything else away.

So one way to solve the problem (there may be other more clever ones I'm not aware of), is to use the "awk" command instead of the "grep" command.
cat in.txt | awk '{if ($4=="ca") print $0;}'

I think the above is self explanatory even for non-awk folks, the only trick is that $0 shows the entire line. If you only wanted to get certain columns in the output, you could decide to only print $2,$3,$5 for example to get first name, last name and phone number.

If you want to make the solution case insensitive, simply use the awk command "tolower()" in your if statement:
cat in.txt | awk '{if (tolower($4)=="ca") print $0;}'

Nifty.

Multiline AutoCorrect

In an earlier blog entry I showed how the cool Microsoft Word AutoCorrect feature can be used to both increase your typing speed and your spelling accuracy. This time we'll use AutoCorrect to write not a single word or a single line, but multiple lines.

So let's say that you often want to write the following 3 lines:
For more information about AutoCorrect in Word,
please see the nifty tricks at:
http://niftypctricks.blogspot.com

The way to add these lines to an AutoCorrect entry, is simply to write the lines in your Microsoft Word document, then highlight all of them, and press AutoCorrect (ALT+T,A in Word 2003). Simply enter the AutoCorrect key (e.g. multi3), and your multi-line AutoCorrect entry is ready to go. So next time you type multi3, your 3 lines will show up with all the formatting (bold, underline, hyperlinks, etc) of the original lines.

I do want to point out that in case you multi-line text is more part of your email signature than just a multi-line text, then you really should use an email signature instead (assuming that you are using Word as your email editor). So if you sometimes want to write:
Regards,
Michael E.
Master of AutoCorrect and Signatures

Then you should create another email signature in MS Word with the desired text in it. You can then right-click on your email signature at anytime and switch it to the desired signature.

Nifty.

Nifty Regular Expressions (RegExp)

I personally use TextPad as my default text editor. There are probably some much fancier ones out there, but TextPad has all the features that I demand, like great regExp support, vertical cut/paste, and a few other nifty features. It is free to download, and only something like $15 to buy. Well worth it. After all, that is not much more than that venti Caramel Macchiato that you normally order. And TextPad has great help on regular expression. I strongly encourage you to read their regExp help. And by the way, if you try TextPad, then I would suggest that you first change these 2 settings:
1) Under Preference/General, set Context Menu, so you can quickly send any file to TextPad
2) Under Preference/Editor, set Microsoft compatible.
3) Under Preference/View, set line numbers.
4) Under Preference/Assoc Files, add any file type you wish to open with TextPad (e.g. .txt)

OK, back to our scheduled programming. Below are a few tricks I've come to appreciate with regular expression. I do expect the reader to have basic familiarity with regexp (^ means beginning of line, $ means end of line, . means any character, etc)

Problem: Remove all lines containing a certain text string.
Find: ^.*FINDME.*\n
Replace:
Explained: Locate the string you want, and select the entire line (including the newline character). Simply replace with nothing. Note that if you chose $ instead of \n, then you would end up with a lot of blank lines instead, but assuming that you want to completely remove these lines, you need to use \n at the end.

Problem: Insert line numbers in front of all lines
Find: ^
Replace: \i(100,10)\t
Explained: Find beginning of line, replace with a numeric counter starting at 100 and incrementing by 10 followed by a tab. So now you'll have a first column with numbers 100,110,120, etc. in it. By the way, \i by default starts at 1 and increments by 1.

Problem: You have a text files with dates in a DDMMYYYY (day, month, year) format in column 1 and you would like to quickly convert them over to an YYYYMMDD format. This is quickly done using regular expression.
Find: ^\(..\)\(..\)\(....\)
Replace: \3\2\1
Explained: Create 3 match sets of 2, 2, and 4 characters respectively from the beginning of the line. Simply put the 3 match sets in the desired order. If you are unfamiliar with match sets, then \( and \) define each set and \1 refers to the first set, \2 to the second, etc. Please note that if you use POSIX style regexp then you do not need to escape the parentheses (i.e. use (..) instead of \(..\) ) to create the match sets.

Nifty

Searching XSLT files

I work a lot with XML and XSLT files, and hence I often need to grep files (i.e. search for text strings in files) for certain term. Windows Explorer does a great job for most file types, but unfortunately the Windows Explorer search function does not include any XSLT files when searching. Just try to create a dummy file called a.xslt with a dummy text string FINDME, and try to locate it using Windows Explorer search. You will not find it. Sad, but true. If someone knows of a simple tweak (e.g. registry change) to change this behavior, then please post it in the comment section of this entry. I'm not sure why XSLT files are excluded. Afterall, JavaScript (.js) and C# (.cs) files are included. Maybe XSLT just made the Microsoft black list since it is not a Microsoft standard.

Assuming for now that we cannot change the Windows Explorer search behavior, I wanted to show you how to search all files including XSLT files, using UNIX command tools. If you just need to search the current folder, you can of course you use
grep FINDME *.xslt

And if you have a fixed 3 level folder structure you may do something like this
grep FINDME *\*\*.xslt
but note that this will ONLY look for files that are exactly 3 directories deep (e.g. it will not search the file named a.xslt in the root folder).

So we need a much more flexible solution that can search files located at any directory level. But let me first introduce yet another UNIX command line tool: find. The command find, recursively lists all files and folders matching a given criterion (e.g. name, create date, etc) and may execute a command on each file found. Again, do a "man find" to get an overview of the options.

List all files recursively:
find . -type f

List all files or directories with the string xslt in them:
find . | grep -i xslt

Find any file that contains the text FINDME
find . -type f -exec grep FINDME "{}" ";"

Note that when using -exec you also need to add those special "{}" ";" characters at the end. Don't want to go into great detail here, but you need to always add it. Otherwise you can basically specify any command you want after exec, like we did with grep in the above case.

Nifty.