The following article outlines a number of tips and tricks to use when importing data.
The following is a list of checks we recommend you run on your data before importing.
- Check that dates are in the correct format (dd-mmm-yyyy, e.g. 17-Apr-2022). Learn more about how to format dates.
- Remove duplicate records from your import file. Learn more about removing duplicates.
- Duplicate contacts: FirstName, LastName, and EmailAddress are the same.
- Duplicate organisations: Name is the same.
- Check that addresses are in the correct format on your import file. Learn more about consolidating or separating addresses for imports.
- Addressed being imported must contain a value in 'AddressLine1'.
- Address contains a country (this is not essential).
- Check if there are any fields which might contain a leading zero (e.g. 0458) in the values (e.g Post Code, Phone numbers, Customer ID, Contact Code). Learn how to ensure the leading zeros are maintained during the import process.
- Check for long Job/Position titles and other fields. Some fields have a maximum number of characters that they will accept. Typically this is either 64 or 128 characters.
Arlo importers accept the date format 'dd-mmm-yyyy', e.g. 17-Apr-2022. If the dates in your data are not in this format, the data cannot be imported. The following instructions outline how to update your date format.
- In Excel, open the file you are planning to import.
- Highlight the dates column, excluding the heading.
- Right-click and select Format Cells.
- From the Category section, select Custom.
- In the Type box, type 'dd-mmm-yyyy'. The sample will be updated and should display as follows.
- Press OK. Your dates should be updated in the correct format.
The following process should be used to remove contacts who have the same FirstName, LastName, and EmailAddress on your import file.
- In Excel, open the file you are planning to import.
- Arrange the columns so that column A= FirstName, B=LastName, C=EmailAddress.
- Highlight the FirstName, LastName, and EmailAddress columns.
- From the Data menu, press Remove Duplicates.
- Ensure the FirstName, LastName, and EmailAddress columns are selected.
- Press OK. Excel will remove all records where the three columns matched, and only leave one remaining record.
Arlo supports importing the following address fields;
- AddressLine1
- AddressLine2
- Suburb
- City
- Postcode
- State
- Country
If you have additional address fields which are not supported and want to keep the information (e.g. a third address line), you will need to merge them into one of the supported fields.
- In Excel, use the formula '=CONCATENATE(TRIM(A2),IF(AND(A2<>"",B2<>""),CHAR(10),""),TRIM(B2))' to merge the contents of two columns into a new single column. The formula will also add a line break between the two columns if required.
- Once the formula has been applied successfully, drag it down across the additional columns.
- From the Home tab, press Wrap Text.
- Ensure the new address field is displayed correctly.
- You can now copy the new column to your import file (as values so you don't copy the formula).
If you have address information consolidated into a single field, you may want to split it into separate columns, so that when you import it, it will be in the correct place. This process may only work if you have a common character splitting up the address details, for example, a comma.
- In Excel, highlight the address row you would like to split.
- From the Data tab, select Text to Columns.
- Select Delimited.
- Choose the character that should split the data. Make sure the preview looks correct.
- Press Finish. Ensure you address data looks correct.
- You can now copy the new columns to your import file.
Opening CSV files in Excel that have leading zeros in columns can cause Excel to read those columns as numbers and silently drop the zeros.
For example, “0875” becomes “875”. If saved like this, the data is permanently changed and cannot be restored.
You can utilize Excels import functionality to retain the leading zeros in fields of CSV files.
- In Excel, open a blank sheet.
- From the Data tab, click the From Text icon.
- Find and select the CSV file which contains your data.
- Select Delimited.
- Tick Comma as the delimiter, and untick Tab if ticked.
- Click on the column/s that should retain the zeros (so it’s coloured black) and change its type to Text in the Column Data Format section.
- Press Finish.
- You can either import the data directly into the worksheet you have open (unless you want it somewhere other than top right, ensure cell is =$A$1) or into a new sheet.
- Press OK.
- The data will now open in Excel. Save the file and you can now use it to import.