Configuring an Excel ODBC Data Source in Windows 7

A very neat little trick  to compare tables of data in Microsoft Excel to reveal differences, is to highlight the data (all rows and columns containing data), and Define a name for the data, then use an ODBC Driver for Excel to subsequently query the tables as if they were a database.


Each tab in the Excel Workbook can contain a different table of (similar) data and the tables can be linked in Microsoft Query Editor so that SQL queries can be performed against the linked tables to reveal all rows of  data where the same information exists in two separate columns of two separate tables, or even more useful (in my experience) the rows of data where the data that exists in one column of one table doesn’t exist in the other.

In Windows 7, the Data Sources (ODBC) management console will only show drivers for SQL Server, and not the list of different drivers seen in previous versions of Windows.

In order to create an Excel Data Source, you need to close the 64-bit version of the management console and run the following instead..

In the 32-bit odbcad32.exe (shown below), upon clicking Add, you’ll see the long list from which you can choose Excel Driver and point it at your spreadsheet that has the defined data within it.

 

 

Note, if you intend to use this method to query the data in multiple Excel tables using the Query Analyzer, you will have to save a copy of the workbook in the older Excel 97-2000 .xls format before you create an ODBC Data Source for it.

Upon returning to Excel, Create a new tab in the same workbook or create a new Workbook if you like, and Open Query Analyzer as shown…

