Some people seem to love reinventing the wheel – why learn from others’ mistakes when you can learn the same thing from your own?Well, for one thing, it might save time and effort trying to fix the outcome, which is why I always try and learn from my colleagues and pass on my methods to them.Here then, in no particular order, are general rules I follow and consider to be “best practice”.
- If you are logged into a system, never leave your computer unlocked if you leave it unattended.
- If you are logged into a system, close all windows before you leave your computer, so you aren’t locking records that other people might want to work on.
- Data is out of date as soon as it is extracted from the system, so it should be used immediately or treated with suspicion. Never use an old file if you can generate the data freshly. There are always exceptions to this rule, but if you always follow it you will never get caught out.
- Put the date in the name of extracted data files so you can see when the data was generated. Use YYMMDD format so they can be sorted by name and still come out in date order – for example 121015_10_JobHeader.xlsx is a file containing Job Header information for Company 10 that was extracted on 15 October 2012.
- Before importing a data file, always check its contents. If you are distracted for any appreciable length of time between checking and importing, check it again. To adapt an old engineering tip: check twice, import once.
- During data importing from a folder containing many files, when the import program presents a file browser for you to choose the data file, sort the list by date modified so you can positively see your file at the top of the list (assuming it is the last one that you edited, which it should be). This routine takes a few seconds at most, and the consequences of not doing this can be a lot of lost time in unravelling an unintended import.
- Always save source data in Excel files so that all formatting is maintained. You will understand what I mean if you have ever opened a .txt source file and had Excel remove all leading zeroes, convert sort codes to dates, convert long alpha-numeric client codes to scientific notation numbers, and so on.
- When filtering data in a spreadsheet with Freeze Pane on, hit Ctrl-Home to make sure that no data is obscured under the frozen area.
- When debugging an import file, make sure you close the error file before reimporting.
- Before importing changes to existing records, make a copy of the records – you can use this for two purposes:
- Comparing the data before and after your import to check the changes have been made correctly
- Recreating the original data if you cock-up and your import does things you hadn’t planned it to.
This article will tell you more about comparing data.
- Immediately after running a successful import, clear the input file field. The reason for this is that the file name refers to a temporary copy of your import file, which is sitting on the server, and it is still ready to go. So if you accidentally press return, the import will run again straight away.
- Do NOT hide columns or lines in Excel data files – you may forget that data is hidden there and save the file as text to import (the hidden data will be saved in the text file without your knowledge), and if you copy a line and paste it elsewhere, there is a danger of field values being transposed to other fields.