You open Microsoft Outlook and get an error message saying "Outlook could not open the file containing the folders used to send and receive e-mail messages, create appointments, and open the address book. Please select from the options listed below." The options given are to create a new personal folders file or open an existing folders file.  It might look as if your mailbox, appointments and address book are lost. They're not. Do not create a new personal folders file. Do not reinstall outlook.

Your .pst file (the file containing your data) may be corrupt or you may have exceeded its default 2Gb limit.

If the file is corrupt there is an Outlook repair program included with Windows which can fix the problem so you can access your email messages, appointments and address book again. The name of the file to run it is called scanpst.exe. If it doesn't start when you type it into start and run menu option use Windows Explorer to search for it. It may reside in c:\program files\common files\system\MAPI\1033. Remember you may need to include hidden files in your search.

When you start the program (scanpst.exe) it it will look like the screen shot below. You need to browse and select your personal folders file. This file is called outlook.pst. It is likely to reside in C:\Documents and Settings\Your user folder\Local Settings\Application Data\Microsoft\Outlook

Click start to run the utility. It will run through or a selection of eight phases. These include checking file consistency, looking for lost data and checking folders and items. Once it has run through the stages a screen shot like the one below will display.

Click repair.

Click ok once the program completes. You should now  be able to open Microsoft Outlook and access your email messages, appointments and address book.

I would have to describe this as a hack rather than anything else as the convert function wouldn't work for me and date() is not supported, i.e. you can't use "SELECT date(timestamp_field) from db"; to extract only the date element; whereas you can use year(timestamp_field) or month(timestamp_field).

I needed to filter records based on the date, to extract rows from the table where the date was equal to today. The date field in the table was a datetime column. My workaround was to use the left function and extract the ten characters of the date which I could then compare to today as in the following.

$query = "SELECT title, contents FROM articles WHERE left(timestamp_field,10) = '$date'";

This worked. I don't think it's the elegant solution, alternatives welcomed.

Migrating Financial Data

This blog is also published on new IT system and is a blog on the subject of implementing new systems, in particular migrating financial data. I have seen countless examples of this being done incorrectly, in such a way that weakens the internal control environment and means the accountants have to burn the midnight oil. This is an approach that works, and which I use time and again.

When you're migrating your financial data to your new system you will want to accomplish a number of key objectives, no less of which include

1. A clean cut off
2. A clear audit trail from one system to the next
3. No loss of control over balance sheet control accounts
4. No impediments to collecting debt, i.e. transactions on statements are still understood by customers

Open and Closed Transactions

To achieve this we need to distinguish between the open and closed transactions in our data. The open transactions comprise unpaid invoices, unallocated payments and uncleared receipts and payments, typically represented by our aged debtors, aged creditors, and the uncleared items on the cashbook.

Closed items can be mapped into the new system as balances rather than taking in detailed transactions, for example, a balance on a GL account. We will treat our control accounts differently.

Importing

To retain control over our control accounts we should import the open items  as transactions rather than balances.  We should be able to extract from our legacy system the open transactions, such as a detailed aged debt in file format. We will then map these transactions, ensuring we follow the field conventions in the new tables, for example the transaction type may be known as "SI" in the new system and something else in the legacy system.  Remember to also set the VAT code for all transactions to "out of scope" as we do not want to create any VAT transactions in our import file.

The Import File

Our import file may have three transaction types; sales invoices, sales credit notes and unallocated receipts. We might have to import these as three separate files. To retain accurate ageing we might have further imports to do depending on how the period system works. By doing so the transactions in your statements will continue to be a key tool in the cash collection process. It is likely, anyway, that we will have to split the file to spread the data transfer load and speed up the import process if errors occur, so creating separate file for each period, if we have to, is not an added overhead. 

If you have debt in the last financial year, ensure you understand whether this can be imported in or whether you might have to amend the transaction date and import into the current financial year (using another field to keep the actual transaction date). If you have only a handful of transactions on a file, you might be better off to just key them in.

Check Totals

Before you begin to import anything ensure your list of transactions and the sum of your import files comes back to the debtors balance. There might be some odd transaction type you've overlooked in the mapping of your data.  When importing use full rollback, i.e. if a validation error occurs no transactions will be imported. Fix the error and import the whole file again. Make sure you do this on the test system first.

Double Entry

If we are importing open transactions as well as the balances on our TB we need to ensure we don't double count. Lets explore how we do this concentrating on debtors, creditors and the cashbook. We'll use the following abbreviated trial balance to work through the numbers.

Method

