Quick Play with Pattern and Hold Spaces in Sed

I had some rows of data that required two changes and both changes had to appear on separate lines. For example, if the input was ...

123
456
789

... and the requirement was to find '456', replace '5' with 'a' on one line and 'b' on another line so that the output would look like this:

123
4a6
4b6
789

The sed script below applies the first change, prints it, applies the second change and let sed print the pattern space at the end of the function.

/456/ {
  s/5/a/p --> pattern space = 4a6, print pattern space.
  s/a/b/  --> pattern space = 4b6
}         --> print pattern space, 4b6

In the script above, notice that the input string was first modified from '456' to '4a6' so the second statement has to replace 'a' with 'b'.

Another approach is to create two lines first (i.e. 456\n456) then replace the first '5' with 'a' and replace the second '5' with 'b'.

/456/ {
  h      --> hold space = pattern space, 456
  H      --> hold space = 456\n456 (456 + \n + pattern space)
  g      --> pattern space = hold space, 456\n456
  s/5/a/ --> pattern space = 4a6\n456
  s/5/b/ --> pattern space = 4a6\n4b6
}        --> print pattern space, '4a6\n4b6'

The script above uses sed's hold space to construct the two lines first. I don't know if the second script is clearer than the first but it was a simple problem that I could use to play with sed's pattern and hold spaces.

Giving Up On Windows 7 Explorer

Giving up on Windows 7 Explorer because file management is harder than WinXP Explorer since it's not possible to disable "auto row highlight" and "select full row".
Feature #1 is confusing because two items can be highlighted in a directory list when you navigate using the mouse and cursor keys: the item under the pointer gets highlighted automatically without having to activate the window but the previously selected item remains highlighted (see the two highlighted folders in the image below). It's too easy to accidentally copy or move the wrong object using keyboard shortcuts because I couldn't tell which object was the active one. In WinXP, you have to click in the window before the selection is changed and only one item is highlighted. Feature #2 means you have to find a blank space (usually the small gap between the item and the edge of the window) with the pointer to show the folder's context menu otherwise you see the highlighted item's context menu.

Playing with an alternative file manager called FreeCommander now. Out of the box, it highlights the entire row but you can disable that feature in Settings, View, File/Folder List, Full row select.

Australian Accounting Number Formats

Number format codes for Excel and Libre Calc used in Australian accounting (Excel does not have this number format code available as a default while Libre Calc has the second one).

Number format code	123456.78	-123456.78	0.00
#,##0_);(#,##0);-_)	123,457	(123,457)	-
#,##0.00_);(#,##0.00);-_)	123,456.78	(123,456.78)	-

Notes:

• Semicolons delimit the sign of the number being formatted: positive;negative;zero.
• The underscore before the right parenthesis: append a trailing space the size of the parenthesis so that positive numbers are right-aligned with negative numbers (which are enclosed in parentheses).
• Using the first format code, your numbers are formatted without cents.

The underlying value in the cell hasn't changed, just the presentation. Use sparingly; you can confuse a user who tries to use what they see in the cell in their formula and wonder why their formula doesn't work.

Convert DOS Lines Into One Comma-Separated Line

Note to myself: Convert multiple DOS lines into one comma-separated line using this GnuWin command: tr -s "\r\n" ,. DOS lines use two control characters \r\n to mark the end of a line. The -s option replaces multiple original characters with only one replacement character.

Resize HFM Running Tasks for Smaller Screens

The list of running tasks in HFM is often too wide to be viewed without scrolling the Internet Explorer (MSIE) window horizontally (unless you have a really big monitor). On a notebook, the Description column is often rendered under the right edge of the browser window. My workaround is to use a custom CSS to break the text in cells into separate lines so that the output is formatted to fit the window's width.

1. Create a CSS file called Personal.css.
2. Add this line to the file: td.line { white-space: normal !important; }.
3. Open MSIE.
4. In MSIE:
   1. Select menu item Tools, Internet Options.
   2. Press the Accessibility button.
   3. Tick the User style sheet checkbox and add the path to your CSS file in the Style sheet text field.

Using !important allows your custom CSS to override the HFM CSS definition, and white-space: normal allows the browser to break text in table cells at white spaces, instead of keeping the text in one line (HFM had white-space: nowrap).

On Writing Scheduled Tasks for Windows AT Command

Less painful ways to write scheduled tasks for the Windows at command.

• Use absolute paths to reference files or folder because at starts in a default folder, not the folder you are currently using.
• Use caret (^) to escape the redirection characters so that you can combine the output (stdout) and error (stderr) streams to a file.

Here's an example that combines those tips: at 12:00 C:\Temp\Test.cmd ^> C:\Temp\Test.log 2^>^&1.

It is also be useful to dump the at command's environment variables so that you can check if the variables are set correctly (e.g. PATH): at 12:00 cmd /c set ^> D:\Temp\Env.txt.

2013/12/10: With Task Scheduler, you don't need to use the caret to escape the cmd metacharacters, i.e. Program = cmd and Arguments = /c C:\Temp\Test.cmd > C:\Temp\Test.log 2>&1.

