Subscribe to this feed

Navigation

Recent Posts

Archive

Macro to insert footer

Tuesday 03 Nov, 2009 - 23:46pm | 0 comments |

The following macro will insert a footer giving the page numbers, file loaction and name, and the date and time on the active sheet.

Sub myfoot()
'Dim fullpath As String
'Dim sLeft As String
'Dim sRight As String

fullpath = ActiveWorkbook.FullName
Const sPAGE As String = "&P"
Const sPAGES As String = "&N"
Const sDATE As String = "&D"
Const sTIME As String = "&T"

sLeft = "Page " & sPAGE & " of " & sPAGES
sRight = sDATE & " " & sTIME

ActiveSheet.PageSetup.CenterFooter = fullpath
ActiveSheet.PageSetup.LeftFooter = sLeft
ActiveSheet.PageSetup.RightFooter = sRight
End Sub

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Excel |Macro |VBA

$this PHP Variable

Sunday 10 Aug, 2008 - 21:24pm | 0 comments |

In PHP $this is a special variable and can't be assigned.

An hour later wondering why my $this and $that tests weren't working!

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:PHP |Variables |Debug |testing

php5 extensions directory and mysql undefined function

Monday 21 Jan, 2008 - 17:58pm | 0 comments |

Call to undefined function mysql_connect() or have a php.ini file which doesn't seem to behave, i.e. the extension_dir seems to have a mind of its own so the MySQL extensions aren't loading? 

I was having this problem on a new install of Apache 2.2.8, PHP 5.2.5, and MySQL 5.1.22 on Windows Vista Home Premium. 

My first mistake downloading only the PHP installer. This does not include the extensions. Download the zip package and copy the extensions folder from it into your PHP directory. Edit php.ini, go to the dynamic extensions section and add the following

extension = php_mysqli.dll;
extension = php_mysql.dll;

Make sure the extensions directory is set to the correct path. Reboot Apache...

I was still getting the same error - call to undefined function. I spent a few hours on google looking for the answer. This included copying php.ini, php_mysql.dll. php_mysqli.dll and libmysql.dll into and out of various directories including c:\windows and c:\windows\system 32. None of which worked. 

Back to the drawing board. phpinfo records the extensions path as being c:\php5. There's the answer. There is no such folder and this isn't where extension_dir is pointed in php.ini? Why would it be picking up this directory?

As a shortcut I create the folder and copy the extensions into it. It still doesn't work. I edit php.ini and change the slashes on the extensions directory and try various symantics

  • c:\program files\php\ext
  • c:/program files/php/ext
  • c:/program files/php/ext/

Reboot Apache each time but it still doesn't work and the extension directory in phpinfo is still pointing at the spectral c:\php5. Where is it picking this up?  I find a few forums suggesting it's a permissions problem. I disable all security settings, turn off User Account Control and open all access to php.ini. It doesn't make a difference, I still get the same message. 

Back to basics. I open the Apache configuration file. It turns out the solution is a lot simpler. PHPIniDir in httpd.conf is incorrect. It's pointing to a folder which doesn't exist so windows has been using defaults to locate the files. Change the folder name to the correct path "C:\Program Files\PHP\" including the closing slash. Restart Apache. Eureka.

I'm not the first person to have this problem. I won't be the last, if you stumble on this post while you're having problems with an install concentrate on phpinfo,  particularly the Loaded Configuration File and the extension_dir, and yes double check the settings on your web server.

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:PHP |Apache |MySQL |Vista |MySQL undefined function

Windows Vista biggest disappointment of 2007

Monday 17 Dec, 2007 - 19:58pm | 0 comments |

If you're buying retail and go into any of the high street stores such as PC World, Comet, Currys, John Lewis, Dixons, Tesco all laptops on sale come pre-installed with Windows Vista. If you go online to Compaq, Toshiba, Sony, HP, Acer and the rest, you have no choice but to buy Windows Vista. In fact, all websites display the same message "Toshiba recommends Windows Vista", "HP recommends Windows Vista", "VAIO recommends Windows Vista", "Acer recommends Windows Vista" on and on ad nauseam.

With the consistent wording, these recommendations clearly originate from Microsoft rather than clinical engineering tests. Microsoft are heavily incentivising manufacturers to push Vista which has unbelievably been in development since 2001, consuming Microsoft people and money. Despite this, Vista, delivered three years late, doesn't perform any better than XP and needs some serious hardware just to run the graphical "Aero" interface such as 1GB of system memory and a 40GB hard drive capacity.  

Business customers running Vista Business were thrown a life buoy, being quietly allowed to "downgrade" to XP. Retail customers, however, don't have the same licensing choice. If you have it, you're stuck with it. The main change in Vista is the unnecessary user interface and an improved search function as it tries to catch up with Google. Oh, and My Computer has been renamed Computer.

With Vista OS I am reminded of the Apple II being replaced with the Apple III in the early 1980s. The Apple III was designed by Marketeers and was the beginning of the end of Apples leading market position until it started to find itself again with the iPod. Vista has the look and feel of a development being led by Marketeers, it's not an operating system of choice.

So what is the alternative? As you can't seem to buy a laptop with XP you can return to Apple and the MAC OS (once it's not the "leopard" 10.5) or you can build your own with a Linux distribution such as Ubuntu, i.e. buy a Vista Laptop and uninstall the Vista OS. For the moment, my choice is not to buy.

