How to trim columns when importing

Overview of how to set up a Trim function

If you need an easy way to reduce the length of a field to fit the maximum sized permitted by QuickBooks or Xero then you can use a trim function to do that.  That function can be applied to a specific column in your import file and then saved along with the map so you can use on subsequent imports.

The LEFT or RIGHT function extracts a given number of characters from the left or right side of a supplied cell contents. For example a customer name of Handy Builder in the Customer Name column THEN , LEFT("Customer Name",3) returns "Han".


Demonstration of how to setup a LEFT and RIGHT Function



Follow these steps:

To setup a trim function follow these steps:
  1. When editing a map in the function column opposite the field you want to truncate select the drop down list and the option to Add New Function
  2. Select the type of function as LEFT and RIGHT Function, and give the function a name
  3. Enter the Expression according to the syntax guide below.
  4. Click OK and the function will be saved and applied to the map

Trim LEFT Expression syntax
LEFT("column_name",num_chars)
example
LEFT("Customer Name",3)

Trim RIGHT Expression syntax 
RIGHT("column_name",num_chars)
example 
RIGHT("Customer Name",4)



ref.AXIS-960