In this article
The following article outlines a number of tips and tricks to use when importing data.
- Checklist before importing
- Formatting dates for imports
- Checking and removing duplicate contacts
- Consolidating or separating addresses for imports
- Importing fields containing lead zeros
Checklist before importing
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. All fields have a maximum number of characters that they will accept. Typically this is either 64 or 128 characters.
Formatting dates for imports
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.
Checking and removing for duplicate contacts
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.
Consolidating or separating addresses for import
Merging two columns of data into a single column, separated by a line break
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 linebreak 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).
Separate address information into separate columns
If you have address information consolidated into a single field, you may want to split it into sperate 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.
Importing fields containing leading zeros
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.