Is it any wonder Vista is ranked number one by PC World as the biggest tech disappointment of 2007?

  1. Windows Vista
  2. The High Definition format War
  3. Facebook Beacon
  4. Yahoo
  5. Apple iPhone
  6. The Broadband Industry
  7. Voice Over IP
  8. Apple "Leopard" OS 10.5
  9. Microsoft Office 2007
  10. Wireless Carriers
  11. Microsoft Zune
  12. Internet Security
  13. Social Networks
  14. Municipal WiMax
  15. Amazon Unbox

 

I've also posted this on really annoying shit dot com

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Windows Vista |MAC OS |Linux

Excel giving incorrect results

Tuesday 06 Nov, 2007 - 00:19am | 0 comments |

There are occasions when Excel might not add up properly, even though you still expect it to do so.  Lets look at a very simple example to illustrate the case. You can reproduce this yourself as you read.

I have a small table of data, four columns wide. There are two row entries.

Simple table of data

I want a summary of  the data in the table. Specifically I am after a sum of column C where the code in column A is A, and I want the sum of column D where the code in column B is B. In both cases this should give me a result of 200 or -200.

The respective formulae to summarise by A code are =SUMIF(A:C,F2,C:C) and =SUMIF(B:D,F2,D:D). The respective formulae to summarise by B code are =SUMIF(A:C,F3,C:C) and =SUMIF(B:D,F3,D:D).

The results given....

SUMIF doesn't return the correct result

As you can see, Excel incorrectly returns a value of -200 against the B code in the summary of column C. Did you get the same result? This is a very simplistic example to  illustrate the need to validate any report or analysis performed with Excel before publishing.

Of course, how you organise your data is also relevant. If the table were instead organised as follows

The data reorganised

the correct result is returned.

The correct result

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Excel |SUMIF |validate results |errors

SnagIt 8.2.0 workaround

Sunday 21 Oct, 2007 - 21:31pm | 2 comments |

In Snagit 8.2.0 there does not seem to be any way to capture a tools type drop down menu. When you get the menu ready and click back to Snagit, Snagit becomes the active window. Of course, this means the menu on the screen you want to capture is automatically closed and you can't get an open image of it.

Oracle Help Menu

In order to do so you have to add in an extra step. Get your menu ready, press the PrtSc button. Open Microsoft Paint or similar and paste the result, using Ctrl V or the edit menu. If you use a photo package your screen shot quality won't degrade as it will if you copy directly into Word.

At this point you can click the capture button in Snagit and select the menu region as in the example above.

Snagit is a very nifty screen capture program from TechSmith, very good for creating documents quickly, or for editing, annotating or sharing your screen shots. It's priced at $39.95 for a single user licence.

SnagIt 8.2.0

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:SnagIt |Screen Capture |Techsmith

Find Matching Text in Excel

Saturday 29 Sep, 2007 - 14:58pm | 0 comments |

The search function in Excel allows you to search for a text string occurrence within another text string.

Use to identify all rows where, for example, a description field, contains a certain word. Perhaps you are looking for every row which contains the word "VAT".

=SEARCH("VAT",A1,1)

where column A contains the description field.

Find Matching Text
A B C D E
1 Novation of lease 3 FALSE Matching String
2 Some other text #VALUE TRUE
3

If the string is not found the #VALUE error is returned. If the string is found you are given the starting position it occurs. In the above example, the word "VAT" starts at the third character.

To tidy up the results you can use the ISERR function to determine whether or not the error will occur. i.e. FALSE means the text is found, TRUE means it isn't.

=ISERR(SEARCH("VAT",A1,1))

Then use the IF function for a final tidy up. If the ISERR function is TRUE don't return anything, otherwise display "Matching String" in the cell.

=IF(ISERR(SEARCH("VAT",A1,1))=TRUE,"","Matching String")

Use the SEARCH function with caution. In this example I was searching for the word VAT. This letter sequence is contained in other words, such as Novation, which may not be quite what you were you looking for in your results. If you are running the function over a large dataset you might not pick up this logical error.

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:SEARCH |ISERR |IF |Excel |Logic Error

Excel macro to copy to Custom Template

Saturday 18 Aug, 2007 - 09:50am | 0 comments |

The following macro copies an Excel sheet to a custom template. All you need do is set the template path to your own or a network drive. This is really only a snippet to demonstrate the concept but is a complete macro nonetheless. I've used it within a more complex process to generate invoices from a data file, then copy each invoice to a separate file to email out as an attachment. This was a temporary solution during the lead up to a billing system change for a Telecoms client. 

I've used this to get around not being able to copy headers and footers in Excel and to avoid copying common drawing objects such as a logo and text boxes. By having these set in the template I didn't need to copy them each time the process looped.

Sub copy_ActiveSheet()
Dim wb As Workbook
Dim source As Range
Dim dest As Workbook

'Enter the path to your template file
Const TemplatePath As String = "C:mark.xlt"

Set source = Nothing
On Error Resume Next
Set source = Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

Application.ScreenUpdating = False
Set dest = Workbooks.Add(TemplatePath)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Excel |Macro |Drawing objects |headers |footers

UK Post Code GeoCoding

Wednesday 13 Jun, 2007 - 12:01pm | 0 comments |

The PAF (Postcode Address File) is the complete address database of the UK containing over 27 million addresses and some 1.7 million post codes. Postzon is the Royal Mails product which links post codes with ordinance survey and grid reference data. These products are licensed by the Royal Mail and are not free which can make access to the data prohibitive for some users. 

