Converting Data

The format of data exported from your legacy system might not be in the required format to import into your 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 fail to import 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.

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.

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)

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/2006. Our accounting year ends in December. Our new system is set up with one back year, i.e. 2007 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/2006	01/01/2007
2
3

The formula is a conditional argument. If the date is earlier than 01/01/2007 change the date to 01/01/2007, 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.

Trim Whitespace

	A	B	C
1	ACCE01	ACCE01
2
3

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

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

©newITsystem.com 2010, all rights reserved ::

Design: Eriginal