Choose the Data Source Name (DSN) created using the 32 bit ODBC dialog, choose what tables (Definitions) you wish to include in your query and manually join the tables containing the similar data, then edit the SQL statement as required (usually just changing = for <> produces the sort of results I’m looking for, i.e. differences between the two columns, not similarities.

My personal preference when it comes to comparing columns of data is to export the columns to separate text files, WinSCP the text files onto a Linux/UNIX machine, then use cat | sort | uniq on each file, then comm (not diff) to perform the comparison and show entries in one but not the other of the two files being compared.  I’ll endeavour to cover this method in another post to compliment this Windows 7 oriented post.

[paypal-donation]

Did you like this?
Tip cyberfella with Cryptocurrency

Donate Bitcoin to cyberfella

Scan to Donate Bitcoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some bitcoin:

Donate Bitcoin Cash to cyberfella

Scan to Donate Bitcoin Cash to cyberfella
Scan the QR code or copy the address below into your wallet to send bitcoin:

Donate Ethereum to cyberfella

Scan to Donate Ethereum to cyberfella
Scan the QR code or copy the address below into your wallet to send some Ether:

Donate Litecoin to cyberfella

Scan to Donate Litecoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some Litecoin:

Donate Monero to cyberfella

Scan to Donate Monero to cyberfella
Scan the QR code or copy the address below into your wallet to send some Monero:

Donate ZCash to cyberfella

Scan to Donate ZCash to cyberfella
Scan the QR code or copy the address below into your wallet to send some ZCash:

Edit Openbox menus in Crunchbang Linux

Unlike some of the heavier, fully functional desktop environments typically provided by the top five on Distrowatch, Openbox used by Crunchbang will not always automatically add the names of newly installed programs to the menu used to subsequently invoke them.

Most folks who are not as far down the rabbit hole as I am, understandably just want a desktop that works but they should pause for a moment before turning off to the idea of Openbox and Crunchbang for the following two reasons.

1. It makes full disk encryption (not just your home directory) available to you during installation which is very reassuring if you should get your laptop stolen.

2. Each time I consider parting company with it and going back to a heavier distro, I find I can’t bring myself to do it because it does everything I need it to.  Plus it does it more efficiently and in as minimalist a way as my puny hardware resources could ever hope for, so why would I?

On top of the Linux kernel, you’ll already be running sufficient packages put into place by the installation procedure to provide a working desktop environment that handles a bunch of important stuff you won’t have thought about, such as handling removable devices such as usb sticks, encrypting the files that get written down to disk, searching for wireless lans or sending a DHCP request if you plug into a network in the hope that it’ll learn of some nearby DNS servers so that your web browser will work when you ask for google.com, but depending on how lightweight your chosen distro is in nature, it may not have much else.  Crunchbang is one of these.  It does the hard stuff up front, and leaves you with a pretty blank canvas on which to build and have fun.  For those of you who say I only need web and email, that’s nonsense. There’s a whole bunch of stuff you need for web and email at the application level to work properly but rest assured Crunchbang already provides it, despite it’s blank, black appearance.

It’ll even keep on working when you find yourself needing to do real work. I have to successfully run my own company using just my laptop during the week when I’m away from my home and the rest of my infrastructure and also use it for entertainment so there’s really no better test than that. Reviews are great, but the proof of the pudding is in the eating. I want to get the work done, and that means I want a fast, super responsive interface that doesn’t mess about. My laptop isn’t for impressing my friends with, it’s key to my survival and my only source of free entertainment. It has to deliver and if it comes up short, I will find out quickly. I also like messing about with photographs so those extra system resources are appreciated.

Additional functionality comes in the form of freely available modules (programs and their dependent libraries) installed and removed at will using Synaptic Package Manager which downloads all the software you’ll ever need from known repositories as and when functionality is required or retired on your desktop – much like your iphone or android phone, only crunchbang doesn’t carry the advertising or any of the bad stuff that leaves you wondering if your computer is actually free or even your own.  Install it and you’ll see much blackness!  No childish fisher-price icons here to lure in paying consumers, just a blank, black canvas and a package manager.  That’s as simple as it gets.

BUT, as I started out saying, it won’t necessarily add the programs to your desktop menu after they’re installed.  Before you let that become an issue for you and miss out on feeling like that kid felt in the 70’s when he/she opened that box and smelt that plastic, read on.  It’s easy to edit the menu to add the programs you’ve just installed.

 

Settings, Openbox, Edit menu.xml (not Reconfigure as shown – thats for afterwards).

The menu.xml file will open in geany text editor.  Anything between <item> and </item> is a, well, item.  So copy an existing block of code and paste it in somewhere appropriate according to what type of program it is (Media, Office, Graphics etc), then just modify the label and executable as required.  I added the xcalc calculator (shown below).

When you’re happy with your edit, save it, then Settings, Openbox, Reconfigure to re-load the .xml file you just modified and see the new item in the menu.  Test it to make sure it works.

 

Did you like this?
Tip cyberfella with Cryptocurrency

Donate Bitcoin to cyberfella

Scan to Donate Bitcoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some bitcoin:

Donate Bitcoin Cash to cyberfella

Scan to Donate Bitcoin Cash to cyberfella
Scan the QR code or copy the address below into your wallet to send bitcoin:

Donate Ethereum to cyberfella

Scan to Donate Ethereum to cyberfella
Scan the QR code or copy the address below into your wallet to send some Ether:

Donate Litecoin to cyberfella

Scan to Donate Litecoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some Litecoin:

Donate Monero to cyberfella

Scan to Donate Monero to cyberfella
Scan the QR code or copy the address below into your wallet to send some Monero:

Donate ZCash to cyberfella

Scan to Donate ZCash to cyberfella
Scan the QR code or copy the address below into your wallet to send some ZCash:

Xubuntu 64 bit vs Crunchbang 64 bit

My recent purchase of a Lenovo IdeaCentre Q180 has proved to be interesting.  Hey, Lenovo, your choice of a Radeon graphics chipset was a poor one.  I think.

64 bit Linux has always interested me.  The real UNIXes like HPUX and AIX are 64 bit and rock solid number crunching beasts, so the prospect of running 64bit UNIX* for free* on my every day machine (without the cost of purchasing a Mac) has always interested me.  The trouble is, 64bit Linux has a checkered past on the desktop with showstopping issues being graphics driver support, flash plugin, and support for scanning and printing, i.e. all the things that a 64bit UNIX number crunching server would never have to worry about.

Despite this, I figure things must have moved on a bit by now, especially with so many inexpensive 64bit CPU’s gracing the systemboards of most modern machines so I’d give it another go.  The first thing to go was the 32bit installation of Xubuntu 11.10 on my 11.6″ Dell Inspiron 11z laptop – a trusty servant and a faultless OS, in favour of trialling 64bit Cruncbang Statler – and not the BPM (backported modules) one on Linux kernel 3, but the more stable, stoical 2.6 Linux kernel.  This is 64 bit desktop OS territory so stability is important, and buggy, bleeding edge software modules are not welcome here.  Not on my machine anyway.

The Lenovo Ideacentre Q180 didn’t come with an OS – an attractive proposition, not paying for an unwanted Microsoft license, and one which helped seal the deal if I’m honest.  I installed 32bit Xubuntu with all my usual post-install customisations, i.e. adding the Medibuntu repository, installing recommended hardware drivers and a full apt-get update && apt-get upgrade and reboot, and finally adding Ad Block Plus and DownThemAll plugins to firefox, and installing Ubuntu One and Dropbox to re-sync all my important stuff stored in the cloud.  After that, for me, apps are just apt-get installed on demand as and when I need them, such as the wonderfully convenient gscan2pdf for scanning receipts and saving them as a pdf in the cloud for safe keeping.  I’m not spending hours trying to think of all the software I need and installing it before I need it.  Life’s too short.  Go do something else instead.  If I want to rip and re-encode a DVD to divx, I’ll just apt-get install dvdrip rar libdvdcss2 as and when I need to.  Not that I’d ever want to do that of course.  I digress.

Once I’d verified that xubuntu 32 ran OK on the hardware, I blew it away in favour of trialling 64bit Xubuntu.  My initial tests with Crunchbang 64 on the laptop were proving to be very very successful indeed.  It’s been on there a couple weeks now, and I have no intention of replacing it anytime soon.  So a win for Crunchbang.  Yay.

I had to download and use unetbootin to create a bootable live usb stick from the downloaded .iso image since the startup disk creator packaged with the OS just didn’t like booting on the Lenovo.  Not to worry.  unetbootin worked a treat.  Installation went without a hitch and hardware drivers installed etc as per the normal routine detailed above.  I was all set to feel that “new vanilla OS” warm comfortable feeling experienced by a graffiti artist when they spot a white wall, or a surfer when turns up at an empty break, when all of a sudden the user interface started to play up.  Frown time.

After some research I quickly realise there are issues with Radeon drivers and Linux full-stop, let alone on 64 bit linux, despite ATI’s claim that their driver supports both 32 bit and 64bit Linux.  It also claims to support RedHat and Suse if you look closely enough, which left me wondering about the “automatic” install on Ubuntu I’d just done.

I’ve tried a number of things but the graphics card driver is definitely problematic.  So it’s going to be Crunchbang 64 on the off chance it’s OK, with a post-install of XFCE for a slightly more user friendly experience (seeing as how Gnome has gone right off the rails since 3.0).  Failing that, I’ll be going back to the more tried and true 32 bit distributions in the hope that the graphics driver behaves itself better.  Reliability is king.  Having a 640 horsepower supercar is no good if it breaks down.   You’re better off with a 320 horsepower Evo.

Did you like this?
Tip cyberfella with Cryptocurrency

Donate Bitcoin to cyberfella

Scan to Donate Bitcoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some bitcoin:

Donate Bitcoin Cash to cyberfella

Scan to Donate Bitcoin Cash to cyberfella
Scan the QR code or copy the address below into your wallet to send bitcoin:

Donate Ethereum to cyberfella

Scan to Donate Ethereum to cyberfella
Scan the QR code or copy the address below into your wallet to send some Ether:

Donate Litecoin to cyberfella

Scan to Donate Litecoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some Litecoin:

Donate Monero to cyberfella

Scan to Donate Monero to cyberfella
Scan the QR code or copy the address below into your wallet to send some Monero:

Donate ZCash to cyberfella

Scan to Donate ZCash to cyberfella
Scan the QR code or copy the address below into your wallet to send some ZCash:

New PC time. Meet the Lenovo Ideacenter Q180.

Despite being a massive fan of the Acer Aspire Revo 3600 that I bought a few years ago, my little £149.99 nettop is just a touch slow these days and could probably do with being upgraded.

My instant reaction was to go for the £174.97 Acer Aspire Revo 3700 and it would be a great choice too, however it’s 1.8GHz CPU would appear to have been trumped slightly by the 2.13GHz CPU in the £179.99 offering from Lenovo – The IdeaCenter Q180.

Yes you read those prices right.  I use Linux (free) and keep the price of my hardware as low as possible.  Any compromises on performance will be offset by operating system choice and subsequent tuning, although I’m not expecting to have to do an awful lot of that given it’s “whopping” 2GB RAM, 2.13GHz CPU and ATI Radeon graphics chip (full spec given below).  Watch this space.

Continued here..

Xubuntu 64 bit vs Crunchbang 64 bit

Processor

Intel Atom D2700 Dual Core 2.13GHz,
1MB L2 Cache

Memory

2GB DDR3 1066MHZ
soDIMM

Hard Drive

320GB SATA

Optical Drive

None

Software

Operating system: DOS

Display

Monitor Not Included

Graphics

ATI Radeon HD 6450 – 512MB

Networking

LAN: 10/100/1000 Gigabit Fast Ethernet
WLAN: 802.11b/g/n

Interfaces

3 x USB 2.0
2 x USB 3.0
1 x HDMI
1 x SPDIF

Expansion

7 in 1 Card Reader

Warranty

1 Year Manufacturer Warranty

Did you like this?
Tip cyberfella with Cryptocurrency

Donate Bitcoin to cyberfella

Scan to Donate Bitcoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some bitcoin:

Donate Bitcoin Cash to cyberfella

Scan to Donate Bitcoin Cash to cyberfella
Scan the QR code or copy the address below into your wallet to send bitcoin:

Donate Ethereum to cyberfella

Scan to Donate Ethereum to cyberfella
Scan the QR code or copy the address below into your wallet to send some Ether:

Donate Litecoin to cyberfella

Scan to Donate Litecoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some Litecoin:

Donate Monero to cyberfella

Scan to Donate Monero to cyberfella
Scan the QR code or copy the address below into your wallet to send some Monero:

Donate ZCash to cyberfella

Scan to Donate ZCash to cyberfella
Scan the QR code or copy the address below into your wallet to send some ZCash: