Broken by design -> AT&T wireless billing export -- not-csv

---

Note: I invite your comments!  If you were able to get this to work for you, please leave me a note.  Likewise, if you were NOT able to make it fly, leave a comment with as much detail as possible, and subscribe, I'll try to address all problem reports in a timely fashion. If you concur with my opinion regarding this abominable data format, by all means, leave a comment, if you would be so kind.

TIA

---

Updated: 2013-02-04

---

What is "not-csv" format?

The AT&T Wireless website offers a feature that allows users to export their billing/usage details to either Excel or "comma separated variable" (CSV) format.  Sadly, what they call CSV is practically the antithesis of CSV, it is thoroughly useless for importing into a database, ergo the term was coined: "not-csv".  It is an abomination, a travesty, and those responsible for it should be horribly and deeply ashamed.

Update: the stupid just gets stupider!

Since publishing this script, not-csv has evolved again -- as I knew it would.

• In between the column names header line and the data lines for the text/data sections, they added [space][tab][new line][space][space][newline] (0x20 0x09 0x0A 0x20 0x20 0x0A).
• They also added a trailing space on the text/data column names line itself -- completely necessary, I'm sure!

Those two gems broke my script, it would only import the first phone line's voice call data, then it bailed.  So once again, superflous padding was added, toward what end I can't even guess.

I also noticed the following -- this may not be brand new, I'm uncertain.  It didn't impact my script, but it's worth mention:

