Excel CSV output using something other than commas

While dealing with the ICD 10 codes, we found that the SQL import process was adding spaces to text fields because of the tab delimiters we were using. A naive attempt to use the CSV format was quickly killed because the ICD 10 descriptions include commas, as I already knew from using J.

Notepad++ choked on the massive find-and-replace operations (I should probably see about submitting a bug). While it would complete the operation, somewhere along the way it would corrupt the file.

While something like dnGREP would probably be the right tool for find/replace in massive files, I instead used Excel 2010 to output the CSV using the pipe character ( | ) as the delimiter.

This is a kind of secret, as Excel doesn’t ask you what delimiter you want to use. Instead, you have to globally change the delimiter for Windows:

1. Open the Control Panel and select “Region and Language”.

2015-07-09 15_16_16-All Control Panel Items

2. Click on “Additional settings…”

2015-07-09 15_17_03-Region and Language

3. Change the “List separator” from a comma ( , ) to the character you want.

2015-07-09 15_16_44-Customize Format

And then remember to do this all again when you’re done, because you probably want your default to stay a comma.

Excel CSV output using something other than commas

ICD 10 code hierarchy, over-helpful Excel, and Jsoftware.com

I recently generated the parent-child and ancestor-progeny data for the ICD-10 diagnostic codes. You can find them on the GitHub repo I set up.

While generating the codes was a pretty straightforward matter of =vlookup() and =left(), I discovered only after exporting from Excel 2010 to a tab-separated text format that Excel or Word had converted my ASCII dash (-) and ellipses (…) into special dash (–) and ellipses (…) characters somewhere along the way.

I was using Notepad++ to review the file generated and I decided to determine just what characters existed in the CDC-provided data. I built up the following PCRE regex one piece at a time until I had the exact list (case insensitive):