As geocode data is freely available in countries such as Canada, Germany, Italy, Spain, France and the USA various hacks have been devised for the UK, including community compiled post code databases, and a petition, which can only be doomed to fail, to try and force the Royal Mail to release their data for free.

The Royal Mail is in the process of revising it's pricing structure, recognising it has in the past favoured larger organisations. The price changes are being phased in and will culminate in all licences being replaced with new terms and conditions on 1st September, 2009. Although these changes are welcome it still means the data isn't free and websites which are non commercial or have low levels of users may still need to look elsewhere if they wish to include mapping information in their web applications.

UK Post Codes are made up of two parts as in SW1A 2AA, the post code for Number 10 Downing Street. The first part is the outward code identifying the delivery office the mail has to go to, and the second part is the inward code which, when combined with the building name or number, gives the delivery point.

Part Meaning Number
SW The post code area 124 in the UK
SW1A The post code district Approximately 20 districts per area
SW1A 2 The post code sector Approximately 3,000 addresses per sector
SW1A 2AA The post code unit Approximately 15 addresses per unit.

The district codes (approximately 2,800) with latitude and longitude coordinates can be downloaded from Jibble, but if you want more precision in your maps you need to look at the full post code unit. 

The definitive tutorial on geocoding UK postcodes with Google Map API was written by Tom Anthony on his blog earlier in the year. He has since followed it up with Caching Google Maps Geocoder Results, another easy to follow well written tutorial on how to, at least temporarily, save your results. For a complete post code unit, however, there are on average 15 addresses so basing a marker on the post code unit will not be precisely accurate, i.e. it will not identify a specific address. To do that you need to include the building name or number.

Tom's method is based on form imput of the post code, but if you already have the post code in a database you can just call the javascript function directly, as in

<script type="text/javascript">
usePointFromPostcode('<?php echo $postcode; ?>', placeMarkerAtPoint);
</script>

The result, however, is a proximity map rather than identifying a specific address. Remember there are approximately 27 million addresses in the UK and only 1.7 million post codes.

Post Code data is not static and for licensed copies of the Royal Mail data there are regular updates. If you pass lat and long coordinates back into a database to avoid hitting the Google or Multimap servers to obtain the coordinates there is a risk, probably quite remote, of your data being out of date. You could mitigate this by adding a date field to your database table so you could check the lat long coordinates at a frequency, say every six months, or a year.

The API keys for Google Maps and Google Search can be optained from

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Geocoding |Google Maps |Google Search |Hack |UK Post Codes

Accept User Input in SQL*Plus

Saturday 05 May, 2007 - 17:11pm | 0 comments |

You can allow users to enter parameters in SQLPlus queries by using accept and prompt. An example of the syntax is

accept startdate prompt "Enter Start Date: "

startdate is the parameter value and the where condition would then include the parameter as follows

WHERE date >= '&startdate'

The example below I saved for use in a batch file. A user could then click the batch file which would open SQLPlus and prompt the user for the query parameters including a "Save File As" prompt, as the output was generating a csv file. The batch file icon can be changed in Windows to something other than the default, something more meaningful for the user.

The appearance might be very retro, but its a simple tool of the "cheap and cheerful" variety and the user is, after all, more interested in the output file.

The Retro SQL*Plus Screen

accept filesave prompt "Save File As: "
accept code prompt "Enter Carrier Code: "
accept startdate prompt "Enter Start Date: "
accept enddate prompt "Enter End Date: "
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET WRAP OFF
SET LINESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SPOOL C:\reports\'&filesave'.csv;
SELECT /* The purpose of this query is to ...... Always good policy to annotate queries */
carrier_id, carrier_name, calls, minutes, turnover FROM
carriers_data
WHERE
trunc(adj_start_time) >= '&startdate'
AND trunc(adj_start_time) < '&enddate'
AND carrier_id = '&code'

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:SQL*Plus |Oracle |CSV

Exit SQLPlus from Batch File

Saturday 05 May, 2007 - 12:57pm | 0 comments |

If you have a batch file set to run multiple SQLPlus queries and to then perform some other action such as merge the results together into a single file, or copy the files elsewhere you need to physically exit SQLPlus or the batch file will not continue to run and execute the commands.

To exit SQLPlus automatically skip a line after your last query and type exit as in

@C:\Queries\Query_1.sql
@C:\Queries\Query_2.sql
@C:\Queries\Query_3.sql
@C:\Queries\Query_4.sql

EXIT
Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:SQLPlus |Oracle |DOS |bat |batch file

Run Multiple SQLPlus Queries via DOS batch file

Saturday 05 May, 2007 - 12:51pm | 5 comments |

To run multiple SQLPlus queries as part of a DOS batch file place all the queries in a separate SQL file. For Example

@C:\Queries\Query_1.sql
@C:\Queries\Query_2.sql
@C:\Queries\Query_3.sql
@C:\Queries\Query_4.sql

Save the file as runall.sql. Then use this file in your batch file

@Echo Off
sqlplus [login details] @C:\queries\runall.sql
EXIT
Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:SQLPlus |bat |DOS |Oracle

CSS Browser Differences IE and Firefox

Friday 04 May, 2007 - 13:38pm | 0 comments |

You can design the most beautiful CSS compliant web, all validated to W3C standards, only to find its almost completely unreadable or unusable in another browser. Font sizes are microscopic, menus don't appear as they may now be hidden under a layer, and the layout is all wrong. Worse, it just doesn't look as if its been professionally designed, and if you have already published it, all you can do is cringe before rolling up your sleeves and correcting it.

Browsers have different defaults. Internet Explorer seems to have more than others, so if you design and test using only IE your pages may not display properly in other browsers. As a rule you should test your site with other browsers before publishing it.

There is hack you can use for Internet Explorer to ensure it reads the right CSS and your site is cross browser compliant. This hack is achieved by simply adding !important to the css you want Internet Explorer to ignore. The following CSS creates similar font size across browsers. IE ignores the first line, whereas Firefox applies the first line.


body
{
font-size: medium !important;
font-size: x-small;
}

As another rule you should try to not mix your stylesheet with inline CSS, i.e. if you define an element in your stylesheet then add some inline CSS to it, the stylesheet CSS may be completely ignored and you may end up with a result you don't want..

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:CSS |W3C |IE |Firefox |Browsers

Create csv import file from Excel

Tuesday 01 May, 2007 - 12:06pm | 0 comments |

In the macro below I create a csv file from the worksheet named accrual. As the sheet is the data returned from an MS Query I first refresh the query before creating the csv file. The Query refreshes from cell A2 as row one contains the header row, the field identifiers required to import into a Finance System.

To use it copy the VBA into the worksheet you wish to run it on and change the details as necessary, i.e. sheet name, whether you wish to refresh the query or not, save as file name etc.. 

I actually use this twice in the workbook, once to create a csv file of invoices to import into the Sales Ledger, and the second time to create a reversing accrual to import into the Nominal Ledger, to accrue the revenue, as the Invoices are dated first day of the month so miss quarter end tax point for VAT. The accrual posts the revenue into prior month and reverses it out again on the first day of the next month.

Sub Accrual()
Dim wb As Workbook
Dim source As Range
Dim dest As Workbook
Sheets("accrual").Activate

'If sheet is the result of a query refresh the data
'started on A2 as row one contains the field identifiers
'Comment out if you do not need to refresh a query
ActiveSheet.Range("A2").Select
Selection.QueryTable.refresh BackgroundQuery:=False

Set source = Nothing
On Error Resume Next
Set source = Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "No Data", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

'Change Filename to what you require
dest.SaveAs Filename:="c:accrual.csv", _
FileFormat:=xlCSV, CreateBackup:=False
dest.Close SaveChanges:=False

'Return to main application page
Application.ScreenUpdating = True
Sheets("Form").Activate
End Sub

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:csv |Import file |Sage MMS |VBA

Use Query Result as Variable in Batch File

Tuesday 01 May, 2007 - 10:47am | 0 comments |

You can use the result of a SQLPlus query as a variable in a windows batch file by spooling the output to a .bat file

SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET WRAP OFF
SET LINESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SPOOL c:cdr_count.bat;
select
'@echo off' from dual
UNION ALL
select 
'SET CDR='||''||count(ddi) from cdrs where trunc(sysdate) = trunc(adj_start_time);
SPOOL OFF;

This creates or replaces a batch file and outputs

@echo off
SET CDR=643950

I am then able to call that batch file from within another batch file and use the SQL query result as it is defined as a variable.

call c:cdr_count.bat
IF %CDR% EQU 0 GOTO END

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:SQLPLus |Oracle |DOS |Batch files

Excel Datatype Error with MS Query

Sunday 15 Apr, 2007 - 11:34am | 0 comments |

If you are importing data into Excel or using an excel datasheet as a data source in, for example, an MS Query you can observe unexpected results as Excel tries to be cleverer than it is.

Only the first eight rows of your data are scanned to determine the datatype for the column, i.e. is it a number or is it text. If you have a data column which uses both, but, say only text appears in the first eight rows, the number values will not be displayed. You will be left with blanks instead of the values.

The workaround is that you have to explicitly define each of your data columns before you import your data. Do this before you import your data to avoid having to F2 through the fields (another datatype weakness in Excel !)

This is partly explained in Microsoft article KB141284 or Q141284

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Excel |MS Query |DataType |KB141284

Merge Multiple CSV Files

Sunday 01 Apr, 2007 - 14:48pm | 55 comments |

You can merge multiple csv or text files with a simple DOS command. Copy the code below into a text editor and save the file with a .bat extension. Save the file in the same folder as the csv files.

copy *.csv importfile.csv

I've used this as part of an import routine. Step one produces a set of csv files from SQL queries on an Oracle database. Step two merges all the csv files together. Step three imports a single file into Sage MMS

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:csv |File Import |batch files |bat

Oracle SQL*Plus Last Full Month

Saturday 24 Mar, 2007 - 18:15pm | 1 comments |

Although Crystal Reports has a built in LastFullMonth function SQL*Plus doesn't.

If you want to use SQL*Plus for your report and want the date parameter to be dynamic you can do so by manipulating sysdate and using some of the built in date functions.

Here is how

select * from db_table
where date_col between add_months((last_day(sysdate))+1,-2) AND add_months((last_day(sysdate))+1,-1)
Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Oracle |SQL*Plus |Dates |Crystal Reports

SQL*Plus Spool File

Saturday 24 Mar, 2007 - 12:40pm | 1 comments |

If you want to produce a csv file from a SQL select query in SQL*Plus use the following syntax.

set echo off
set heading off
set feedback off
set pagesize 0
spool c:\filename.csv
select col1||','||col2 from db_table;
spool off

This will produce a csv formatted file. Straight to excel, change csv to xls but bear in mind two potential issues (a) row limit in Excel, and (b) leading zeros in number fields will be lost in excel.

The leading zeros can be resolved if you instead spool the file as a txt as you will be forced to use excels conversion wizard to open the file.

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Oracle |SQL |SQL*Plus |csv

Batch File to Append Date to file name

Saturday 24 Feb, 2007 - 21:49pm | 36 comments |

A DOS batch file (file with extension .bat) can be a very effective way to automate a series of file operations. You can schedule the batch file to run at a specific time, and automate, say, a series of operations such as copying, moving, deleting or renaming files. You can create a batch file in any text editor like notepad and just save the file with an extension of .bat. In windows, if you open a DOS prompt, type edit and hit return a retro text editor will open which you can also use.

The batch file code below will copy all microsoft excel files (.xls) from the current folder into a folder called "import" and append the current date in yyyymmdd format to the filename. Additionally the file extension is changed from .xls to csv (I am not sure how readable the csv file will be to other programs so let me know the result you get if you use it). 

@Echo Off
@For /F "tokens=1,2,3 delims=/ " %%A in ('Date /t') do @( 
Set Day=%%A
Set Month=%%B
Set Year=%%C
Set All=%%C%%B%%A
)
@For %%a in ("*.xls") do copy %%a "import\%%~na_%All%.csv"

In this example the batch file needs to be saved in the same directory as the Excel files.

There are many applications in using it. For example, sales people ftp their expense reports to a folder, these are swept up at the end of a day, the date appended to the filename, they are imported into a DB, the files are then moved to an archive folder.

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:MSDOS |.bat |batch files |excel |csv

Dynamic XML in PHP Application

Sunday 18 Feb, 2007 - 14:59pm | 0 comments |

To generate dynamic XML in your PHP application add the following line to your Apache httpd.conf file

AddType application/x-httpd-php .xml

XML files will then be parsed as if they are PHP. You can add your PHP scripts to the file to generate dynamic content and then use mod rewrite to turn the URL into a friendly one, i.e. turn something like  dynamic.xml?company=eriginal into eriginal.xml

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:XML |PHP |Apache |Web database

The Wireless Phone Jack

Sunday 18 Feb, 2007 - 11:15am | 0 comments |

With a wireless phone jack you can add an extra phone line outlet anywhere in your home without any rewiring. All you need is an electrical socket. The jack can be used with any device using a phone line including a modem, set top box, phone and fax machine. It's also fully expandable meaning you can add as many additional units as you need.

I use a phone jack for a fax machine in my home office and can't recommend the product highly enough. It's simple plug and play set up and retails at around £50..

Wireless Phone Jack
Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Wireless |Wireless Jack |Products |Home Networking

Chained Select in Microsoft Access

Saturday 17 Feb, 2007 - 18:14pm | 0 comments |

When it comes to systems and applications less is more. If you find your business increasingly relying on a satellite system of spreadsheets and access databases then it really is time to review your strategy and your core systems. On a current project I've begun to feel like Copernicus gazing into the sky, except instead of stars I'm gazing out at a huge constellation of spreadsheets. In fact. there are so many, that the core system, Oracle,  is almost completely obscured from view.

Excel is great for data analysis, but it isn't the best for collecting data. Microsoft Access, on the other hand,  is a step up the ladder. You can design a better user interface and exert more control over the data entry process and consequently on the output in your reports. Although outside the scope of my remit I decided to help one group of users by replacing their shared spreadsheet with an access database - doing the work on my own time, and emphasising it was only a temporary solution.

Access, being a relational database, means your data entry screens may pull data from several tables and may only add the data to one. In building a screen that does this you might like some of your form fields to depend on the result selected in another field. This type of chained select process is very common on web forms using Javascript but it is as equally achievable in Access.

Lets have a look at a very simple database which we will call "customer". We will set up three tables.

Table 1 is called check and is used to list the types of check that will be performed on a customer.

Table 1: check
ID check_type
1 Credit
2 Technical
(AutoNumber)  

Table 2 is related to table 1. It is a list of the possible outcomes of the check applied. Therefore it includes a foreign key (fkey) which is the index from table 1. In the example below we can see that the first three rows all relate to the credit check in table 1.

Table 2: check_result
ID check_result fkey_check_type
1 Accept 1
2 Proceed with Caution 1
3 Reject 1
4 Local Broadband issue 2
(AutoNumber)    

Table 3 will be the main table use to input data from a form. To keep things simple for our example this table includes the customer name as a free text field. In practice this would actually be a key to a separate customer table.

Table 3: customer_update
ID customer_name fkey_check_result date_time
1 Eriginal  1 17/02/2007 15:09:30
(AutoNumber)      

You can set these tables up in Access by using the Create table by entering data menu option. The three table are related to each other as follows.

table relationships

We are now ready to create the user form, which we will name update, to input the data. Using the Create Form in Design view menu option create a form with four fields. The fourth field will contain the date time stamp which we will hide from view.

  1. Customer name. Text box
  2. CheckType. List box
  3. CheckResult. List box
  4. DateTime. Text box

To create the chained select between the two list boxes we need to do the following. Still in design view, view the properties of the CheckType list box. In the data tab we will input a SQL query in the row source as follows

SELECT [check].[ID], [check].[check_type] FROM [check]; 

Next in the event tab we need to add an event procedure to the After Update field. Click the first button at the end of the line and select [Event Procedure]. Then click the second button which will take you to a VB (visual basic) screen. Add the following code and save.

Private Sub CheckType_AfterUpdate()
Dim strSQL As String

Me!CheckResult.Requery
End Sub

Basically here we are saying after you make a selection in the check type field, i.e. select whether the check type was a credit or a technical check, then rerun the SQL query in the check result field.

In the properties of the check result field add the following SQL query

SELECT [check_result].[ID], [check_result].[check_result] FROM check_result WHERE ((([check_result].[fkey_check_type])=[Forms]![update]![CheckType]));

In summary, the chained select is obtained by using the selection in the first field as a parameter in the SQL query in the second field, and by re-querying the SQL in the second field when a selection is made in the first.

Chained select in Microsoft Access
Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Microsoft Access |Access |Chained Select |Excel |Systems

Merge Data from Multiple Spreadsheets

Sunday 11 Feb, 2007 - 12:59pm | 0 comments |

I was looking for a quick way to merge the data from multiple workbooks into one worksheet in a new excel workbook.

The process of manually going through 40+ files and manually extracting the data wasn't going to be an effective use of time.

Although I used code before to do it I couldn't lay my hands on it, which hard drive, which file etc. so I had to start again but I needed a fast solution so fired up google.

One of the first links I clicked was for DigDB, an excel add-in which actually could do the job and a lot more besides but I didn't want to pay the licence. I bookmarked the site to return to later.

The solution I found was on Ron de Bruin's Excel Tips page which contains a lot of VBA code examples which you can use for free. The code there solved the problem exactly. It copies all the ranges you specify in the source workbooks and copies them into one master worksheet.

The precise code is reproduced below with some minor additional comments. To use it open an Excel workbook. Click on tools, Macro and Visual Basic Editor. In the VB editor select View and Code. Copy the code below into that page and click save. Save the file as basebook.xls on your C drive.

In the VB editor change the ranges you want to copy as required.

Sub Example1()
Dim basebook As
Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:source_data"
ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet
rnum = 1

Do While FNames ""
Set mybook = Workbooks.Open(FNames)
Set sourceRange = mybook.Worksheets(1).Range("A2:E2")

'If the worksheets are named use mybook.Worksheet("NameOfWorksheet")
'Set the range as required

SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "B")
' Change the column letter above depending on where you want the data to start on the destination sheet
' Change the column letter in in this section - Cells(rnum, "ColumnLetter")

' basebook.Worksheets(1).Cells(rnum, "F").Value = mybook.Name
' Uncomment the line above if you want to add the workbook name into a column on the destination worksheet
' Change the column letter in in this section - Cells(rnum, "ColumnLetter")

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only the values

' With sourceRange
' Set destrange = basebook.Worksheets(1).Cells(rnum, "A"). _
' Resize(.Rows.Count, .Columns.Count)

' End With
' destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

To use it the source files need to saved into a folder named source_data on the C drive, i.e. C:/source_data

From within basebook.xls select tools and macro. Select the macro to run.

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Excel |Macro |VBA |Spreadsheeets

Project Plans

Saturday 10 Feb, 2007 - 15:53pm | 0 comments |

I once agreed a contract with a Business Angel to work on two of his companies. One of them was loss making and the other was evidently booming but it's processes and internal controls were weak.

There was a story about the latter. Eleven months into its financial year their accounting system developed a bug. The program of action the business planned and followed was to replace the system and recruit a number of  temps to re-input the eleven months of data and attempt to get everything up to date. Ten months later when I joined it still wasn't up to date, the prior year hadn't been closed, the business was running without management information (MI) and had no cash (never a good sign).

The system which developed the bug was Sage Line 50. The audit trail had apparently been wiped. This had been the conclusion of the acting Accountant who devised the plan to replace it. 

I have a natural curiosity. An off the shelf package is very unlikely to develop a bug so severe as to merit a complete system change. In addition the support team at Sage will ordinarily fix a problem if one arises.

Day one I switched on Sage to verify my hunch and sure enough when I clicked into the Audit Trail it was blank, but it was only blank because the search filter was on. When I took the filter off all the transactions were there.  When I ran the verify data routine there were no errors. There was nothing wrong with the system.

The company had actually lost over £200K during this blind period although it believed it was making money.

I use this example frequently to emphasise the importance of.

  1. Having a written project plan with gateway reviews, a Prince2 like methodology, that prevents plans being ill conceived and carrying on indefinitely without giving any results.
  2. Getting a second opinion if the solution seems too drastic. Hiring me for a half day when the problem was identified would have saved a substantial sum of money and over ten months of efforts.
  3. Not doing things in a panic. Take time to use common sense, in this example, the un-likeliness of Sage Line 50 being unfixable.
Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Project plans |Prince2 |Sage Line 50

Microsoft Outlook Corruption

Tuesday 16 Jan, 2007 - 22:12pm | 1 comments |

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

select your outlook.pst file and click start

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 repair.

Repair Complete click ok

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

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:microsoft |outlook |scanpst

Using datetime stamp in MySQL query

Tuesday 16 Jan, 2007 - 18:59pm | 0 comments |

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.

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:SQL |MySQL |timestamp |date function

Migrating Financial Data to New System

Tuesday 16 Jan, 2007 - 17:22pm | 0 comments |

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.

xyz Ltd
Trial Balance 31/08/06
Dr £'000 Cr £'000
Fixed Assets 150
Trade Debtors 500
Other Debtors 40
Bank 75
Trade Creditors 420
Other Creditors 140
Reserves 205
Totals 765 765

 

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 

xyz Ltd
Cash Book 31/08/06
Dr £'000 Cr £'000
Bank Statement 95
Uncleared Payments 28
Uncleared Receipts 8
Sub-total 103 28
TB Bank Balance 75

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

xyz Ltd
Migration Journal #1 
Dr £'000 Cr £'000
Bank 8
Migration Suspense 8
being uncleared receipts
Migration Suspense 28
Bank 28
being uncleared payments
Totals 36 36

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

xyz Ltd
Migration Journal #2 
Dr £'000 Cr £'000
Bank 95
Migration Suspense 95
being reconciled bank balance
Totals 95 95

At this point our Trial Balance looks like this:

xyz Ltd
Trial Balance 31/08/06 [new system]
Dr £'000 Cr £'000
Fixed Assets
Trade Debtors
Other Debtors
Bank 75
Trade Creditors
Other Creditors
Reserves
Migration Suspense 75
Totals 75 75

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

xyz Ltd
Debtors 31/08/06
Dr £'000 Cr £'000
Open Invoices 515
Unallocated Credits 3
Unallocated Receipts 17
Part Paid Items etc. 5
Sub-total 520

20

TB Debtors Balance 500

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

xyz Ltd
Migration Journal #3 
Dr £'000 Cr £'000
Trade Debtors 515
Migration Suspense 515
being unpaid debtor invoices
Migration Suspense 3
Trade Debtors 3
being unallocated credit notes
Migration Suspense 17
Trade Debtors 17
being unallocated receipts
Trade Debtors 5
Migration Suspense 5
being unpaid debtor invoices
Totals 540 540

At this point our Trial Balance looks like this

xyz Ltd
Trial Balance 31/08/06 [new system]
Dr £'000 Cr £'000
Fixed Assets
Trade Debtors 500
Other Debtors
Bank 75
Trade Creditors
Other Creditors
Reserves
Migration Suspense 575
Totals 575 575

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:

xyz Ltd
Creditors 31/08/06
Dr £'000 Cr £'000
Open Invoices 425
Unallocated Credits 3
Unallocated Payments 2
Part Paid Items etc.
Sub-total 5 425
TB Creditors Balance 420

The journal entries will be:

xyz Ltd
Migration Journal #4
Dr £'000 Cr £'000
Migration Suspense 425
Trade Creditors 425
being unpaid supplier invoices
Trade Creditors 3
Migration Suspense 3
being unallocated credit notes
Trade Creditors 2
Migration Suspense 2
being unallocated payments
Totals 430 430

Our trial balance now looks like this

xyz Ltd
Trial Balance 31/08/06 [new system]
Dr £'000 Cr £'000
Fixed Assets
Trade Debtors 500
Other Debtors
Bank 75
Trade Creditors 420
Other Creditors
Reserves
Migration Suspense 155
Totals 575 575

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.

xyz Ltd
Migration Journal #5
Dr £'000 Cr £'000
Fixed Assets 150
Migration Suspense 150
being opening TB Fixed Assets
Other Debtors 40
Migration Suspense 40
being opening TB other debtors
Migration Suspense 140
Other Creditors 140
being opening TB other creditors
Migration Suspense 205
Reserves 205
being opening TB Reserves balance
Totals 535 535

Our Trial Balance now looks like this:

xyz Ltd
Trial Balance 31/08/06 [new system]
Dr £'000 Cr £'000
Fixed Assets 150
Trade Debtors 500
Other Debtors 40
Bank 75
Trade Creditors 420
Other Creditors 140
Reserves 205
Migration Suspense
Totals 765 765

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.

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:financial data |data migration |new systems |system implementation

Converting data for a New System

Tuesday 16 Jan, 2007 - 16:19pm | 0 comments |

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.

Make Date Field Consistent
A B C
1 1012007 01012007
2
3

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

=(TEXT(A1,"00000000"))
....converts the field to eight character length

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

Extract Date Elements
A B C D E F
1 1012007 01012007 01 01 2007
2
3

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

=LEFT(B1,2)
....extracts the first the day from the string

=LEFT(RIGHT(B1,6),2)
....extracts the first the month from the string

=RIGHT(B1,4)
....extracts the first the year from the string

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

Convert to Date Format
A B C D E F
1 1012007 01012007 01 01 2007 01/01/2007
2
3

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

=DATE(E1,D1,C1)
....converts to date format dd/mm/yyyy

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

=DATE(RIGHT(TEXT(A1,"00000000"),4),LEFT(RIGHT(TEXT(A1,"00000000"),6,2),LEFT(TEXT(A1,"00000000"),2))
....converts our original sring of dmmyyyy to date format dd/mm/yyyy

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.

Modify a Date
A B C
1 31/12/2004 DATE(2005,01,01),A1,DATE(2005,01,01))>01/01/2005
2
3

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.

=IF(A1>DATE(2005,01,01),A1,DATE(2005,01,01))
....changes the date if the date is earlier than the condition

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.

Trim Whitespace
A B C
1 ACCE01 ACCE01
2
3

=TRIM(A1)
....removes whitespace from the end of a fixed length field

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