• The text/data sections now have two sub-sections (but without a whole new column names header) one for picture/video messages and one for data.
• Sadly the "item" field reverts to "1" at the start of each sub-section (which potentially violates my table's primary key of [item]+[date]+[phone line]).
• But even if it didn't, in keeping with their legacy of shit, the KB values are formatted to separate thousands with a comma -- yeah!  A field in a CSV row that contains embedded commas but is not enclosed in quotes.  Nice work, guys, absolutely brilliant!

So to restate: my script does NOT support importing the second and third sub-sections of the text/data section, which contain records of picture/video messages and data usage, respectively.   


Can "not-csv" be converted to something useful?

I have written a script (in javascript) that will import the AT&T "not-csv" data format into SQL Server, convert it to actually usable CSV, or both.  That script is available here, see the link below.

Download the Script


Why import wireless billing details into a database?

If you aren't familiar with database technology and how to use it, the answer to that question is well beyond the scope of a blog post.  If you are, your wireless phone billing details contain a wealth of information about your life, work and family. 

Personally I have used this data to optimize my wireless account settings, identify usage trends, recover contact phone numbers after losing a phone, bill clients for phone consultation/support time, and keep loose tabs on entries into and exits from my daughter's circle of friends while she was in high school. 

Without importing into a database there's no way to analyze more than one billing period at a time, and if you have multiple lines on a family plan it further limits you to analyzing only one line at a time (without significantly editing the exported data as provided .)  Once it's in a database table, your analysis possibilities are limited only by your skill and imagination, you can go macro to micro to everything in between.

---

Downloading Billing Details:

At time of this writing the AT&T wireless billing details export page can be reached at this link (you must already be logged in for it to work.)  From this page, select the desired billing period, select the "CSV" option button and click the download button.
Note that this link has changed several times over nearly as many years; without a doubt it will change again, at which point the link will be broken.  (The same can be said of my script as well, I have had to refactor it three times since 2005.)

---

Installing and using the script:

Installation for import to SQL Server: 

1. Download http://www.thinkset.com/ImportATTBilling.zip and extract the files to a directory.
2. In SSMS create or choose a database, select it as the default/current database, and execute DDL.SQL on SQL Server.
3. Decide whether you wish to use trusted security or SQL auth, examine the files trusted_wrapper.cmd and sql_auth_wrapper.cmd, and edit one to suit your environment. (Edit the connection string parameter to reflect your server and database.)
4. Rename the .cmd file you edited in step 3 to ImportATT.CMD. (This step is optional, mostly for purposes of usage instructions.)

Installation for conversion to usable CSV files:  

1. Download http://www.thinkset.com/ImportATTBilling.zip and extract the files to a directory.
2. One option is to simply execute the script directly from a command prompt, passing it the /file: and /outputdir: arguments
3. For example, assuming you saved your not-csv download to a file named 12345678.csv in the same directory as the ImportATTBilling.js script, and you wanted the output files to be created in the same directory as well, the command would be:  ImportATTBilling.js /file:12345678.csv /outputdir:.
4. Another option is to decide which directory you want the output to be written, and then edit the tofile_wrapper.cmd file to pass the /outputdir: argument.
5. Rename tofile_wrapper.cmd to ImportATT.CMD. (This step is optional, mostly for purposes of usage instructions.)

Usage: 

1. Logon to the AT&T web site and navigate to the billing details download page.
2. Select the desired month, click the CSV option button, then click the Download button.
3. For ease, save the download file to the same directory as the script, using a descriptive file name (e.g., 2012May.csv.)
4. Execute ImportATT.CMD from a command prompt, passing it the name of the downloaded file as the first argument, enclosed in quotes if it contains embedded spaces (may be relative or fully qualified.)

Tip:

• If you need to import or convert more than one month's worth of downloaded not-csv, open a command window and execute the following, in the directory that contains all the not-csv files: copy *.csv att.csv (Assumes that all .csv files in that directory were downloaded from AT&T and that none of them are named att.csv.)  Then you only need to run the script once on att.csv, as that command combines all files that match the pattern *.csv into one file. 

 

---

---

Script's supported arguments:       

     /file:[input file name] (required)
     /connection:[database connection string] (optional)
     /outputdir:[output file directory] (optional)
     /nobanner (optional, omits banner output)
     /? (optional, outputs a summary of accepted arguments)

     /f:, /c:, /o: and /n are synonyms for
     /file:, /connection:, /outputdir: and /nobanner, respectively.

[input file name]: The full or relative name of a file downloaded from the AT&T wireless site. This is the only required argument.

[database connection string]: (It's complicated, Google it.) This argument is optional, if it is omitted, no database output is generated.

[output file directory]: The directory in which you want converted output files to be written. (It must already exist.) Output files are named based on the [input file name], by prefixing with "voicedetails_" and "datadetails_". If files with those names exist, they will be overwritten without warning. This argument is optional, if it is omitted, no file output will be generated.

If neither /connection: nor /outputdir: are passed, output is written to console. If invalid values for either are passed, the script will exit without generating any output.

---

Schema Note: you may notice two pairs of fields in [ATTTextMsgImport] with very similar names:

• [Msg/KB/Min] and [Msg/KB]
• [In/ Out] and [In/Out]

These field names were changed by AT&T. If you need to, you can derive a single column to combine a pair,  e.g., COALESCE([In/ Out], [In/Out]) As CInOut

---

Disclaimer:
The Programmer asserts that this code has been tested on modern hardware, and is free of malintended constructs, however, you, the end user, assume all responsibility for verifying that it's safe to use in your environment.

If you lack the technical ability to do so, DO NOT RUN THIS CODE!  You alone are responsible for any damages and/or loss that may be incurred through use or misuse of this code.

Under absolutely no circumstances will the Programmer be held liable for damages of any sort, whether consequential, incidental, transcendental -- NONE, AS IN NOT ANY, WITHOUT REGARD TO NATURE OR CAUSE.

Your use of this code constitutes implicit acceptance of the terms above.

Visual Studio » BRIEF keyboard emulation implemented in VS2010 AddIn (downloadable project)

They can make us learn a new O/S every 3 years, and force yet another framework down our throats every 5-7 years -- we've all had to adapt to that. (To resist is to either languish in legacy code, or retire.)  They can even make up "new" C-like languages, that we are thus compelled to learn, if they must. But they CANNOT take our BRIEF keyboard convention away, on that score there can be NO compromise! :-)

(To download the latest version VS2012 binaries, click this link.)
(To download the latest version VS2010 project, click this link.)

(To download the original VS2010 project, click this link.)

Close all instances of VS2010, then copy the files BRIEF.AddIn from the project directory, and BRIEF.dll from the bin directory to:

%USERPROFILE%\Documents\Visual Studio 2010\Addins

Important:  After unzipping the files will probably have the "downloaded from the internet" security block set, for full details, see this link http://www.howtogeek.com/70012/what-causes-the-file-downloaded-from-the-internet-warning-and-how-can-i-easily-remove-it/

When you restart VS2010, you'll find a set of functions that start with BRIEF.Connect, in the list of functions that can be mapped to keystrokes (Tools->Options->Environment->Keyboard.) Select Text Editor in the drop-down labeled "Use new shortcut in:" and map the following functions to their corresponding keys

BRIEF.Connect.BRIEFPaste: INS
BRIEF.Connect.BRIEFCut: NUM-MINUS
BRIEF.Connect.BRIEFCopy: NUM-PLUS
BRIEF.Connect.BRIEFHomeKey: HOME
BRIEF.Connect.BRIEFEndKey: END
BRIEF.Connect.BRIEFUndo: NUM-ASTERISK
BRIEF.Connect.BRIEFLineDelete: ALT-D
BRIEF.Connect.BRIEFSearchFile: F5
BRIEF.Connect.BRIEFSearchNext: SHIFT-F5
BRIEF.Connect.BRIEFPageUp: PAGE UP
BRIEF.Connect.BRIEFPageDown: PAGE DOWN
BRIEF.Connect.BRIEFArrowDown: ARROW DOWN
BRIEF.Connect.BRIEFArrowUp: ARROW UP
BRIEF.Connect.BRIEFArrowLeft: ARROW LEFT
BRIEF.Connect.BRIEFArrowRight: ARROW RIGHT
BRIEF.Connect.BRIEFToggleColumnSelect: ALT-C
BRIEF.Connect.BRIEFToggleLineSelect: ALT-L
[Latest version only]
BRIEF.Connect.BRIEFAltA: ALT-A
BRIEF.Connect.BRIEFCtrlArrowLeft: CTRL-ARROW LEFT
BRIEF.Connect.BRIEFCtrlArrowRight: CTRL-ARROW RIGHT
BRIEF.Connect.BRIEFDelete: DEL

You can also map these built-in functions if desired:

Edit.MakeUppercase: CTRL-UP
Edit.MakeLowercase: CTRL-DOWN

Upgrade instructions:

If you have already installed an older version and wish to upgrade, you'll want to export your keyboard settings only, then execute devenv.exe /ResetAddin BRIEF.Connect.BRIEFAltA, then import the keyboard settings you just exported.  That will update the supported commands to include any new ones, and will save you from having to remap the required keys (you'll only have to map the newly added ones.)

Note:  For reasons unknown I had to delete the AddIn files, start and exit VS, then copy the files and start it again, before the AddIn functions were included in the list [of functions that can be mapped to keys.]  Why that might be necessary, I haven't a clue -- what made me think to try it (after re-examining the code I had just added) I couldn't tell you either... Apparently something internal needed it to be gone completely before it could be back... go figure.

In the top-level project directory there are helper CMD files (one for VS2010, one for VS2012) to walk you through the upgrade steps, automating as much as I know how.  Each of them starts VS 3 times, once with the import/export dialog open so you can export keyboard settings, once after deleting the old files but before copying the new ones (see note above) and finally, again with the import/export dialog open, so you can import keyboard settings.

Release notes:

1. Added support for Alt-A
2. Made Alt-C/L/A cancel other modes if active and collapse selection
3. Made DEL key cancel BRIEF selection mode if active
4. Added support for ctrl-left/right, to extend selection if a BRIEF mode (ALT-C or ALT-A) is enabled

Known Issues:

1. If you turn on either column or line select mode (ALT-C or ALT-L), select some text and then click somewhere else in the document, your selection will collapse, and you will still be in that selection mode, starting at the new caret position.
2. The built-in copy/cut/paste ops do not automatically cancel either of these modes, though the BRIEF copy/cut/paste ops do. If you use the built-ins, you'll have to toggle these modes off manually.

Future Plans:

It'd be nice to make the F5 search thing a little more full-featured. I plan to fix known issue #1 by processing events.


Enjoy!

Client Scripts » Send To-Email Recip Workaround for XP x64 (v.2.0.1)

XP x64 is pretty damn cool, but there is still much left undone. For example, the Send To -> Email Recip shell feature -- it doesn't use Outlook to create the email, because Office is still 32 bit. OE that ships with XP x64 is a 64 bit shell extension, so it becomes the only game in town.

If you run %windir%\SysWOW64\explorer.exe, the Send To thing works from there -- but that isn't always real fucking convenient now, is it? So I wrote this script to fix the problem.

To use this script:

1. Copy the text from the code window below, and paste it into a text editor, such as notepad;
2. Save the file to whatever [target] directory you want, as SendToMailRecip.js;
3. Open a console window and change directory to the [target] directory
4. Type the following command:

 

   SendToMailRecip.js /register:yes

 

That will add it to your registry as a shell extension for all file extensions. The location of Outlook is of no consequence to this script.

 

Code: SendToMailRecip.js

 

var hdr = new Array(
"         Script: SendToMailRecip.js version 2.0.1",
"        Purpose: Work-around MS lameness ",
"                 re: Send to -> Mail Recip shell functionality on XP x64",
"         Author: Mark J. McGinty",
"           Date: 19 March 2009",
" IP Declaration: Copyright (c) Mark J. McGinty 2009, All Rights Reserved",
"    Permissions: Granted to public domain: permission to use and/or distribute,",
"                 provided this header is left intact"
);

var usage = new Array(

"",
"Usage:",
"       To register this script (using its current location) as a ",
"       SendTo shell extension, execute the following command:",
"",
"            SendToMailRecip.js /register:yes",
"",
"       To unregister, execute the following command:",
"",
"            SendToMailRecip.js /register:no"
);

 // The subject and body text for the emails this script creates are generated using the two

// template strings below.  Needless to say, non-alpha characters must be escaped according
// to JavaScript rules (e.g., "\r\n", as is used below, will be rendered as a blank line.
// Note that it was not necessary to split-up the value assigned to bodyTemplate, it was
// done that way to help visualize the end output.)
//
// The token {{filename}} is replaced at runtime with the name of the attached file.
// (Inclusion of this token in either template is optional.)
var subjectTemplate =  "Emailing file: {{filename}}";
var bodyTemplate =  "See attached file: {{filename}}" +
   "\r\n" +
   "\r\n" +
   "(If in doubt, contact the sender before opening the attachment.)" +
   "\r\n" +
   "\r\n" +
   "Make XP x64 SendTo work for you!" +
   "\r\n" +
   http://deprecatethis.blogspot.com +
   "\r\n";

 

String.prototype.Replace = function (findStr, replStr) {
 if (findStr == null)
  return this;
 if (replStr == null)
  replStr = "";
 var a = this.split(findStr);
 return a.join(replStr);
}

 

var reg = WScript.Arguments.Named("register");
if (reg != null)
{
 WScript.Echo(hdr.join("\r\n"));
 try {
  var oShell = new ActiveXObject("Wscript.Shell");
  var sendtoDir = oShell.ExpandEnvironmentStrings("%USERPROFILE%") + "\\SendTo\\";
  var shortcutName = sendtoDir + "Mail Recipient.lnk";
  var fso = new ActiveXObject("Scripting.FileSystemObject");
  var tmp = "";
  if (reg.toUpperCase() == "YES")
  {
   var oShellLink = oShell.CreateShortcut(shortcutName);
   oShellLink.IconLocation = "%SystemRoot%\\system32\\SHELL32.dll,156";
   oShellLink.TargetPath = oShell.ExpandEnvironmentStrings("%SystemRoot%") + "\\system32\\wscript.exe";
   oShellLink.Arguments = "\"" + WScript.ScriptFullName + "\" /file:\"%1\"";
   oShellLink.Save();
   try { fso.DeleteFile(sendtoDir + "Mail Recipient.MAPIMail", true); } catch(e) {};
  }
  else
  {
   try { fso.DeleteFile(shortcutName); } catch(e) {};
   fso.CreateTextFile(sendtoDir + "Mail Recipient.MAPIMail", true);
   tmp = "un";
  }

   WScript.Echo("\r\n\r\nSendToMailRecip: " + tmp + "registration succeeded!");

 } catch(e) {
  WScript.Echo("\r\n\r\nSendToMailRecip: " + tmp + "Registration failed. (You may lack sufficient permissions.) \r\n" +
   "Error: " + e.description);
 }
 WScript.Quit();
}

var fullPath = WScript.Arguments.Named("file");
if (fullPath == "%1")
 fullPath = WScript.Arguments.Unnamed.item(0);
if (fullPath != null)
{
 var olMailItem = 0;
 var a = fullPath.split("\\");
 var fileName = a[a.length - 1];
 var oOutlook = new ActiveXObject("Outlook.Application");
 var oItem = oOutlook.CreateItem(olMailItem);
 oItem.Attachments.Add(fullPath);
 oItem.Subject = subjectTemplate.Replace("{{filename}}", fileName);
 oItem.Body = bodyTemplate.Replace("{{filename}}", fileName);
 oItem.Save();
 oItem.Display();
 WScript.Quit();
}

 

WScript.Echo(hdr.join("\r\n"));
WScript.Echo(usage.join("\r\n"));
WScript.Quit();

 

---

This script supports one command line switch with two possible options: /register:[yes|no]

/register:yes creates a shortcut to the script file (using its current path location) named 'Mail Recipient.lnk' in the user's SendTo folder (under the profile folder, as obtained by expanding the environment variable %USERPROFILE%.) It also deletes the file 'Mail Recipient.MAPIMail' from the same folder, if it exists.

/register:no re-creates the file 'Mail Recipient.MAPIMail' in the SendTo folder (it is merely a zero-length file with the extension '.MAPIMail', which is registered to a MIME type handler.) It also deletes the shortcut 'Mail Recipient.lnk' from the same folder, if it exists.

Neither option alters any handler registration, including but not limited to the one invoked for files with the extension '.MAPIMail'.

---

Unfortunately, a simple script like this one relies on the shell to pass the selected file name as a command line argument -- and it passes only one (the file that was right-clicked, regardless of how many were selected.) In order to utilize multiple file selections, the called binary must make API calls to read the selection list from the system (via an interface defined for this purpose.)

---

If you wish to customize the subject and/or body text of the emails this will create, you'll find, near the top of the script (near line 35) there are two template strings that are used to render subject and body, respectively. These templates support a single case-sensitive token, {{filename}} which is replaced with the name of the attachment when the script is called.

The values assigned to these template strings can be set to any JavaScript-legal string expression of the user's choosing.

Registry Hacks » Enable/Disable short file names

(Note: before following this or any other tip on this site, please read the Disclaimer)

The advent of long file names forced the introduction of short "tunneled" names, to conform with ancient 8.3 limits, and application code based there-upon. These short names are used by the system as aliases to provide down-level compatibility. They are generated by the system, using the first few characters of the long name, appended with a tilde followed by a number to insure uniqueness. (Example: C:\PROGRA~1 as an alias for C:\Program Files)

Generating/resolving these aliases incurs overhead, which by now has become mostly needless, as support for 16 bit applications has been retired, so NTFS offers a way to disable this behavior.

But tragically, there are some applications out there that still have built-in dependency on short names – and the manner in which they fail is anything but forthcoming. Case in point, the live updater for Motorola Phone Tools. The app installs from CD, but demands to be updated when executed, and the update fails if short names are disabled. (Deeply annoying from the word go, I can assure you! To the R&D folks at Moto, and/or the hacks they paid to develop this crap: you suck, get it together!)

Usung REGEDIT.EXE, browse to the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem

Edit the value of this DWORD data item:

NtfsDisable8dot3NameCreation=(1/0)

(Short names enabled = 0; Short names disabled = 1)

Registry Hacks » How to kill pesky MSI roll-backs

(Note: before following this or any other tip on this site, please read the Disclaimer)

Ever encountered an MSI-based installer that, in spite of reporting some failure, actually does succeed in leaving you with something workable? Sadly any such stroke of seemingly good luck tends to be short-lived, as the next MSI you run – even if it’s entirely unrelated – compels you to roll-back that “failure” thus removing that workable something from your system. It lacks a way for the user to decide that marginal success is good enough, and the cycle of forced removal and near-successful re-installation becomes irritating in a heartbeat.

Using REGEDIT.EXE, browse to the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\Rollback\Scripts

(Remove applicable string data items as desired.)

Registry Hacks » How to cancel Autoplay

(Note: before following this or any other tip on this site, please read the Disclaimer)

[Note: this tip was written Feb 2008, whether or not it applies to Windows 7, I can't say.]

I absolutely despise the way Autoplay has evolved into an intrinsic part of mounting removable media. The autorun.inf scheme doesn’t offend me in-and-of itself, it fills a need, and as a technology it’s net-positive. There are some annoying implementations, but it’s always possible for its implementers to know the contents of the media they are working with.

Conversely, Autoplay is an attempt to guess the user’s intentions based on analysis of the media content. Sadly it isn’t very good at this. When I insert a CD or memory stick, I nearly always have a plan for its contents already in mind, the application[s] I intend to use are already running, and I neither need nor want some set of suggested actions.

Worse yet, the amount of time Autoplay consumes is routinely excessive; any media flaws become excruciating. And worst of all, the apparent ability to configure Autoplay that’s offered by its UI is a sham – want to remove some handler that some app installer has added to the chain: tough luck. There is no graphical way to truly control it.

Using REGEDIT.EXE, browse to this key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\AutoplayHandlers\CancelAutoplay\Files

Then add a string data item named:

*.*

(Its value should be empty.)

Client Scripts » A script to authenticode-sign your executables (an alternative to signtool.exe)

First note that you'll need an authenticode certificate, issued by a recognized certificate authority (CA). The cheapest I've found is Comodo, if you dig a little you can get a cert *with exportable private key (PK)* from them for $100.

Why is it important that the PK is exportable? Well, without that you won't be able to copy or move the cert to any other machines (without help from the CA) and you won't be able to use signtool's CLI. (Whether or not my script will work without exportable PK I'm uncertain, but my assumption is that it would not.)

Whatever you do, don't go to Verisign for a cert, unless you have more money than you know what to do with! Last I checked they wanted you to pay extra for their "pro" cert if you wanted to export the PK. Verisign just sucks in general -- I'm still pissed off about the bullshit they pulled in 2000... but that's another story...

Back to the topic, the script in downloads will use a cert from your cert store only in the simplest of cases, i.e., you have one authenticode cert in your personal store. I'm sure it's possible to modify it for more complex cases, if you can navigate the Certificates collection. (Comments are inline in the code.) Barring that, you can always export the cert with PK to a .PFX file and use that, as long as you don't mind storing the password in your script in clear text.

If you're wondering why use a script, rather than calling signtool.exe's CLI from a batch... I suspect that if you've found this topic, you already have your own reasons -- please by all means post them under this topic. (I'll post my reasons for writing the script if there's any activity here.)

var oCode = new ActiveXObject("CAPICOM.SignedCode");
var dirname = WScript.ScriptFullName.slice(0, WScript.ScriptName.length * -1);
var filename = dirname + WScript.Arguments.Named("file");
oCode.FileName = filename;
  
var isSigned = false;
try {
 var buf = oCode.Description;
 isSigned = true;
} catch(e) {
 isSigned = false;
}

try {
 if (isSigned == false)
 {
  var oCode = new ActiveXObject("CAPICOM.SignedCode");
  var oSigner = new ActiveXObject("CAPICOM.Signer.1");
  oCode.FileName = filename;
  // The code below assumes a very simple personal cert store, with only
  // one authenticode cert in it, or with the desired cert being first
  // in the list. More complex cases can be handled by navigating the store.
  //
  var oStore = new ActiveXObject("CAPICOM.Store.1");
  oStore.Open();
  oSigner.Certificate = oStore.Certificates.item(1);
  // Alternative to 3 lines above, requires you to export your code signing
  // cert with private key, to a file (and assumes it is in the same dir as
  // this script. Also requires coding the pwd in your script in plain text.
  //
  //oSigner.Load(dirname + "signingcert.pfx", "pwd");
  oCode.Sign(oSigner);
  oCode.TimeStamp("http://timestamp.comodoca.com/authenticode");

  // throw an error on purpose if signing failed
  buf = oCode.Description;
  oStore.Close();
 }
} catch(e) {
 WScript.Echo("An error occurred: " + e.description + "\r\n(Press ENTER to quit)\r\n\r\n");
 WScript.StdIn.Read();
}

VBA Macros, Client Scripts » VBA to Invert Case for several Apps

Oops CAPS LOCK -- damn I *hate* retyping!
How many times have you accidentally pressed the caps lock key, or forgotten about it being on until after typing some amount of text? I bet I've done it 10 times just today! What a bummer to have to retype it -- especially when it's so avoidable!

Recently it occurred to me how easily such mistakes could be fixed by a bit of code. The first place I wanted an InvertCase hot key was Visual Studio 6. I got it to work inside of 10 minutes -- cool! I decided to map the macro-function to CTRL-\, easy to hit with one hand, and all the more intuitive combinations were taken.

Encouraged by quick success, I thought about all the other places it would come in handy. The various apps that make up MS Office came quickly to mind. It would've been nice to have a single source that worked in many host applications, but sadly that's a pipe dream. As I examined some of the potential host apps for this, I realized that a.) the core code for this is highly portable, and b.) each host app has its quirks.

So I isolated the core functionality into a portable function, shown below...

Code:

' The implementation core, which of course will need to be
' defined in each host app environment, but the source is the same
' in all of them.
'
Function InvertCaseCore(inbuf)

   Dim l, c, outbuf, i
   l = Len(inbuf)
   For i = 1 To l
        c = Mid(inbuf, i, 1)
        If (c >= "A") And (c <= "Z") Then
            c = LCase(c)
        ElseIf (c >= "a") And (c <= "z") Then
            c = UCase(c)
        End If
        outbuf = outbuf & c

    Next

    InvertCaseCore = outbuf
End Function

Now onto the practical side, here are wrappers for some host apps that I use, first stop, VS6...

Code:

' macro in Visual Studio 6
Sub InvertCase()
   Dim outbuf
   outbuf = InvertCaseCore(ActiveDocument.Selection.Text)
   ActiveDocument.Selection.Delete
   ActiveDocument.Selection.Text = outbuf
End Sub

Next I moved on to MS Word. In this case, the ActiveDocument object exists in Word's VBA model but it doesn't expose a Selection property, as it does in VS6. Also, in VS6, the Selection object is not global, as it is in Word. So ActiveDocument.Selection in VS6 became merely Selection, as is shown below.

Code:

' VBA macro in MS Word
Sub InvertCase()
   Dim outbuf
   outbuf = InvertCaseCore(Selection.Text)
   Selection.Delete
   Selection.Text = outbuf
