Data Cleaning

What is data cleaning?

Data cleaning can mean removing duplicate records, corrupt records, and correcting and completing information already held in a database.

It can help avoid waste in mailings, and improve customer service.

I have now created a new domain, which will be used to promote the data cleaning side of my business.
dataclean.uk.com

I am unusual because I am offering a complete service, we can take a list of contacts and companies from a database, process them, then merge the results back into the original database system. My many years of database experience helps greatly with this process, and it is a lot more efficient than manually checking the database.

Among the services I am offering am:

• Finding duplicates, and dealing with them appropriately so that no data is lost.
• Updating individuals and companies that have moved, so their new address is on your database.
• Noting people who have opted out of marketing sales calls. In the UK there can be a fine for calling private numbers that are registered on the TPS list, or for calling businesses that are registered on the CTPS list.
• If someone is registered on http://www.the-bereavement-register.org.uk/ our customer's database can be updated to indicate the person has died. Receiving mail for someone who has died can be very upsetting for the relatives, and can make a business seem heartless. With this information the business can respond appropriately.
• I can update records so that the addresses match the Post Office's official address format.
• I can enhance data, by looking up telephone numbers of individuals and companies, obtaining turnover and staffing levels, and by retrieving key contact details for businesses.

Because the process is done in bulk, and I take responsibility for merging the results back into the master database the whole process can be very fast and efficient.

Google and Outlook Calendar Synch

Its easy to set-up two-way synchronization between Google's on-line calendar and Outlook.

Simply download the small add-in from here:

http://www.google.com/support/calendar/bin/answer.py?hl=en&answer=89955

During installation, supply login details for your Google account.

And that's it, changes to one calendar are reflected on the other.

Tested with Outlook 2010 running on Windows 7 64-bit

Could not load file or assembly 'Microsoft.SqlServer.BatchParser"

I had created an SQL Server tool kit, that allows me to document and generate code against SQL Server databases. This uses SQL Server SMO (Server Management Objects)

After moving my developement environment to Windows 7 64bit this application no longer worked and I got the error messages: Could not load file or assembly 'Microsoft.SqlServer.BatchParser

I found that the solution was to download and install the SQL Server Management Objects Collection for 64bit.
This is available here:
SQLServer2005_XMO_x64.msi

Google Chrome Loading Slowly

Google is my preferred browser, I found it much faster than the alternatives. Recently however it has become very slow, with most pages refusing to load the first time around and having to refresh.

IE and Firefox where OK.

I found the culprit was the latest install of Skype. It installed an add-in in Chrome which put a Skype dial button next to all phone numbers on the page. By going to Chrome\Tools\Extensions I was found the add-in and disabled it. 

Normal service was resumed and Chrome was back to it usual nippy self.

Print What You Like

Printable Versions of Web Pages

Have you ever needed to print out a web page, but over half the page is filled with adverts, banners, buttons and other stuff? Many websites have a print version, but, if they don't this 
is a very useful web site: http://www.printwhatyoulike.com/home/index2


There is also a bookmark link you can add to you button bar, which will allow you to modify any page you visit to make it more printer friendly. You can remove sections, change font sizes and more.


There is also a section of code you can add to your site to make it more printer friendly.
http://www.printwhatyoulike.com/print_button


http://www.kandysolutions.co.uk/Solutions.html

Import GMail Contacts into MS Outlook

To import Gmail contacts into Outlook.

The combination of Outlook 2010 and Gmail seems to be a good one.
Outlook 2010 does a great job of synchronising with my Gmail account. It automatically configures the settings correctly when given login credentials, mirrors the folder structure of gmail. It also gets past the restrictions on outbound mail when I am on someone else's ISP connection which had previously rendered Outlook useless.

Step by step instructions on how to import Google contacts into Outlook