Create New Code
A B C D
1 Alpha Ltd A 001 A001
2 Alpha 2 Ltd A 002 A002
3 Alpha 3 Ltd A 003 A003
4 Beta Ltd B 001 B001

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 &.

=TEXT(COUNTIF($B$1:$B2,B2),"000")
....counts the number of fields begining with the letter in cell B2 in the data range $B$1 to $B2. The result is returned as a text field with a length of three characters

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.

Determine Length of Field
A B C D
1 Product x Description 21 20),"Fix this","")'>Fix this
2
3

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.

=LEN(A1)
....returns the number of character in cell A1

=IF(LEN(A1)>20,"Fix this","")
....checks to see if the number of characters in cell A1 is greater than or less than twenty. If its greater, text is displayed.


Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Excel |data conversion |data migration |new systems |System Implementation

Windows Automatic Update

Sunday 14 Jan, 2007 - 22:46pm | 0 comments |

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

Restart Your Computer

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?

Living dangerously with 3 seconds to go

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.

Automatic Update

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.

Windows Updates Settings

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Microsoft |Windows |Updates

Hard Drive not Installed

Saturday 13 Jan, 2007 - 17:09pm | 0 comments |

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..

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Hard drives |laptops |IBM |T21 |T22 |Windows

Mod_Rewrite and Regular Expressions

Friday 12 Jan, 2007 - 19:50pm | 0 comments |

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..

Metacharacters used in Regular Expressions
Character Meaning
 Start matching from this point
$ End matching at this point
. Any character, equivalent to the wildcard (note: except: the dot will not match character to denote a new line i.e ). Be careful when using the wildcard, particularly in validation as you may not want to match every character
[ ] Denotes a character class. Will match any one of  the characters included between the square brackets, as in [xyz] will match any of x or y or z, not all three together. Note the dot is not a wildcard if used between square brackets, it's simply treated as a dot.
| Or
? Optional
+ Match at least one or more times
* Match zero to infinite number of times
{ } Curly braces are used to specify a specific number of times to match
( ) Used for Grouping
Use before characters to escape or negate the meaning of them  $ . +
- Range for matching, as in [0-9] numeric characters or [a-z] lowercase characters

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

Combined Metacharacters in Regular Expressions (characters combined)
Character Meaning
[^ ] Not like the following as in [^xyz] not any one of x y or z

Some common Examples

Some common patterns (characters combined)
Character Meaning
[0-9] Numeric, will match any one numeric character
[a-z] Lower case alphabetic
[A-Z] Upper case alphabetic
[a-zA-z] Alphabetic (upper and lower case)
[^0-9] Not numeric
[0-9a-fA-F] Matches a single hexadecimal character
"[^" ]*" Matches between double quotes
([^/]+) Match any folder name

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.

Regular Expressions (shorthand characters)
Character Meaning
\d Matches a single numeric character
\t Matches a tab character (ASCII 0x09)
\r Matches carriage return (ASCII 0x0D)
\n Matches line feed (ASCII 0x0A)
\A Only ever matches at the start of a string
\Z Only ever matches at the end of a string
\b Matches at a word boundary
\w  
\B  

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.

$pattern = "/^[a-z]$/";
$match = "s";
echo preg_match ($pattern, $match);

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

$pattern = "/^[a-zA-Z]$/";
$match = "S";
echo preg_match ($pattern, $match);

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.

$pattern = "/^[a-zA-Z]{3}$/";
$match = "SAP";
echo preg_match ($pattern, $match);

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.

$pattern = "/^[a-zA-Z]+$/";
$match = "Navision";
echo preg_match ($pattern, $match);

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.

$pattern = "/^[^a-zA-Z]+$/";
$match = "1234,�£";
echo preg_match ($pattern, $match);

The following will match any number of aphanumeric characters

$pattern = "/^[0-9a-zA-Z]+$/";
$match = "Lotus123";
echo preg_match ($pattern, $match);

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

$pattern = "/^[a-zA-Z]+([0-9]+)?$/";
$match = "Lotus123";
echo preg_match ($pattern, $match);

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

$pattern = "/^[a-zA-Z]+s?([0-9]+)?$/";
$match = "Lotus123";
echo preg_match ($pattern, $match);

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

$pattern = "/^(Lotus|lotus)s?([0-9]{3})?$/";
$match = "Lotus123";
echo preg_match ($pattern, $match);

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

$pattern = "/^(Lotus)s?([d]{3})?$/";
$match = "Lotus123";
echo preg_match ($pattern, $match);

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

$pattern = "/^blog.php/([d]{4})/([d]{2})/([d]{2})/([-0-9a-zA-z]+).html$/";
$match = "blog.php/2006/12/02/here-it-is.html";
echo preg_match ($pattern, $match);

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

Command Flags (mod_rewrite)
Character Meaning
[R] Redirect. Write as [R=301] for example to change the type
[F] Forces the URL to be forbiden
[G] Results in a 401 message
[L] The last rule. Use this at the end of every rewrite rule that doesn't link together.
[N] Rerun the rules again from the start
[C] Chains the rule with the next one
[NC] No case. Make the rule case insensitive
 

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.

Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Regex |Regular Expressions |Apache |Mod Rewrite

Hello World

Friday 12 Jan, 2007 - 16:40pm | 0 comments |

The customary greeting for new programs
Comment
| Link | Back to top | del.icio.us digg it furl reddit

Posted in: Business
Tags:Hello World |Blogs


© Eriginal Ltd 2011, all rights reserved