End Sub

Both of the above act on the currently selected text when the hot key is pressed. Next I did MS Excel, which was a slight departure because apparently its VBA macro facility is disabled while text in a cell is selected. When I clicked the hot key, the selected text disappeared. So in Excel it acts on the contents of the active cell... which suits me well enough.

Code:

' VBA macro in MS Excel
Sub InvertCase()
   ActiveCell.FormulaR1C1 = InvertCaseCore(ActiveCell.FormulaR1C1)
End Sub

To make this fly in Outlook, I had to use Outlook Redemption (an excellent tool, btw.) I also had to create a commandbar button in the Inspector; I named it &\InvertCase, which gives me ALT+\ as a hot key to invoke it.

Code:

Public Sub InvertCase()
   Dim oInspector As Outlook.Inspector
   Set oInspector = Application.ActiveInspector
   If oInspector Is Nothing Then Exit Sub

   Dim oSafeInspector As Redemption.SafeInspector
   Set oSafeInspector = New Redemption.SafeInspector
   oSafeInspector.Item = oInspector
   Dim buf As String

   buf = InvertCaseCore(oSafeInspector.SelText)
   oSafeInspector.SelText = buf
   Set oInspector = Nothing
   Set oSafeInspector = Nothing
End Sub

Not sure where else I'll want this, but when I think of someplace, I'll post the wrapper here.

That's it for now, enjoy!

Broken by design » MOSA (Outlook SMS AddIn)

This little tool is kind-of pretty cool actually, it connects with my Bluetooth handset and sends text messages to mobile phones from my desktop, available for free from Microsoft.

But it does at least one thing that is intensely lame: it rejects any phone number that is not in Outlook's preferred format:

+1 (000) 000-0000

To me it's a really stupid limitation, why doesn't it just strip that BS and impute the country code, like every other reasonably intelligent piece of software does?

And another *slight* problem: the outbound SMS messages it creates (on AT&T, anyway) do not count as text messages; rather they count as data across the unwire, rounded-up to the nearest 1K, and billed as such.

Like so many other things they do, so close but yet so far.

Broken by design » Visual Source Safe: Holy Mother of Lost Work and Wasted Time

The number of ways this piece of shit is broken could well be conventionally incalculable using modern day desktop technology... As I think of it, VSS is the only tool that has repeatedly earned my distrust, yet I continue to use it.

But everything starts somewhere, so I think I'll go for low-hanging fruit.

When VSS encounters a file that's writable but not checked out (or any other sort of version mismatch, for that matter) wouldn't it be nice if it offered a way to compare the two, *before* making a decision about which to keep? How hard could that possibly be to implement -- given that it already has a difference comparison facility [albeit lame] built into it?

