Manage Imports

See more

Tips for importing data

Follow

The following is a selection of tips and tricks to use when importing data.  

Check List 

The following is a list of checks we recommend you run on your data before importing.

  • Remove duplicate records.  
    • Duplicate contact: FirstName, LastName, and EmailAddress are the same.
    • Duplicate organisation: Name is the same.
  • Check that addresses are in the correct format and are valid.
    • Content must be split into the following format: AddressLine1, AddressLine2, Suburb, PostCode, City, State, Country.
    • Address must contain a value in AddressLine1.
    • Address contains a country (this is not essential).
    • Check addresses are in the correct case. Data which is in all capitals can look messy.
  • Check that dates are in the correct format (d-mmm-yyyy, e.g. 17-Apr-2014) See below section for correcting the date format.
  • Check that email addresses are valid.
  • Check that all data is in the required format for each of the fields
  • Check that First Names, Last Names, and Organisation names are in the correct case, i.e. they are not all capitalised.
  • 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). See instructions below 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 63 or 127 characters. If an import fails with an 'Inner Exception' error, look for a field in your import file that exceeds this character limit and then edit it down. We have increased the character limit on the position field to 128 characters.

General

Formatting Dates

Our importers all accept the date format d-mmm-yyyy, e.g. 17-Apr-2014. If the dates in your data are not in this format, the data may not be imported.  

To change the format to d-mmm-yyyy:

  • Open the spreadsheet you're planning to import
  • Highlight all dates in the spreadsheet, being careful not to highlight the headings
  • Right click on your mouse and select Format Cells
  • From the Category section, select Custom
  • In the Type box, type d-mmm-yyyy
  • Press OK. 
    2014-07-04_10_26_33-platform_version_report.csv___Read-Only__-_Excel.png

Check for duplicates in Excel first, before importing  

  • Open the spreadsheet you are planning to import.
  • Arrange the columns so that column A= FirstName, B=LastName, C=EmailAddress.
  • Insert a new column into column D with a column heading called UserKey.
  • Add the following formula to all the cells in column D.

    =CONCATENATE(TRIM(A2),TRIM(B2),TRIM(C2))

    Note: The Trim formula removes any spaces from the cell values. Arlo will also do this when the data is imported.
     
  • Select all rows and columns which contain data.
  • From the Data menu in Excel, within the Data Tools group, press Remove Duplicates.
    1.png
    • Tick My data has headings.
    • Press Unselect All. 
    • Tick UserKey item.
    • Press OK. A confirmation window will appear that will tell you how many duplicates were removed.
  • Delete the UserKey column.
  • Save the changes to the spreadsheet.
  • Import into Arlo.

Split out content from a single column into multiple columns

Use the following process to split content which is currently stored in a single column, across into multiple columns. 
 
For example, column A contains a person's full name (Felicity Merrington) and you want to split this into two columns; first name and last name.
  • Select column A.
  • From the Data menu in Excel, within the Data Tools group, press Text to columns.
    2.png 
  • Press Next. 
  • Tick the options which indicate how the data is currently separated within the single column.     
    3.png

    Hint: If the data is separated by a character, e.g. "," or "|", tick Other: and enter the character in the box.
     
  • View the Data preview to ensure the data appears to be split correctly. 
  • Press Next. You will be taken to the next step.
  • Press Finish.

Quickly find and select a cell, row or column in the Spreadsheet  

  • While in Excel, press <CTRL> <G>.
  • Enter the the reference you would like to select. e.g. 
    • enter A1 to select cell A1
    • enter 509:509 to select row 509
    • enter B:B to select column B
    • enter A1:B100 to select the block of cells from A1 to B100 - 2 columns (A, B) and 100 rows 
      goto.png

Quick way to copy a Formula or content down to the bottom of a column

  • Select the cell you would like to copy down.
  • Double click the bottom right corner.
    1.png

Working with Address Fields 

Merge (Concatenate) two columns of data into a single column, separated by a line break

  • Use the formula below to merge column A and B where the contents of column B will appear as a second line in the same cell, i.e. the formula will add a line break.

    =CONCATENATE(A1,CHAR(10),B1)
    1.png 

    or use the following formula which also;
    • removes potential spaces in front of and at then end of cells (TRIM formula)
    • only includes a line break if both columns contain data, i.e. does not include unnecessary line breaks

      =CONCATENATE(TRIM(A1),IF(AND(A1<>"",B1<>""),CHAR(10),""),TRIM(B1)) 
Note 1:  Apply text wrapping in Excel to view the data correctly.
    • Select the column containing the data.
    • In Excel, select the Tools tab/menu.
    • From the Alignment group, press Wrap Text.
      a.png
Note 2:  Importing multiple address lines.
  • Arlo importers support importing two address lines (e.g. PostalAddressLine1, PostalAdressLine2, Suburb, PostCode, City, and Country ). It the addresses your are importing contain more than 2 lines, use line1 (e.g. PhysicalAddressLine1) for the first line and concatenate the other lines into line 2 (e.g. PhysicalAddressLine2).

Working with Fields Containing Leading Zeros (e.g Post Code, Phone Numbers, Customer ID)

Problem

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”. This isn’t a formatting issue, and converting the column to Text won’t cause the leading zero to reappear – the data is permanently changed, and becomes an issue if the file is then resaved.

These leading zeros are significant in Post codes in Australia where 875 isn’t a valid postcode in some regions but 0875 is. This is also likely to affect cells that contain other leading zero numbers such as phone numbers with area codes.

Solution

You can utilize the Import functionality of the Excel to retain the leading zeros in fields of CSV files.

  1. Open a blank Excel Spreadsheet.
  2. Open the Data tab of the ribbon. In the Get External Data section, click the From Text icon.
  3. In the "Select Data Source" window, find and select the CSV file to open.
  4. In Step 1 of the wizard of the Wizard, choose the file type as Delimited
  5. In Step 2 of the wizard, tick Comma as the delimiter, and untick Tab if ticked.
  6. In Step 3 of the wizard, 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.
  7. Click on Finish.
  8. 'Import Data' window will pop up.  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 worksheet
  9. Click 'OK'
  10. The data will now open in Excel. Save the file
  11. If you've gone through this to Import into Arlo, you can now import it.

Other articles that may be helpful:

Related articles

Was this article helpful?
0 out of 0 found this helpful