Firstly, if you dont feel confident in cleansing your data and uploading it into Mira, we offer a service to do it for you at $200 per hour. Contact brad@mira.au to organise if you require it.
The time taken to cleanse your data will depend on how much the data is structured. If you dont cleanse your data it will bring up multiple error messages which will need to be fixed for each individual record.
For those who are more technically confident I will outline how to use Excel functions to filter the data to improve its consistency and minimise the errors that need to be edited individually.
We assume that you can use Excel at a intermediate level.
A CSV data file can easily be opened within Excel and will have columns for each type of data which usually has a header which describes that column (eg: id, Customer name, Address, Phone, email, etc). Each row is a new record which outlines all the client details. See example below.
Step 1 : Look at the data to see what issues are present in the CSV data file.
Some key issues are highlighted below and are typical for data files because people dont always put things in the right box and often combine information in the same box.
A. So at Column E and Row 3 which is supposed to be an email field, we have the text "DO NOT PROVIDE SERVICES" which should be in the Notes column.
B. In Column F we have cells with a phone number mixed with a name which should only include a phone number.
C. In Column F and Row 4 we have a mobile number that has lost its leading "0".
D. In columns H, I, J, K we have components of an address that should be imported as one column into Mira.
Step 2: Fixing the issues is outlined below using Excel.
A. You may need to manually cut and paste these into a notes column if there are only a few of them in the file. Otherwise you may need to use the techniques below to get the desired result.
B. To separate numbers from text in a cell:
Create a new column for the number and label it (eg: phone, mobile, etc)
If the number is first then enter the following formula into the first data row.
=LEFT(Column/Row,SUM(LEN(Column/Row)-LEN(SUBSTITUTE(Column/Row, {"0","1","2","3","4","5","6","7","8","9"},"")))) (eg:=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
Starting at the left hand side of the cell it counts all the numbers without a space and then removes what follows.If the number is last then enter the following formula into the first data row.
=RIGHT(Column/Row,SUM(LEN(Column/Row)-LEN(SUBSTITUTE(Column/Row, {"0","1","2","3","4","5","6","7","8","9"},"")))) (eg:=RIGHT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
Starting at the right hand side of the cell it counts all the numbers without a space and then removes what comes before it.Copy down the formula down the column to the bottom of the rows.
Copy the entire column and paste the Values (See below) into the Original Mobile/Phone column. This will remove the formulas and only input the value.
Create a new column for the text and label it (eg: notes, etc)
Enter the following formula in the first data row
=RIGHT(Column/Row,LEN(Column/Row)-LEN(Column/Row used in 3. above))
(eg:=RIGHT(A2,LEN(A2)-LEN(B2)) )
This formula removes the number from Left hand side of the cell and removes it and keeps the text.Copy down the formula down the column to the bottom of the rows.
Copy the entire column and paste the Values into a new Notes column.
C. To add the leading 0 to a mobile phone number:
Create a new column and label it (mobile, phone, etc).
Enter the following formula into the first data row.
=IF(LEFT(Column/Row, 1)="4", "0"&Column/Row, Column/Row) eg: =IF(LEFT(A2, 1)="4", "0"&A2, A2)
This formula examines the first character of the data and checks if it is the "4" character. If is is the "4" character it combines a 0 at the front of the number. If it is not a "4" it just reproduces the original number.Copy down the formula down the column to the bottom of the rows.
Copy the entire column and paste the Values into the Original Mobile/Phone column.
D. To add a set of columns into a single column:
Create a new column and label it (address 1, address 2, etc).
Enter the following formula into the first data row.
=CONCATENATE(Column1/Row1, " ", Column2/Row2)
eg:=CONCATENATE(A2, " ", B2)This formula will combine the columns into a single column with a space between the data from each column.Copy down the formula down the column to the bottom of the rows.
Copy the entire column and paste the Values into the Original address column.
E. Sometimes some records will seem to have empty rows but actually have a space in the cell. This will often create errors in the import. To remove these spaces:
Highlight the range with your mouse
Open the Find and Replace dialog in Excel
Enter a space in the Find field and leave the replace field empty
Click on the Find all and check it is only finding the spaces in the column you want to change
Click on Replace all