S h o r t S t o r i e s

// Tales from software development

Archive for May 2011

Excel VLOOKUP always treats ? as a wildcard

leave a comment »

Earlier in the week one of our customers provided some data in an Excel spreadsheet.

I needed to match up the coded data values in the spreadsheet with coded values in our database and create some SQL INSERT statements for the mappings.

Rather than do this manually, I executed a SQL query to get a list of the codes from our database and added these to an additional worksheet in the spreadsheet. Then I added a formula on each row of the customer’s worksheet that used the Excel VLOOKUP function to match the customer specified code and the codes in my worksheet and return the key value, located in an offset column, for that row.

Then I added a CONCATENATE formula in another column to build the SQL INSERT statement for each coded value. It all seemed to work rather well until I looked carefully at the INSERT statements generated and realised that some of these were garbage.

The first incorrect INSERT statement mapped a code of ???? to the key value for the DBCT code. How could VLOOKUP match ???? with DBCT ? I looked at the next incorrect INSERT statement – this one was for a code of ????CR and VLOOKUP had matched it with the DBCTCR code.

The customer had used a question mark in their code values to indicate an unknown code value. In other words, ???? simply represented an unknown code and ????CT represented an unknown code that probably ended in CT. I had assumed that VLOOKUP would fail to make a match but slowly it dawned on me that it was actually performing a wildcard match so that ???? was matching the first code with exactly four characters and ????CT was matching the first code with six characters that ended with ‘CT’.

There’s no way of turning this behaviour off so I had to update the lookup formula so that it compared the matched value with the lookup value and, if these were the same, then it performed the lookup to get the key value.

Written by Sea Monkey

May 31, 2011 at 8:00 pm

Posted in General

Tagged with

svn:externals for a single non-text file doesn’t work

leave a comment »

I was tidying up the company’s Subversion repository the other day and decided it was time to move all the duplicate copies of our strong name key file into a single location and create svn:externals links in each application’s source tree to reference the single copy of the file.

After adding “/Code Signing/Microsoft .NET/Vitality.snk” to the repository I added an svn:externals property on the “/trunk/build/signing/” folder in the first application’s source tree to reference the key file:

svn:externals “^/Code Signing/Microsoft .NET/Vitality.snk” Vitality.snk 

Everything seemed to work as expected except the continuous integration build broke. When I looked at the build log the problem was that the Vitality.snk file was missing.

I checked the application source tree out to a temporary folder using TortoiseSVN and the Vitality.snk file was exactly where I expected it to be – in the “/trunk/build/signing/” folder.

As the build uses the command line Subversion client, I decided that I’d better do another checkout test using svn.exe. This failed with the message:

svn: warning: Unrecognized line ending style

After a few minutes of Googling I stumbled across this bug report:

http://subversion.tigris.org/ds/viewMessage.do?dsForumId=463&dsMessageId=1128735

So, the problem appears to be specific to having a single svn:externals reference for a non-text file.

My circumvention was to put the svn:externals property on the /trunk/build folder and reference the folder containing the key file instead of the file itself:

svn:externals “^/Code Signing/Microsoft .NET” Signing

Written by Sea Monkey

May 27, 2011 at 7:00 pm

Posted in Subversion

Tagged with