Automatically edit import field before you import it

You can import a field from the import file by linking the field to a Profit field in the import definition. It may, however, be necessary to edit the field to be imported before it is actually imported into Profit.

You can convert a field value to capital letters, for example, edit different date formats or split an address into street-number-addition. For complicated actions you can apply VB script.

Link Profit field to import field:

  1. Go to the import function.
  2. Go to the view with the import fields.
  3. Drag the field to be imported to the Profit field, so that these fields are linked.

    Set the options for automatic editing. See the following subjects.

  4. Use one of the following options for fields in the import file that are not filled:
    • Value for empty fields

      Enter a value this is used to fill empty fields during the import. This value applies to all records to be imported.

    • If blank, ask for the value
    • For every empty field in the import you can enter a value during the import. This allows you to set a value to be recorded for each record.

Contents
Import options for date fields

Use this option if dates in the import file have a different format from those in Profit. This allows Profit to correctly interpret the date while it imports.

Set reading options for date fields:

  1. Select the field you want to adjust.
  2. Select the action you want to perform.

    Select the date format that is used in the import file.

Consult the following examples when you select the correct format:

Import option

Example
(date = 15 January 2013)

Date (DDMMYY)

150113

Date (DDMMYYYY)

15012013

Date (DdxMMxYY)

15-01-13

Date (DDxMMxYYYY)

15-01-2013

Date (MMDDYY)

011513

Date (MMDDYYYY)

01152013

Date (MmxDDxYY)

01-15-13

Date (MMxDDxYYYY)

01-15-2013

Date (YYMMDD)

130115

Date (YYYYMMDD)

20130115

Import options for numerical fields

Use these options to edit amounts and figures to be imported.

Edit amounts and figures:

  1. Select the field you want to adjust.
  2. Select the action you want to perform.

Consult the following examples when selecting the correct format:

Import option

Action on the value to be imported

Number (make negative)

The number becomes negative. A negative number remains negative.

Number (make positive)

The number becomes positive.

Number (invert)

If the number is positive, it will become negative. If the number is positive, it will become negative.

Number (times 10)

Multiply number by 10.

Number (times 100)

Multiply number by 100.

Number (times 1000)

Multiply number by 1000.

Number (divide by 10)

Divide number by 10.

Number (divide by 100)

Divide number by 100.

Number (divide by 1000)

Divide number by 1000.

Number (blank = 0)

If the import file does not contain a value, the value 0 is imported.

Round to whole numbers

Numbers with decimals are rounded off.

Import options for text fields

Use these options to edit text

Edit text:

  1. Select the field you want to adjust.
  2. Select the action you want to perform.

Consult the following examples when selecting the correct format:

Import option

Action on the value to be imported

Text (capitals)

Change text to capital letters.

Text (small letters)

Change text to lower case.

Text (first character capital)

Change the first character of the text to a capital letter.

Text leave out 'NULL'

If the value to be read is NULL, do not read it.

Text (first word)

Only import the first word.

Text (last word)

Only import the last word.

Text (first number)

Only import the first number that appears in the text.

Text (everything except a number)

Do not import numbers, but import the rest of the text.

Text (truncate)

Read text in so far as it is not longer than the maximum field length.

Example: 

You want to import ledger accounts, but all the descriptions start with a lower-case letter.

Use the Text (first character capital) option to convert the first letter to a capital letter for each description.

For example, the description 'machines' is changed to 'Machines'.

Import options for address fields

Use this option to edit addresses, for example, to split an address to be imported into several fields.

Edit addresses:

  1. Select the field you want to adjust.
  2. Select the action you want to perform.

  3. If the field to be imported consists of a street and house number, use these options:
    • Link the Street field to the import field and use the Address (street) option.
    • Link the House number field to the import field and Address (number) in.

Address option

Explanation

Address (street)

Only read the street

Address (number)

Only read the house number.

Address (house nr. addition)

Only read the addition to the house number

Address (number with addition)

Read number and addition in a single field.
Import options for postal codes

Use these options to edit postal codes.

Edit postal codes:

  1. Select the field you want to adjust.
  2. Select the action you want to perform.

Consult the following examples when selecting the correct format:

Postal code option

Explanation

Postal code (add space)

Add space between the numbers and letters of the postal code.

For example: 1111AA is changed to 1111 AA.

Postal code (delete space)

Remove space between the numbers and letters of the postal code.

For example: 1111 AA is changed to 1111AA.

Directly to

  1. Import options for fields
  2. Empty field via import
  3. Import fixed value for a field
  4. Automatically edit import field for this imported
  5. Automatically add consecutive number to field via import
  6. Enter field value for each line during import
  7. Enter field value once during import
  8. Import images