Usually at the end of you chart of accounts you will have a range of codes you can use as suspense accounts. We will use one of these to control our migration. We will use it to post our transactions and to also post the Trial Balance. If it doesn't equal zero at the end of the exercise then the migration contains an error.

Bank

As we want to retain the integrity of our bank reconciliation on our new system we need to disaggregate our TB bank balance. The balance is comprised of 

We will import or key in the uncleared payments and receipts. Our two entries will be

This leaves us with a credit balance on our Bank so we have one more journal to post. This is to take in the reconciled balance from our legacy system. If we post this balance as a bank transaction rather than a pure GL journal it means we can open up our bank reconciliation program in our new system and reconcile this single entry. Our bank account, after we post the following entry, is then clean

At this point our Trial Balance looks like this:

Debtors

Excluding the effect of provisions which we'll ignore in this example, our Debtors balance consists of all open transactions, i.e. unpaid invoices, unallocated credit notes and payments. Leading up to the go live date ensure your credit control dept. are proactively allocating cash and cleaning up the debtors. In any case you are still likely to end up with a debtors balance consisting of, for example

We will key in or import these items. Our journal entries being:

At this point our Trial Balance looks like this

Creditors

The exercise on creditors is simply the reverse of the process for Trade Debtors. There are those who argue to pay off all creditors before the go live date. This is argued on the basis of reducing the amount to import and, if there were any problems with the payment program in the new system, we would have bought ourselves some time. I'm not an advocate of this method, the main reason being the adverse effect on cash flow but also because I don't really see any advantage in doing it. The creditors should be cleaned in the same way as the debtors leading up to the go live date, and imported just the same.  Our disaggregated creditors balance is:

The journal entries will be:

Our trial balance now looks like this

The Trial Balance

So now our control accounts are complete we can journal in the rest of the balance sheet. As we have already taken in the debtors, creditors and bank balances we do not need to journal these in. Our remaining journal will be as follows.

Our Trial Balance now looks like this:

Conclusion

Our new trial balance agrees to our old trial balance. We've retained the audit trail of our file imports and journal entries and printed a hard copy of our new TB. Users on the legacy system should now only have read only access to it. Our control accounts and ageing reports agree to the legacy system, and because we've taken the bank balance in as a file import and as a bank transaction our bank reconciliation program works from day one. Many implementations treat the bank balance as though it were an ordinary GL account. Post implementation this can lead to a drain on qualified resource and a weakening of the internal control system.

Converting Data

This blog is also published on new IT system and is a blog on the subject of implementing new systems and preparing the underlying data using excel. There are many tools you can use to help in data migration but excel is useful to illustrate the types of issues you may face.

Migrating data between applications is not a clean mapping exercise, field sizes can be different, field types can differ, the format of data extracted will more than likely need to be changed before being loaded into a new system. Although there are many conversion tools we will look at a few examples of data which we're going to manipulate in excel. Even if you use another tool to clean data an understanding of the types of problems you may encounter will help you debug should a file you are importing into a new system fail to load correctly.

Date Formats

The date format could be any combination, i.e.

• YYYYMMDD
• DDMMYYYY
• MMDDYYYY
• etc...

Let's look at an example where the format of the date is DMMYYYY. The date is a field in our sales invoice file and we need to convert it into the format DD/MM/YYYY before we can import it into our new system.

The first thing we need to do to the string (a linear sequence of characters, in this case DMMYYYY) is to convert the field into DDMMYYYY, i.e. make all the fields consistent so that the 1st of January would appear 01012007 rather than 1012007 as it currently does. Move your mouse over cell B1 in the example below to reveal the formula.

In this example we use the text formula to convert the date field into a field with eight characters.

Once we have the field in a consistent format we can then more easily manipulate the contents. We could now split the string into days, months and years. The days is a simple left formula. The years are a right formula and the month is a combination of both. As usual moving your mouse over the cells below will show the formula

Here we use the LEFT and RIGHT formulas to extract the components of the date.

Finally we can use the DATE formula to convert to the format we need to import our transaction file

The DATE fomula works as DATE(year, month, day)

Although we have taken several steps to achieve our result we could have equally taken just one, though we are left with an ungainly looking formula

Changing Dates

We may need to change the dates in an export file as the transactions are too old to import into the current database. This may arise where we have some open invoices going back to previous financial years. There might be too much of an overhead associated with opening and closing financial years in the new system, in order to import just a few transactions; or the new system hasn't been set up to accept transactions beyond a certain date

