Diigo Bookmarks 05/20/2011
Reply
List of All Articles with the Topic "Windows"
Nearest Postcode Search in Microsoft Excel
I need to find a load of addresses by proximity to a postcode. I have the addresses in a Microsoft Access database.
- Download Paul Jenkins’s UK Postcode csv and import into Access as a table
- Create a query on your own table adding the following calculated field:
<pre>PCregion: Trim( Left( [My Table]![Postcode], InStr( [My Table]![Postcode], ” ” ) ) )</pre> - Create a second query that joins the above query to the imported postcode table. Join on the “PCregion” field created above. Also add in the latitude and longitude fields from the postcodes table
- …
Well, I couldn’t be bothered to fight with Access so I exported the key tables and used Excel instead!
The principals are the same. Load the tables as tabs, load the Postcode data as a tab. Create a front sheet containing the reference Postcode (Create a Named Cell for this “STARTREGION”). Translate the reference Postcode to Latitude and Longitude by doing a VLOOKUP to the Postcode table, e.g.:
=VLOOKUP(TRIM(LEFT(RC2,SEARCH(" ",RC2 & " "))),UK_PostCodes!C1:C4,3,FALSE)
Where RC2 contains the reference Postcode and “UK_PostCodes!C1:C4″ is the postcodes table (column 3 contains the Latitude and 4 the Longitude). Note that I’ve used R1C1 reference style as it is easier to mix fixed and relative references.
Now use the same formula in the main data tables so that you have Lat. and Long. columns added based on the Postcode on each line.
Finally add a “Distance” column to each data table with the following formula:
=ROUND(DEGREES(ACOS(SIN(RADIANS(STARTLAT))*SIN(RADIANS(RC28))+COS(RADIANS(STARTLAT))*COS(RADIANS(RC28))*COS(RADIANS(STARTLON-RC29))))*60*1.1515,0)
In this case, we’ve used the STARTREGION named cell and the Lat./Long. data is in columns 28 & 29 respectively.
The calculation and Postcode reference data came from HM2K’s website: How to search by nearest uk postcode in php.
Cygwin BASH function to open the latest version of a document
One handy function I’ve added to .bashrc (so it is always available) under Cygwin (the LINUX command environment for Windows) works out the current working version of a document. It assumes that you keep copies that have a version number or date in the file name that will sort correctly.
# Opens the latest version of a file using the Windows default application
# Assumes that you have a range of files that can be identified using some for of prefix
# and that the last part of the file contains a version number or date that sorts in the correct order
# e.g. myfile-lots-of-rubbish-20090720-01.doc & myfile-lots-of-rubbish-20090723.01.doc
# Two arguments are required. The first is the PATH to search in. The 2nd is the shared file prefix (e.g. 'myfile-lots-of-rubbish-')
# Put single quotes around the arguments to prevent them from being GLOBed by the shell.
# Only searches in the GIVEN path, not subfolders.
# Use with an ALIAS to have an easy way of opening a specific file from the shell
function cyOpenLatest {
# We have to use find rather than ls because of shell expansion issues in the arguments (and problems with spaces in file/folder names)
res=`find "$1" -iname "${2}*" -type f -prune -printf '%f\n' | sort -r | head -1`
# Work out the file type from the .ext
ext=`echo $res | sed "s/.*\.//"`
# Add whatever you want to this list above the *)
case $ext in
doc*) TYPE="in Word" ;;
xls*) TYPE="in Excel" ;;
ppt*) TYPE="in PowerPoint" ;;
vsd*) TYPE="in Visio" ;;
*) TYPE="with Windows default application"
esac
if [ "$res" != "" ]; then
echo "Opening [$res] $TYPE"
cygstart "$res"
fi
}
You can use it with an alias like this:
alias gic="cyOpenLatest '$HOME/Documents/Here is a folder with a space or two/' 'a-document-'"
If you name your documents sensibly such as “a-document-2009-07-20.doc” or “a-document-v01.01.doc”, then the latest version of the file will be opened in the default application
Speeding up Cygwin
Yesterday I mentioned my success with Cygwin.
One issue I did have though was with the speed of startup. It was taking 15-20 seconds to start a BASH shell.
It turns out that this was a PATH issue. I went through my Windows PATH and cleared out the clutter. Now it takes just around 3-4 seconds for a full BASH login and less still for just running a script.
I now find myself using the BASH shell for all sorts of things and I’ve set up a number of alias’s to switch to folders I’m using a lot and to open common documents.
One handy function I’ve added to .bashrc (so it is always available) works out the current working version of a document. It assumes that you keep copies that have a version number or date in the file name that will sort correctly.
You can find the code on my development blog.
Here are a few more alias’s I use:
alias np='cygstart "/cygdrive/c/Program Files/Notepad++/notepad++.exe"' alias c='cd /cygdrive/c/' alias d='cd /cygdrive/d/' alias work='cd "$HOME/Documents/Workdocs/"' alias pers='cd "$HOME/Documents/Persdocs/"' alias facebook='http://www.facebook.com'
Windows command prompt vs PowerShell vs Cygwin for remote backup scripts
I’ve been struggling with trying to get a new backup routine working for my Laptop.
I should point out that I have several complex requirements for backup so my needs are probably not average.
However, it really shouldn’t be this hard!
I need to use a combination of BZR (Bazaar) for document version control and RSYNC (for files that don’t need version control and for those folders that might contain files too big for version control systems – around 1/3 to 1/2 available memory).
All of the backups need to happen over a secure link since I am often outside of my home network – indeed quite often behind locked-down enterprise firewalls but that’s a story for another time. So I use SSH (Secure SHell) to manage the secure connection and transmission. Thankfully both BZR and RSYNC can both use SSH as a transport.
I don’t want to have to enter my remote system password loads of times though and this is where things started to get annoying. Using the Windows native versions of BZR, RSYNC and SSH I could not get a single shared password to work no matter what I tried.
I also had some problems trying to control the output from the various tools and use it to further control what happens next – for example getting an IP address and working out whether I am on a network and where that network is.
I tried to do this with a Windows command script first but even with the Windows 7 extensions it really is far to hard to get anything useful done and when I found myself turning to more and more utilities to help I thought “enough is enough”.
At that point I happened to be reading an article on Windows PowerShell, the .NET scripting host so I thought I’d give it another go (having tried it before). I soon found that, although powerful for controlling the WMI interface, it is desperately convoluted and annoying for general use.
So, realising that most of the tools I wanted to use have their roots in the UNIX world, it would make sense to try out the latest version of Cygwin. This has really come on a long way since it’s early days and is far more mature. It is also very much lighter in weight than the Microsoft provided UNIX services for Windows or whatever they are currently calling it. The Microsoft provided tools load perhaps a dozen services into memory permanently though they are rarely required. Cygwin only uses memory when it needs to.
After converting my backup script from Windows batch to a BASH script under Cygwin, I soon had everything working as I wanted it – including the seemingly intractable problem of the shared passwords, now using KEYCHAIN to manage the SSH-AGENT and keys. So now I only need to supply a password once, it is held reasonably securely in memory and used by SSH as and when required. I only need to enter it once per reboot.
The full script not only backs things up, it also auto-commits changes to BZR and changes network settings to match my current location and proxy server requirements. The later is not yet converted from batch as I don’t need it just at the moment.
Let me know if you are interested in a copy of the script and I’ll upload it somewhere.
Running Komodo Edit Open Source Code Editor Under Windows 7
The open source version of Komodo’s code editor and development environment Komodo Edit is a great tool for development. I use it for PHP, HTML, JavaScript and more.
However, I haven’t done any serious coding for a while so I haven’t needed to run it under Windows 7 even though I had it installed. When I did, I was disapointed to find it behaving very poorly. It wouldn’t resize properly without messing up the screen. I tried with some of the compatibility settings that Windows 7 gives you but that made no difference. I also tried an upgrade to the latest version.
A quick search didn’t reveal anything about Windows 7 specifically but I did spot a discussion about problems under Vista that were related to file permissions. Sure enough, making Komodo Edit run as Administrator fixed the issues.
I’ve had a few file permission issues under Windows 7, I’m fairly sure it is down to me messing around. However, it is clear that Windows Vista and Windows 7 are both rather sensitive to permissions issues which is worth bearing in mind.
Looking at %USERPROFILE%\AppData\Roaming\ActiveState\KomodoEdit, I could see that SYSTEM, my user and Administrators all had full access but that Administrator was the owner of some of the files. I can only summise that this is the issue.
As this needs rebuilding with Windows 7 RC, I haven’t the time to test further but certainly running as Administrator does the trick.
By the way, Komodo Edit is available for Mac and Linux as well as Windows. It has a big brother “Komodo IDE” with additional features if you need them. Both are highly configurable, support many languages and are based on the Mozilla code (like Firefox and Thunderbird) & can easily be extended with JavaScript.
How-to show a Message of the Day (MOTD) at the Windows Command Prompt
One of the features available under UNIX is the Message of the Day (MOTD). This is run every time you start a command prompt and displays the content of a file. In addition, the UNIX shells allow all sorts of stuff to be run and configured every time you start a new prompt using the .profile and .bashrc command files.
Windows users don’t generally expect that kind of flexibility from their command prompts. However, Windows does indeed support the use of an “autorun” into which you can shoe-horn any command you like.
So for my standard setup, I make the shell autorun run a “.profile.cmd” file that sits in the %USERPROFILE% folder. From that file, I can run anthing I like.
To set up a shell autorun, you have to edit the registry so the usual warnings to be careful and back things up apply. There are two locations you can set, one for the machine as a whole and one for the logged-in user.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Command Processor
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Command Processor
If you want to set an autorun for another user, you need to go down HKEY_USERS and find the appropriate one, it’s really easier just to log in!
In one or both of those locations, add a new “String Value” (REG_SZ) called “AutoRun” with the value:
%USERPROFILE%\.profile.cmd
Now create that file and put in a message such as:
@echo "Hello and welcome to my command prompt"
Save the file and open a new shell and you should see the message just after the Microsoft copyright.
This should work on all versions of Windows at least from XP onwards.
If you want to add this to a batch file to set up new machines, here is the command you need:
reg.exe ADD "HKLM\SOFTWARE\Microsoft\Command Processor" /v AutoRun /t REG_SZ /d ^%USERPROFILE^%\.profile.cmd /f
(Note that the above needs to go on a single line)
How-to use WebDAV on Windows Vista and Windows 7
Windows is supposed to have a built-in WebDAV client. However, it doesn’t ever seem to have been especially robust and certainly since Vista a lot of people (myself included) have found that it simply doesn’t work on many supposedly WebDAV enabled sites.
Thankfully there are a couple of free (and some not free) options that, while not as nicely integrated into Windows Explorer, do enable you to transfer files back and forth.
The two that I found were:
I’ve tried the first of these two and it seems to work OK. It’s not polished but it gets the job done. There is also an online version using a Java plugin if you don’t want to install the client.
It seems that the second option is no longer in active development. Don’t be fooled by the web site though, they have changed the license to freeware.
Now, at last, I can once again load multiple files to our corporate intranet on WebEx without having to switch to a Linux desktop!
Keeping Control: File and Folder Links for Windows Users
A good backup strategy for any computer involves keeping control of where stuff is stored. The fewer locations that contain files that change, the fewer locations have to be maintained.
UNIX users have always had the ability to keep things wherever they wanted and then to LINK that information into the required location. Basically, links create a link or tunnel between one file or folder and another. Most of the time, you will not notice that you’ve entered a tunnel and you are not interested really.
Windows users, however, have always been the poor cousins here. Stuck as we were in FATland, we had no access to fancy features such as links. So Microsoft in their inimitable fashion created a poor-man’s link – the Windows Shell Shortcut – so that the Windows GUI had some minimal capability (really only for menu’s and Windows Explorer).
Windows 2000 improved on this by introducing “Reparse Points” one form of which is the “Junction“. This is an extension to NTFS that allows folders to be joined (linked) to another location in the local volume space. Making junctions is not an obvious process, you can do it from the disk manager and there is a tool in the Windows 2000 server resource kit called linkd. The POSIX tools included in the resource kit contain the UNIX command ln which can also create junction points and hard links; fsutil in XP can also. There are some third party tools too.
It’s odd because I seem to remember that OS/2 had some kind of linking feature.
Anyway, links of the UNIX type are a massively useful feature that has finally (with Vista, Windows 2008 and beyond) made it fully to NTFS and Windows.
Vista, Windows 2008 and Windows 7 all have a command line tool called mklink. This can be used like the Unix ln command to create both hard links (which must be on the same volume) and soft links. Soft links under Windows can, in fact, span across SMB network drives as well.
You might also like to look at another free tool called “Link Shell Extension” by Hermann Schinagl. This integrates into Windows Explorer, the web site also has a more complete explanation of the history of links in Windows. LSE does a number of clever things and is well worth a look. Hermann also has a “dupmerge” tool on his web site that will replace duplicated files with hard links.
So now, if we want to tweak the HOSTS file for example (c:\windows\system32\drivers\etc\hosts), we don’t need to leave in place since that would mean that we would need an extra backup routine. Instead, copy it to somewhere that already gets backed up. Delete the original file and then from the command line:
mklink c:\windows\system32\drivers\etc\hosts %USERPROFILE%\BACKUPS\hosts
Now you can edit the hosts file from either location, there is only one file (in %USERPROFILE%\BACKUPS). The difference being that even if you delete the file from its normal location, it will still exist in the “real” location. If you delete it from its “real” place in BACKUPS of course, the link will be broken and wont work.
To link a complete folder, it is the same command with a /D parameter added. For example, I keep a folder of command line utilities such as ls, ssh and rsync in a folder on a USB pen drive. I sync that folder to the BACKUPS location on my hard drive for convenience but I need the folder in my PATH otherwise its hard to execute the utilities. I don’t want a really long path, it’s bad enough already, so I link the folder to c:\cmd with the following:
mklink /D c:\cmd %USERPROFILE%\BACKUPS\PEN\cmd
Now I add c:\cmd to the path and the utilities seem to be in both places.
I’ve said in other posts that I like to reinstall Windows now and again but it can be a pain to restore all of the document files. Similarly, if you keep multiple operating systems on your hard drive, how do you keep your documents sorted? One way is to put all documents, videos, music, etc. onto a separate partition. Now, instead of going mad with the Windows registry trying to relocate your normal documents folders to another drive. Simply delete the normal documents folder – %USERPROFILE%\Documents\ under Windows 7 and relink it to the appropriate folder on the other drive as so:
mklink /D %USERPROFILE%\Documents d:\Docs
Put this in a script that you run when you reinstall Windows and its easy and quick.
One final note. You may find a few pieces of software that cannot cope with links. Certainly Subversion cannot though Bazaar can. Windows Explorer seems OK though as do utilities such as RSYNC.
Version Control for Mortals
Version control systems (VCS, or Revision Control Systems or Source Control Systems) are designed for software developers. They enable one or more people to work on source code, annotate changes, split and merge the code, link to bug tracking systems and a number of other things that are interesting to developers but not to most people!
Indeed then, for most people, you might expect that version control systems are not interesting at all. But you’d be wrong.
What makes them interesting to most people is the fact that most of us are very poor at looking after those all-important files that make up our business assets and often personal assets too. We copy, save and delete stuff without giving too much thought to what we are doing. Then later on we scratch our heads and wonder what happened to xyz piece of critical information. I wonder how many times you’ve gone through your emails to get a document back that you know should be (and may well be) on your hard drive somewhere. With desktop search systems now all the rage, you will probably find the document but then you realised that you used it as a template for another document and accidentally saved over the top! Or it got deleted when you were tidying up the old project folders …
Well, in step the version control systems to save the day. They will benefit anyone who recognise the above scenarios.
I’ve been using a VCS for over a year now for my day-do-day documents. I’m glad I did too as I used it to recover most of my documents after a drive failure earlier this year.
I started by using Subversion (SVN). This is a Centralised Versioning System. It requires a central server that is the hub and master for all documents and changes. It is very well supported and many low-cost web hosts also provide Subversion servers.
It is not ideal though for managing general documents. Firstly it does get quite slow (especially for larger files) and changes can only be committed over a live network connection so it’s no good for disconnected work. Secondly, I found it very sensitive to how it was used and I’ve often managed to get my repository in a mess that was very hard and very time consuming to recover from. This is not acceptable in a system that you have to rely on. I’m sure its fine for its original purpose of source control but it is not so good for managing day-to-day work.
Next I looked at Distributed Version Control Systems. The most popular of these (the free ones anyway) are: Git, Mercurial and Bazaar. Mercurial seemed to be the one best developed for Windows so I tried that. It does seem reasonable but it seems to balk quite a bit at large’ish files (a few Mb, it seems that the Windows interface at least hangs quite often at least on Windows 7) and that makes it unsuitable for our needs. Git seems to have a lot going for it but is not so well developed for Windows and is rather more complex, I haven’t tried it at least yet. So that leaves Bazaar. I’m now using Bazaar in anger and I’m quite impressed. It seems to handle large files sensibly, it is easy to set up (really easy), it doesn’t complain when you move files and folders round, it doesn’t get in the way, it’s reasonably fast. You can also use it with a central repository too like SVN.
Bazaar comes with integration to Windows Explorer but you will probably want to look at the command line options too for automation.
I’ve set up a schedule that runs a commit of my main repository “workdocs” every morning, noon and afternoon (9am, 12pm and 4:30pm) and the extra data that is kept in the repository is simply backed up as part of the regular backup since it is just a hidden folder in the root of the “workdocs” folder.
I then commit changes manually as and when I want to after making significant changes to files.
For really critical files, you could couple Bazaar with the file change detection of SyncBackSE to automatically commit changes or you could use AutoHotKey to intercept the <ctrl>-s key combination to run a commit before or after doing a save.