1. Go to Contacts on your Gmail accout
2. Look for the more actions button (its at the footer of the contacts page for me as I write this)
3. Choose Export
4. You can choose to export all contacts or a specific group
5. Also, choose Outlook CSV as the specific type
6. Choose the location on your local system for the csv file and click Export
7. In MS Outlook, go to File\Open on the menu
8. Select import
9. Select Import from another program or file
10. Choose Comma Separated Values (Windows)
11. Browse to the CSV file you created in step 6
12. Select a destination folder - set this to Contacts
13. Click Next and then Finish to complete

Among other things I have done through my company Kandy Solutions is to create custom Outlook add-ins, and to implement other solutions using MS Offices, such as the automation of Word and Excel, creating automatic form letters based on database entries.

Setup a HP LaserJet 1100 on Windows 7 Ultimate 64bit

By default, my elderly but reliable Laserjet 1100 is not listed on the list of printers in Windows 7 Ultimate.
Doing a windows update on this list does add it, but installing using this driver did not get the printer working.

It can be downloaded from this site
http://catalog.update.microsoft.com/v7/site/Search.aspx?q=%22windows%207%20laserjet%201100

Once download - the files need to be extracted.

Then go to printer properties, driver, and when prompted to selected on, click on Have Disk, and browse to the files you have just extracted

The driver pack contains drivers for many different printers, so it is necessary to select the 1100 from the list.

After this my printer appeared to have installed ok, but still did not work. So I rebooted the PC, in line with the ancient moto, "If at first you don't succeed, reboot"

Data Cleansing

Along with my database development work, and creating software, there have been many times when I have been asked by clients to help correct their data. This can include things like identifying bad addresses, missing postcodes, sorting out the way names are stored in the system that sort of thing. It can also involve identifying and resolving duplicates. For residential addresses, finding duplicates is fairly straightforward, but for organisational data it can be much more complex.

When correcting business contact data there can be several layers and a lot of related information to deal with. For example, Organisations change name through re-branding exercises, (e.g. Barclays Bank, became Barclays) flotations etc..  Also, companies may have different forms of their names such as Kandy Solutions Ltd, and Kandy Solutions Limited. They may change addresses, and of course staff can move branch change company and change position within organisations. This is where having a database specialist can be very useful first of all in identifying all these duplications and variations, then in merging the records together.

Why does this matter? Well, how much it matters will depend on your business, but if you are mailing brochures or other mass mailings you will be wasting a lot of money mailing to the same person multiple times, posting to addresses that don't exist, so the mail never arrives.

My company, Kandy Solutions have now started to offer a full data cleaning service for customers in the UK. We can take care of everything, from checking that the database does not need additional unique indexes to prevent unnecessary repetition of data through to identifying new addresses for organisations, checking addresses are correctly formatted and match the official Royal Mail Postal Address file, identifying people who have signed up to the various opt-out schemes operating in the UK, looking up business and residential phone numbers for people on the list and many move.

Here is a full list:

Deduplication

Royal Mail's Postal Address File (PAF) Verification

Business Gone Away - find their new address

New Contact Addresses - where people has paid for their mail to be redirected, it can be possible to find their new address too, if they have chosen to let the Royal Mail make that information available

Business Telephone Lookup - we can add business telephone numbers to your data if they are missing

Residential Telephone Lookup - As long as people are not ex-directory, we can add their home phone numbers to your database

Mailing Preference Service (MPS)

Telephone Preference Service (TPS) - Vital if you telemarketing to individuals, you need to know if they are registered with the TPS, because if you call them and they complain you could face a fine.

Corporate Telephone Preference Service (CTPS) - Similarly if a business has registered a telephone number with CTPS and you cold call it, you are committing an offense. We can update you database and identify any numbers you hold that are on this list and help you avoid inadvertently calling people you shouldn't

Analysing Data - Which are your best clients, which leads are old and have never come to anything, and so can be archived rather than wasting money continuing to target them. I can answer these and many more questions by analysing your client database.

Telephone Number Validation

Data Enhancement

Name reformatting

There is much more information about the data cleansing service we offer available on our web site at http://www.kandysolutions.co.uk/Data%20Cleansing.html