To change the date we need to first see if it meets our condition, if it does we change the date. If it doesn't the date stays the same. Let's use an example where we have an invoice dated 31/12/2004. Our accounting year ends in December. Our new system is set up with one back year, i.e. 2005 is listed as year one. In order to post the transaction we would therefore need to modify the date (making sure to keep the old date, for reference, in a different field in the new database). Hover your mouse over cell B2 to see the formula.

The formula is a conditional argument. If the date is earlier that 01/01/2005 change the date to 01/01/2005, otherwise keep the date the same.

Fixed Field Lengths

When you export fixed length fields you can end up with whitespace forming part of the string. This may occur on, for instance, business partner codes from your legacy system. The field may have been defined with, for example, a fixed length of 7 characters but the code used was only 6. You will need to get rid of the whitespace to reduce the characters to six. To do this we will use the TRIM formula.

Creating a New Coding System

For one reason or another you may want to change your coding system. You may have outgrown your old system or you may just want something simpler. Lets assume you're going to change your supplier coding system from four alpha plus two numeric to one alpha plus three numeric. Given that we have our supplier database in excel how can we automatically generate the codes?

To do so we're going to use a combination of the formula we've used above. Obtaining the one alpha character is a simple exercise. We use the LEFT formula on the Supplier Name.

To automatically generate the numeric value of the code we need to determine how many of a given letter have already been used, so we can then allocate a new number. To do this will use a derivative of the IF formula. This is the COUNTIF formula. Hover your mouse over the cells in column C to see how we use the formula to obtain our result. Notice how in column c, using the COUNTIF formula we freeze the cell in the first part of the argument i.e. $B$1 but in the second part we only freeze the column, not the row, i.e. $B2

The formula in column C counts the number of records, in the dataset above, which start with the letter in the adjacent row cell. The formula in column D simply adds the two strings together using the ampersand &.

Field Lengths

The character length of fields in your legacy system might differ to the corresponding field length in the new system. If you try to import a file and the text in the field is greater than the field length in the new database, the file import will fail. Typically, this might arise on description fields, such as your product descriptions.

To identify which records we need to amend, before importing a file, we can use the LEN formula.

In cell C1 we've added to the formula to highlight only those records which meet a condition. Our condition checks if the length of the field is greater than twenty characters. If it is "Fix this" is displayed in the cell. If it isn't, nothing in displayed.

Why would anyone write a program that..........

Why would anyone write a program that would automatically restart your computer? A program so persistent that, when you click "Restart Later",  you only get a moratorium of ten minutes before the message pops up again with the same determined prompt? Every ten minutes, as though it were taking orders from the atomic clock. The message gives you five minutes to act. This is less time than it takes to go to the toilet, make a coffee and return to your desk. In which time, the relentless program has shut your computer, ignoring your unsaved work, like the spreadsheet or letter you've been painfully composing.

It doesn't even take a trip to the toilet.  You might be retrieving a file, or talking to someone or pacing the room on your mobile. Why would someone write a program that can restart you computer, and wipe your work, as you do that?

Before anyone says, "Well, you can change the settings", I already know that, and that's part of the problem. In an age of hot desking and shared computers you inherit the settings of previous users. Some, not unsurprisingly, might select the recommended setting, which will run this program.

For those that might not know how to change their setting click Start followed by Control Panel. Look for the icon below and double click.

Change the settings. My preferred setting as pictured below is to not download automatically. I like to decide what to install and run on my machine. I learned this after installing Service Pack 2, to discover afterwards my DVD drive didn't work anymore as there were no supported drivers.

On starting up your laptop you may get a worrying error message reading "hard drive not installed". The laptop doesn't boot.. This has happened to me on an IBM T21 and a T22 running windows. The reason it happens is the battery loses its charge. There is no problem with the hard drive, you will not have damaged the hard drive or lost data.

Press F1 to enter the BIOS or hit any key until you are prompted to hit F1. There are two things you need to do. The first is to press F9 to load the default configuration file.

The second thing to do is to change the date as it may have reset itself to something like 01/01/1980 - change it back to the current day. Remember the date format is likely to be in US structure "month/day/year". If you don't set it correctly some of your programs may not function properly, i.e. you may get notifications saying your licence is out of date. To fix it go back here and reset the date correctly again.

After you have made the changes press F10 to save. The laptop now powers up as normal, but you might want to search on eBay for a replacement battery as it will continue to happen whenever you plug the laptop out of a power socket..

The majority of the mod_rewrite examples you will find on the internet relate to .htaccess. I didn't want to use a .htaccess file but instead wanted to write the rules in either vhost.conf or in httpd.conf. There is a performance hit in using .htaccess on your server which is one of the reasons I didn't want to use it, also, if you don't need to use htaccess the recommendation is not to.