Some of the dialogs shown in case of those mismatches have 5 different options -- yes, 5! And some of their labels are anything but clear, and some are practically guaranteed to completely screw you over! So as long as we're living large here in "wish it worked land" what if -- just suppose for a moment if you will -- it did something wild and exotic like comparing the contents of the apparently conflicting versions, to make sure they are actually different, before frivolously troubling the user with a bunch of no-win choices!

Oh wait, I forgot the VSS built-in comparison facility is utterly useless -- it seems inordinately sad and pathetic that it's "ignore white space" option fails to deliver, particularly when it sometimes injects BS whitespace as part of the check-in cycle.

Case in point, I can generate a set of SQL scripts using DMO, check them in, check them out again and regenerate them identically (without changing the database objects at all) -- VSS compare will tell me every single one is different from the checked-in version, but the only differences are white space that it injected.

I'm not even going to touch on shared files, that's such a cluster-fuck it gives me a headache to even think about it.

But I will mention, to the company that's been forcing Unicode down our throats for years... is VSS *ever* going to support Unicode text as anything other than binary?

Broken by design » INTERNET_CACHE_ENTRY_INFO.CacheEntryType

It might be a stretch to call this "broken" but the wininet.h surely has omitted significant detail. These are the bit values officially defined for it:

#define NORMAL_CACHE_ENTRY 0x00000001
#define STICKY_CACHE_ENTRY 0x00000004
#define EDITED_CACHE_ENTRY 0x00000008
#define TRACK_OFFLINE_CACHE_ENTRY 0x00000010
#define TRACK_ONLINE_CACHE_ENTRY 0x00000020
#define SPARSE_CACHE_ENTRY 0x00010000
#define COOKIE_CACHE_ENTRY 0x00100000
#define URLHISTORY_CACHE_ENTRY 0x00200000