[^-0-9A-Z \t\r\n.,\[\]/()'%<>=+%]

Of course, upon reflection, I just realized that J has a great way to find the characters:

/:~ ~. 1!:1 <'C:/Path/ICD10/icd10withHierarchy.txt'
%&'()+,-./0123456789<=>ABCDEFGHIJKLMNOPQRSTUVWXYZ[]abcdefghijklmnopqrstuvwxyz

view raw
FindNubWithJ.ijs
hosted with ❤ by GitHub

Right after the command is the result, which starts with a tab, line feed, carriage return, and then a space before the percent sign (%).

1!:1 <'filename' is just the notation for reading a file. ~. is the Nub verb, which removes all duplicates from an array (of which a string is a kind). /:~ determines the sort order for an array and applies it to itself.

ICD 10 code hierarchy, over-helpful Excel, and Jsoftware.com

Team Charter – DC SUG – “Are We Done Yet?”

I attended the “Are We Done Yet?” meetup of the DC Scrum Users Group last night. @RichardKCheng did a good job as the trainer. His slides are available as a PDF.

The Team Charter is the team’s self-realized, self-authored norms for how they work, unlike the Project Charter that is handed down from on-high. This includes things like the schedule, definition of done, project understanding, and team rules. There’s no reason I can’t write one up for our team tomorrow.

Some of the stories told and questions ask definitely point to other teams needing a Team Charter more than my team does. We’re already on board with timeliness and professionalism, and we have a decent Definition of Done. Even so, it certainly won’t hurt. Rules for ending horseplay, being available during core hours, and paying attention during meetings are only necessary where the team doesn’t already do these things.

For the “Definition of Done” and “Definition of Ready” themselves, Richard spoke to defining these at various levels:

  1. Ready to Start
  2. Project Backlog Item (PBI)
  3. Sprint
  4. Release

The “Definition of Ready” is essentially the “Definition of Done” for “Getting a User Story/Requirement Ready.” The backlog should be prioritized and refined before Sprint Planning occurs so that there is a supply of PBIs that are Ready. Richard recommended having a refinement meeting several days before Sprint Planning and then having a developer review the PBIs help get to this Ready state.

One of the key recommendations of the night, which should be obvious once you understand the principles of short feedback loops is that the more things you put off doing until the Release level, the greater your risk of failure. As much as you can while being efficient, move activities from the Release level to the Sprint level, and from the Sprint level to the PBI level.

Maybe security scans are onerous and time-consuming, but there could be ways of running part of the scan every Sprint, which thus gives the team the opportunity to address the findings much earlier.

While multiple weeks of regression testing can’t, by definition, be performed every two weeks, creating automated tests (i.e. Coded UI Tests) can permit you to get much of the value at the Sprint or even the PBI level. Why not run the automated tests after every check-in?

All in all, a good meetup, but mostly a confirmation of what I suspected.

Team Charter – DC SUG – “Are We Done Yet?”

Android Loadout

I’ve recently been pretty pleased with the experience of using my Samsung Galaxy Tab 4 (7″). Here’s my loadout, with Nova Launcher handling my home screen.

Android screen 1Android screen 2Android screen 3

I’ve pixelated names and some personal details. I have two screens of widgets flanking my home screen that has nine icon groups as well as a few additional widgets.

Below are all of the widgets, icons, and groups spelled out.

Continue reading “Android Loadout”

Android Loadout

My team’s Definition of Done

Our “Definition of Done” should have been written up long ago. While my team may act as the model “Agile” team in our company, we certainly have a long way to go.

The following is our first good draft of our “Definition of Done,” and a lot of it is about how we use Team Foundation Server (TFS), rather than productive activities that go along with the steps.

Without further ado, I present for your edification or amusement, one team’s

Definition of Done

Requirement

  • Proposed (set to Active)
    • The Requirements Review Checklist has been answered.
    • The Area Path, Iteration Path, and Release Version are accurate.
    • The Requirement has been committed to a sprint.
    • The CCB CR Number and Size are populated.
    • Not Blocked (Blocked set to “No”)
    • At least one child Task has been created.
  • Active (set to Resolved)
    • The requirement has been met.
    • All child Tasks and Bugs are Resolved or Closed.
  • Resolved (set to Closed)
    • Verified that the bug requirement has been met.
    • Test Scripts/Test Plan has been updated if applicable.
    • Wiki documentation has been updated if applicable.
    • Feature documentation has been updated.

Bug

  • Active (set to Resolved)
    • All child Tasks are Resolved or Closed.
    • View the parent Requirement to determine if this is the last child to be resolved (in order to then resolve the parent).
    • The Root Cause is set to the appropriate value (not “Unknown”).
    • The Reason is set to the appropriate value (“Fixed” unless Path 3 below).
    • The Area Path, Iteration Path, and Release Version are accurate.
    • The Bug has been addressed:
      • Path 1: Bug has been fixed in the data or environment and no longer occurs.
      • Path 2: Bug has been fixed in the code and will not occur after a new build (nightly).
      • Path 3: Bug cannot be reproduced, needs more detail, or reports as-designed functionality.
  • Resolved (set to Closed)
    • Verified that the bug has been addressed.
    • Test Scripts/Test Plan has been updated if applicable.

Task

  • Proposed (set to Active)
    • Remaining Work has been set according to the Size of the parent requirement and/or the developer’s estimate.
  • Active (set to Resolved)
    • Task is accomplished.
    • Remaining Work is 0 and Completed Work accurately represents the hours spent on the Task.
    • The Area Path, Iteration Path, and Release Version are accurate.
    • View the parent Bug or Requirement to determine if this is the last child to be resolved (in order to then resolve the parent).
  • Resolved (set to Closed)
    • Unit Tests are updated.
    • Code Review is completed.

Sprint

  • Sprint Retrospective
  • Team Sprint Review
  • Internal Sprint Review (cross-team functionality)
  • “Sprint Review and Approvals” form drafted.
  • Customer Sprint Review and “Sprint Review and Approvals” form signed.
  • The Release Version of all completed CRs populated.
  • Release Notes draft updated.
  • The Coded UI Tests (CUIT) pass.

Release

  • Regression Testing complete.
  • All Change Requests in the release are Closed (in cooperation with the Change Management team).
  • All Requirements and Bugs in the release are Closed.
  • All documentation included in the release package submitted (Change Management Team checklist).
  • Informational meeting and notes provided to the Help Desk, Documentation Team, and Trainers.
My team’s Definition of Done

Paste through remote desktop connections

I have to tunnel through three remote desktop connections in order to run SSMS on a computer that is allowed to connect to a production SQL server. Somewhere in the tunnel I lose the ability to paste text from my local machine into the remote machine. Generally I’m just hunting for specific data, so it isn’t a big deal to compose new SQL for that purpose.

At other times, however, it’s a real pain having to retype a long query that I’ve already composed and run on a test instance. In those situations, I hit WIN + b, thanks to AutoHotKey:

#b:: ; Simulate typing of the text on the clipboard
KeyWait, LWin ; Ensure that the Windows key has been lifted.
KeyWait, RWin
ToPaste = %ClipBoard% ; Convert to text
Sleep 100
StringReplace, ToPaste, ToPaste, `r`n, `n, All ; Prevent CR+LF from causing two newlines
Sleep 100
SendRaw %ToPaste% ; SendRaw simulates typing the characters exactly, including AHK's special !^#+ characters
Return

You get to watch the computer do the typing, so be patient. If you press any keys or click around, this script will just keep on typing and mix in your live actions with what it’s typing.

You do have to watch out for the helpful features of whatever program you’re “pasting” into. Auto-indent exaggerates already-indented text that you “paste” and sometimes auto-complete suggestions are accepted in place of what you wanted.

Regardless, this little script saves time and frustration.

Paste through remote desktop connections

Clean Paste

While there are times that I appreciate the way copy-paste retains formatting in Windows, it very frequently causes headaches. Some of the applications in the Office suite provide the ability to paste without formatting, but there are still many times that unwanted formatting tags along or causes problems.

AutoHotKey to the rescue!

The following little script lets me hit the Windows key and “v” and get a clean paste without formatting:

; Copied from http://www.autohotkey.com/board/topic/10412-paste-plain-text-and-copycut/
#v:: ; Text–only paste from ClipBoard | WIN and V keys are the hotkey
KeyWait, LWin ; Wait for the Windows key to be lifted,
KeyWait, RWin ; since it would sometimes combine with the pasted text.
OldClip = %ClipBoardAll% ; Save formatted text for later
ClipBoard = %ClipBoard% ; Convert to text
Send ^v ; For best compatibility: SendPlay | simulates pressing CRTL and V keys
Sleep 50 ; Don't change clipboard while it is pasted! (Sleep > 0)
ClipBoard = %OldClip% ; Restore original ClipBoard
VarSetCapacity(OldClip, 0) ; Free memory
Return

view raw
AHK-PastePlain.ahk
hosted with ❤ by GitHub

This is pretty much just copied from a forum post. Nice and fast and no bad formatting.

Clean Paste

The Current Iteration in TFS

I’ve wanted to write queries based on the current sprint in TFS (Team Foundation Server), as have quite a few others, including one developer at my office yesterday.

Looks like Microsoft listened. @CurrentIteration is being added as a token.

That’s great, of course. When looking to write a query against the current sprint, however, you are in danger of losing sight of unclosed work items in previous sprints. When you reach for @CurrentIteration, you probably just mean “all unfinished work that has been committed to a sprint.” If you filter to a single sprint, you’ll miss any stragglers you failed to close or move forward from previous sprints.

Consider using the following pattern, where “ScrumOfScrumsRelease 1.0.0.0” is your backlog path, and all of your sprint paths are children to that:

Under backlog iteration node, but not equal to the backlog iteration node. Not closed.
Under backlog iteration node, but not equal to the backlog iteration node. Also not Closed.

This will also catch any items that weren’t closed in your previous sprints.

The Current Iteration in TFS

Project Management is Communication

As I reflect on the PMP training material that discusses the knowledge areas and process groups, I am struck that, at its core, project management is communication. While other jobs or skill sets may well also boil down to communication, project management as presented by PMI is all about getting everyone on the same page.

If you think about the project of building a pyramid, the project manager does not cut stone, haul any tools or supplies, or even take measurements to ensure the construction is occurring as planned. The project manager contributes to the accomplishment of the project by ensuring that important communication is occurring among team members. This communication is the means by which teams coordinate their activity. The long list of best practices is a kind of checklist of the various things that should be communicated and probably written down.

Project Management is Communication

PM PrepCast

I’m working on getting the PMP certification, mostly for upward (salary) mobility.

I was pretty intimidated with the project hours requirement, but after talking with Nathan Scott, it ended up being far simpler than I feared. Looks like the key is to ensure you have someone who can vouch for the work you claim.

I’m now working my way through the PM PrepCast. I was skeptical before purchasing, since I picked it up as the cheapest option out there, and I’m still crossing my fingers that their 35-hour certificate will count, but I’m impressed with the video podcasts. While certainly not groundbreaking, they cover the material in a sensible fashion and discuss where the PMI ideal may not line up with the truth on the ground, but also put into perspective that the activity areas take a wide variety of forms. Just because a lot of documentation is implied, the documentation doesn’t have to be that onerous.

PM PrepCast