Copy a Formula Down a Column for Thousands of Rows in Microsoft Excel

For whatever reason, I had never figured out the easy way to do this until today.  There are two approaches, one with the mouse, and one with the keyboard:

Mouse Method

  1. Click to select the formula in the top cell.  If you’re still editing the formula, press Ctrl-Enter to save and stay in the cell.
  2. Double click the “copy handle” … make sure you’re hovering with the “narrow cross” not the “fat cross”:

Keyboard Method

  1. Navigate to the top cell containing the formula.  If you’re still editing the formula, press Ctrl-Enter to save and stay in the cell.
  2. Press Ctrl-C to copy the formula.
  3. Press the down arrow to move to the cell directly below the copied cell.
  4. Press Ctrl-Shift-End to select the range of cells including the target cell and extending down until an empty row is reached.
  5. Press Ctrl-P to paste the formula into the selected range of cells.

That’s it!  In either case, the formula is copied down to all cells in the column until an entirely blank row is reached, so make sure you have at least one cell in every row filled in, or the copy down process will stop at the empty row. With either method, you can verify by pressing Esc to clear any selection, then Ctrl-Shift-End to jump down to the last updated row.

ASCII Delimited Text: Why the “Right” Answer Isn’t Very Useful

To overcome some issues with the SQL Server bulk import/export process, I went shopping for a an unquoted, delimited format this morning.  There’s a very old, very simple solution that would have worked fine in my specific circumstance.  Here’s why I’m not going to use it:

The problem I had was that I need to export using the SQL Server bcp utility and re-import using BULK INSERT.  While you can specify the separators with these tools, they don’t handle quoted fields at all, i.e. quoting, if present, doesn’t actually protect the separators should they appear in data.  Per the documentation:

If a terminator character occurs within the data, it is interpreted as a terminator, not as data, and the data after that character is interpreted as belonging to the next field or record. Therefore, choose your terminators carefully to make sure that they never appear in your data.

This was a problem, because my data actually could contain tabs, carriage returns and line feeds, which were the default separators.  It also definitely contained commas.  That puts all the common delimiters out of commission.  “Choose your terminators carefully” is pretty vague, but I figured why not give it a shot?

The approach I almost took was to use the ASCII “record separator” and “unit separator” (ASCII 30 and 31, HEX 0x1E and 0x1F respectively).   Here’s a blog post that nicely sums up the format and why it’s historically and logically valid.

Though it’s not well-documented (somewhat contra-documented), SQL Server supports this.   Even though they suggest that only “printable” characters and a small set of control characters are valid, I had no problem exporting an ASCII-delimited text file using a command like this:

bcp SELECT "x,y FROM t" queryout "c:\filename.asc" -c -r 0x1e -t 0x1f -UTF8 -T -Slocalhost"

I didn’t get as far as trying the BULK INSERT on the other end, though, and here’s why…

Once I had that “ASCII delimited” file, I opened it in NotePad++ to verify that the format was readable and correct.  It was, but the effect wasn’t pretty.  I immediately realized that if I wanted to do anything else with this data–extract a column as text, import into Excel–I was going to have problems.  Excel’s text import wizard, for example, doesn’t support control characters other than tab.  This wasn’t really news to me as I see weird control characters in ASCII all the time working with magnetic stripes, bar codes, RFID, and other encoding and storage mechanisms.  Yes, you can eventually get at the data to a usable form with search and replace, or worst-case regular expressions, but why make it hard to manage if you don’t have to?

In my case, the whitespace control characters in the data improved readability but weren’t functionally required–the data payload itself was code.  Plus, I had comment blocks available as a “quote-like” protection structure.  So, I ended up compromising on replacing the whitespace control characters in a such a way that I can get them back if I want to, or leave them alone for the automated process.  What I ended up doing was this:

bcp SELECT "x,REPLACE(REPLACE(REPLACE(y,CHAR(13),'/*CR*/'),CHAR(10),'/*LF*/'),CHAR(9),'/*TAB*/') FROM t" queryout "c:\filename.tsv" -c -UTF8 -T -Slocalhost"

That produces a “normal” tab-separated file with CRLF as the record separator.  I knew that “x” couldn’t contain those characters, so by replacing them out of “y” I have a file that safely exports and imports while being viewable, editable and importable using normal tools without excessive processing.

I wish we had kept better support for ASCII control characters in our tools as we moved forward with new technologies–it would have been useful to have distinct separators that never appeared in data (until, inevitably, your data payloads themselves contained ASCII-delimited data, at which point you’re back to quoting or replacing or choosing new separators… rinse… repeat).  Of course another solution would have been making the SQL Server utilities just slightly more robust so they could process modern quoted .csv and .tsv formats.  There’s always version.next, right?

Invalid FORMATETC structure (Exception from HRESULT: 0×80040064 (DV_E_FORMATETC)) on Simple Windows Forms Drag-and-Drop Implementation