Excel Data Validation Drop Down Control Not Visible

A user found a strange problem in an Excel workbook: the data validation drop down control did not appear for any data validation cells in a specific worksheet (they appear in other worksheets). I tried the possible solutions but they did not work.

The solution came when I tried to resave the workbook as an Excel 97-2003 XLS file and the Excel compatibility checker reported some formulas were unavailable (in this case, SUMIFS) for earlier versions of Excel. After saving the workbook as an XLSX file to avoid this error, the data validation drop down control reappeared in the worksheet. It looks like opening an XLS file using Excel 2007 in compatibility mode may cause unexpected problems like this.

(Slightly) Faster Typing with Standard Android Onscreen Keyboard

Found that you can enter digits and punctuation by using the touch-and-hold gesture on the standard Android onscreen keyboard. For example, hold down the q to get '1' or hold down the DOT to choose from a menu of common punctuation marks. This gesture makes it possible to type slightly faster since you do not need to change modes so frequently.

The touch-and-hold gesture also provides extra characters for keys in the number and symbol modes. For instance, 1 provides a choice of fractions with 1 as the numerator, $ provides common currency symbols and ( provides different left brackets.

2012-07-17: When three dots appear below the suggested words, touch-and-hold any of the suggested words to get a table of additional words.

If you read the Google's Use the keyboard guide, it mentions the touch-and-hold gesture but you might miss the usefulness of the feature since the guide does not go into detail or provide any examples.

PS. When I tried the touch-and-hold gesture on a physical BlackBerry Bold keyboard, I get capital letters, so that gesture is available on other mobile phone keyboards.

Flatten or Collapse Excel Multi-column Data Into One Dimension

You can flatten or collapse multi-column Excel data into one row or column using GnuWin utilities.

1. Copy the worksheet data into the clipboard.
2. Open a CMD window and enter this chain of commands: getclip | tr -s [:cntrl:] \n | putclip.
3. Paste the data back into your worksheet. You should get a column of data.

The getclip-putclip pair of programs gets and puts data in the system clipboard and is part of the CygWin package in GnuWin. tr translates control characters (e.g. TABs) to NEWLINEs and the -s option squeezes out repeated characters. This chain of commands works because each column of Excel data separated by a TAB character in the clipboard. Here are more examples of this pattern.

If you want to remove duplicates in your data, insert sort -u into the chain: getclip | tr -s [:cntrl:] \n | sort -u | putclip. For example, if you start with this input (note the trailing TABs in rows 1 and 2) ...

1  
2 4 
3 5 7
4 6 8
5 7 9

... you end up with the following:

1
2
3
4
5
6
7
8
9

If you want a single row output, replace all control characters with TABs: getclip | tr -s [:cntrl:] \t | putclip. In this case, you cannot include sort because it sorts lines of data and there is only one line in the output.

1 2 4 3 5 7 4 6 8 5 7 9 

Add Album Art to Google Play Music

The standard Google Play Music app doesn't display album art for MP3 files ripped by Microsoft Music Player (I thought the images are embedded in the files). This seems to be annoying enough to warrant the creation of album art grabbers apps on Google Play. The aptly named Album Art Grabber by Tim Clark seems to do the job. After running the app to get the images, I found I have to delete all the data stored by the Google Play Music before it displays the downloaded album images.

1. On your Android device, open the Apps manager.
2. Select Device, Apps.
3. Select the All tab.
4. Select Google Play Music.
5. Select Storage, Clear Data.

See this video How To Add Album Art On Google Play Music (for Android).

Graffiti for Android

Check out "Graffiti for Android"

My first handheld computer was the Palm Pilot. It was a remarkable device for many reasons but the feature I remember best is the Graffiti writing input system. To enter text, the user has to learn to write a simple shorthand that is similar to block lettering. It takes about a day to become used to the writing style.
For an Android phone, you can install the Graffiti application from Google Play. To use it, go to the Settings panel, Language & Input, Default and choose Graffiti as the default. The next time you enter text, the Graffiti input pad will appear instead of the Android keyboard.

https://play.google.com/store/apps/details?id=com.access_company.graffiti

Cannot open user default database. Login failed.

I encountered this error when I tried to open a new connection using SQL Server Management Studio (SSMS): Cannot open user default database. Login failed.. It was a bit puzzling because my application using the same user account and password can connect to the required database.

It turns out that when SSMS starts a new connection, SQL Server expects the user account to be assigned to a default database (per the message) while my application could always connect since a database is specified in the connection string.

When a client connects to a SQL Server instance without specifying a database context, the default database defined for its login is used. If that database is unavailable for any reason, the above message appears.

The fix is to specify the default database for the user in their Login Properties.

I think when the DBA restored my database, the previous database was first deleted, so the default database setting was also deleted for my user account.

Reference

Cannot open user default database. Login failed.

Cannot Change Defaults in Wordpad

Hm, there is no way to change the default font or paragraph in Wordpad. The workaround is to create and write a WRI file with all your settings then use it to launch Wordpad instead of opening Wordpad directly (see Changing Defaults in Wordpad).