Although the rewrite rules in .htaccess are almost identical they didn't quite work for me in httpd.conf. I needed a series of rewrite rules to handle dynamic page content, namely to make them search engine friendly, such as turning page.php?id=2 into page-2.html

Mod_Rewrite can be complex, particularly in the use of regular expressions (regex). A regular expression is a string used to describe a pattern. They can be used for server side validation of user submitted input or for rewrite rules in Apache. If you haven't used regular expressions before their first sight might instil the same wonder as a wall filled with strange, alien hieroglyphics. Where do you even start to translate your dynamic URL into this odd language? Fortunately there is a key to the characters to help decipher a regex string or to translate your URL into a regex pattern..

When some of these characters are used in combination with each other their meaning may change

Some common Examples

Shorthand characters can also be used in pattern matching. You might be familiar with some of these from your PHP scripts. The majority of these will not be used in Mod_Rewrite I only include them for completeness and so as to refer back to them later.

The ^ and the $ are known as anchors. Anchors match a position before, after or between characters.

When you start to look at examples of using regex the terminology, metacharacters and their meanings becomes a lot easier to understand. Let's look at some simple examples first before applying what we know to Mod_Rewrite.

In testing our examples we will use PHP's function preg_match. Here we will define two variables $pattern (the pattern to test) and $match (the string we apply the pattern matching to). We pass both variables to the PHP function. The function will return 0 if there is no match and 1 if there is a match.

This will match one aphabetic character. It will fail if there is more than one character such as "sa". It will fail if the string contains a non alphabetic character. It will fail if the letter is in upper case.

This will match for a single uppercase or lowercase letter. Any other characters will fail

Using the curly braces we can define how many characters in the match. In this example any three letters will match, but the match will fail if it is only two letters or more than three.

Bu using "+" instead of the curly braces we say the match can occur one to infinite times. i.e this will match for any word or string comprised of letters.

Remember the caret ^ negates a class. In the following example only characters which are not letters will match. This includes symbols like the comma etc.

The following will match any number of aphanumeric characters

Our pattern might have different elements we want to match. Lets add extra classes. In this example Lotus and Lotus123 will match. We've made the 123 optional (note how we have gpouped it with () brackets

In the following example we introduce another character s to denote a space. As we have added ? to it, i.e. s? we are saying it is optional. This pattern would match Lotus, Lotus123 and Lotus 123

Of course if we wanted to match the word and only the word Lotus completely we would use the following. This will only match Lotus, Lotus123, Lotus 123, lotus, lotus123 and lotus 123. But of course this would also match Lotus 345

In these examples the [0-9] could equally have been written as [d] indicating a digit.

These are the basics but working through them should enable us to read and understand regular expressions, we can understand the quantifers(*, ?, +) and the anchors (^, $, \b, \w) and the other metacharacters used in regex to pattern match.

Lets now apply what we have learned to examples using Apaches mod_rewrite. At first we will just examine the pattern matching, we will then apply it to the rewrite syntax. Don't practice on your live server as, if you're unfamiliar with mod_rewrite and regex, your rules might render unexpected results.

The basic syntax for Apache mod_rewrite in httpd.conf is

RewriteEngine on
RewriteRule ^PatternToMatch$ WhatToDo

For example the rule below will match the web page ella.html and rewrite it to mark.html

RewriteEngine on
RewriteRule ^ella.html$ mark.html

i.e. the the URL will say ella.html but the content served up will be mark.html

The first part (^PatternToMatch$) is what a user will type in as a URL or click on to follow a link. For search engine indexing it is better if this link is a static page rather than a dynamic one. The mod_rewrite is a cloaking device. Our "bird of prey" php pages can disappear and appear as static html.

When the user clicks the link for a static html page mod_write will apply the matching rules we've given it and display the content from our dynamic php page.

Lets suppose we have a blog. The actual URL of a post might be blog.php?id=122. We might prefer a user to link to it as follows blog.php/2006/12/02/here-it-is.html

So in the URL we are looking for a specific match. Lets build it up

The more you define the greater the load on the server.

Anything between ( ) brackets in our pattern we can use as variables in our match. IN our example we have four which will be known as  $1, $2, $3 and $4. We can pass these to our match as these will be the variables needed for our PHP script to run without giving a 404 error. Our PHP script will look like this

blog.php?date=$1-$2-$3&name=$4

ReWriteBase /archive/

Command Flags

When you change a URL to directory level remember you URL's for css, javascript, images etc. need to use absolute rather than relative path or they won't be found.