Problem is, if you call FindFirstUrlCacheEntryEx/FindNextUrlCacheEntryEx to enumerate the cache you'll find that there are several bits in use that aren't defined.

For one thing, bit 0 (NORMAL_CACHE_ENTRY) is always set, for every entry, so I assume an entry is "normal" when only that bit is set. The tech ref makes no mention what to make of composite types -- how can an entry rationally be "normal" and something else at the same time? For my purposes I masked bit 0 and then considered a 0 result to be "normal".

More, in my cache right now there are entries with the following [undefined] type values: 0x41, 0x45, 0x101, 0x1041, 0x1141, 0x2041, 0x3041 and 0x8041. This means there are 5 single-bit values that are left undefined by wininet.h:

0x00000040
0x00000100
0x00001000
0x00002000
0x00008000

I've noticed that Pragma: no-cache appears in the headers of all entries with CacheEntryType == 0x3041. Whether anything empirical can be inferred from this about type value 0x3041, I can't really say -- but it surely speaks to just how worthless Pragma: no-cache is! All items of this type are backed by a local file, and worse, every single one has expiry in the past! What the fuck? Talk about being thrown under a bus! Whether IE would actually use them may be another matter entirely, but the fact that they exist in cache and on disk surely makes it possible.

Nothing I've found online even mentions this short-fall of definition, most of it merely regurgitates what's found on MSDN... which is useful, not at all.