When creating a Windows Forms application in Visual Studio and implementing drag-and-drop you may encounter this exception during debugging if you drag outside your own application, even though the drag and drop operations complete successfully inside your application.

This may not be a bug, or even an error!  Things to try:

  1. Run the application executable directly from the debug folder, not in Visual Studio debug mode.
  2. Run in VS debug mode with your application directly over a window that can accept its drag content type.  Perform the drag directly from your application to the valid drop zone on the other application, passing over no other applications (including the desktop) or invalid drop zones.  For example, if you are using DataObject.SetText, place your application directly over the text area of a text editor that accepts dragging, e.g. WordPad.

In those two conditions, you probably won’t see the exception thrown and the drag-and-drop will succeed.

I think what’s happening here is that Visual Studio is being a little too aggressive in watching the Windows event messaging system.  Because of the way dragging works, these “first chance” COM exceptions will occur as the dragging mouse passes over targets that cannot accept the content stored in the DataObject.  If they have any drag-drop awareness, they will attempt a COM native “get” operation on the FORMATETC structure created when you initiated the drag in your application (you used the DataObject wrapper and injected it with DoDragDrop, but this is what you did in effect).  If the format doesn’t match any of the formats the dragged-over applications (including Windows Explorer, a.k.a. the desktop) accept, this exception is thrown.  It is then, typically, handled either by that application or Windows itself, for example by switching to the “um, not so much” cursor (circle with line through it).  Running outside of debug mode, or between two application that agree on format, it “just works” (dropping successfully in places it can, blocking the drop in places it can’t).  In debug mode, VS is telling you, “hey, look, an exception, you could handle this if you wanted to,” but in most cases you’ll just let the OS or other applications handle it.

Bottom line: don’t sweat this one too much if your application runs okay outside the debugger and in the controlled debugging conditions described above.

Here’s an article that is the closest thing I could find to an official explanation.

Testing a .NET .asmx Web Service from LINQPad

Great post here about using the Visual Studio Command Prompt tools to pull the WSDL from a .NET .asmx (traditional, non-WCF) web service and compile a standalone class library which encapsulates the interface.  Basically, run a Visual Studio prompt, switch to a writable directory and run these two commands:

Convert WSDL to service reference .cs classes:

C:\Users\user\Documents>wsdl http://localhost/MyService.asmx

Compile into a class library:

C:\Users\user\Documents>csc /t:library MyService.cs

Then in LINQPad add a reference to the new .dll and the System.Web.Services .NET library.

Then you can write this in LinqPAD:

// create an instance of the service 
var service = new MyService();
// invoke a web method and dump the results
Service.MyOperation().Dump();

 

Fix Missing Boot Loader After DISKPART UNIQUEID DISK on Windows Server 2008R2 VM Under Hyper-V

Just had a scary one… After setting up Windows Server 2012 R2 to back up all its Hyper-V VMs through the host’s nightly Windows Backup I found that only one of the three Windows Server 2008 R2 VM clients was being backed up.  The other two were failing, one with a disk CRC error, the other for no listed reason.  Based on some searching, I suspected a problem with disk ID clashes–all the VMs were from the same template .vhdx file, so they all had the same “unique” ID.  Oops.  This is actually supposed to be handled okay by the current version of Windows Backup on a Hyper-V host, but I figured I’d eliminate it as a potential issue anyway.  So, I had run DISKPART UNIQUEID DISK to reset all the Ids on all the partitions (virtual disks) in the pool of VMs to unique values.   This morning I checked the logs and saw the backups up the two VMs again failed.

Next I tried a chkdsk on all the volumes on the affected VMs.  However, after scheduling the chkdsks and rebooting the VMs I got the dreaded black screen of “no boot device can be found.”  This happened on both the VMs I had tried (the third I hadn’t attempted to reboot yet), for which I, of course, had no backup.  Oh no.  Unlike Windows 7, Windows Server 2008 R2 lacks any automatic repair tool.  Fortunately, a quick Google search turned up a Tom’s Hardware thread that indicated that the “repair” command prompt available from the original install DVD would allow the repair of the boot record with this command:

Bootrec /RebuildBcd

That command actually will scan all the mounted partitions and offer to add any Windows installations to the boot loader.  In my case, there was only one installation offered per VM and by selecting it I was back in business.  The scheduled disk checks even kicked in and now all the machines are up and running.  Whether this actually fixes the backup issue we’ll see tomorrow…

 

 

Visual Studio 2010 SP1 Slow to Start with “Loading toolbox content” Status

I still install VS2010 on all new machines for a number of reasons.  It seems that inevitably over the course of the life of the install I will eventually run into a problem where Visual Studio becomes slow to start, getting “stuck” for many seconds with this message in the status bar:

Loading toolbox content from package Microsoft.VisualStudio.IDE.Toolbox.ControlInstaller.ToolboxInstallerPackage ‘{2C98B35-07DA-45F1-96A3-BE55D91C8D7A}’

I initially theorized this had something to do with the Telerik control suite updating (via its outside “Control Panel” installer), but even completely uninstalling that didn’t solve it.  Since I keep doing a Google search to remember the real solution, I wanted to permalink the answer.  Basically:

  1. Close all instances of Visual Studio
  2. Back up registry (regedit, File|Export, Export Range: All)
  3. Delete registry key: [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\10.0\Packages\{2c298b35-07da-45f1-96a3-be55d91c8d7a}] (I also back up the specific key I’m deleting before I do it, just in case I click the wrong one)
  4. Navigate to C:\Users\WindowsUserAccount\AppData\Local\Microsoft\VisualStudio\10.0\
  5. Move toolbox*.tbd (typically four files) out of this folder and to a backup location:
  6. Restart Visual Studio

The toolbox*.tbd files will be immediately recreated on launch, probably at a much smaller size.  After the first launch, during which the toolbox is being rebuilt, Visual Studio should start much more quickly.

 

Kaspersky: Uninstall Java Now

Echoing what I’ve been saying for years, threatpost.com, the Kaspersky Lab news blog today says that, in light of the recent OSX/Java exploits, “the best course of action is to uninstall Java.”

They make a decent comparison between the Oracle of today and the Adobe of a few years ago.  I’d go farther and argue that Oracle isn’t the new Adbobe so much as the original Adobe–insular, arrogant, bloated, self-serving.

This is why, for at least the last 10 years, I have only been willing to install Java on virtual machines or hardware that would be erased at the end of the project (often by the end of the day).  Coincidentally, this is exactly how I treat iTunes–install when needed, then purge asap.

Dropbox Doubles Referral Bonus and Ceiling

Dropbox is a great, easy-to-use service for automatically synchronizing files—documents, music, photos, video, whatever!—between computers and mobile devices. As well as being available on all your devices, your files are also backed up “in the cloud” and are available securely through the Dropbox website. The standard account includes 2GB of storage and is completely free. You can install the Dropbox client on as many computers and mobile devices as you wish.

Dropbox Logo

On your computer, Dropbox acts just like a regular folder. Drag in files and they’re automatically synchronized with the cloud and your other devices. That’s it! Dropbox does all the hard work in the background, keeping your files in sync and keeping backup copies when files are changed or deleted.

Dropbox allows you to earn additional free storage through referrals. If you’re already a Dropbox user, you’ll notice they doubled the referral bonus and ceiling (including retroactively for existing referrals). That means you now get 500MB per referral for up to 32 referrals (16GB additional space).

If you don’t already have Dropbox, get it now! If you use my referral link, you’ll get an extra 500MB right off the bat (meaning you’ll start with 2.5GB instead of two). You can also earn extra space for completing a few simple steps on their web site (taking the video your, inviting at least one friend, etc.).

I’ve been using Dropbox for the last two and a half years and I’m not sure I could get by without it now. If you haven’t tried Dropbox yet, try it now!

Impact on Smartphone Data Usage of Getting a WiFi Router that Works

I upgraded to a Netgear N900 Wireless Dual Band Gigabit Router (WNDR4500) on 11/23/2011 because my (not that) old WNR3500 simply couldn’t maintain connections on mobile clients and Apple products.  The effect on my 3G data usage via my Sprint phone was dramatic:

 

I did try two other routers first:

Back in May I had tried an ASUS Black Diamond Dual-Band Wireless-N 600 Router (RT-N56U) briefly, but it ran super-hot and the settings required to make it compatible with the IPSEC VPNs I use resulted in noticeably slow throughput over the WAN port.  That went back within the 30-day return window.

With the holidays on the horizon, and the attendant flood of Apple devices that inevitably brings, in early November I became desperate enough to try the ZyXEL ZyWALL USG20W 802.11n Wireless Internet Security Firewall with 4 Gigabit LAN/DMZ Ports, 2 IPSec VPN, SSL VPN , and 3G WAN Support.  That required me to re-develop a level of expertise in TCP/IP networking that I hadn’t exhibited in years.  As much fun as that was, even with the help of ZyXEL tech support neither I nor they were ever able to get the unit to function the way I wanted (a main sticking point being the impossibility of bridging the wired and wireless networks while continuing to support the other functions of the router, in particular NAT loopback).  But what finally made me take it back was the horrendous WiFi coverage.  Maybe I’m just spoiled by MIMO, but the ZyXEL was essentially useless at WiFi—it would have required me to run a separate access point just to cover the whole house, and that’s not evening addressing the low throughput from the main unit.

The WNR3500 had worked well until the arrival of smartphones and Apples products in the house, so I decided to give NetGear another chance.  I’ve been completely satisfied with the WNDR4500, and going by the graph so has my smartphone.