Auto Importer Triggers

Unique Triggers can be added to the name of the file that is to be handled by AutoImporter. These Triggers offer the User a myriad of customization options that can automate processes traditionally considered manual pre- and post-import tasks. Ease of use and repeatability make adapting any import to a standard set of options simple.

All Triggers start with an underscore and end with the dot before the extension or an underscore that starts a new trigger.

Ex: TMMFileName_trntrue.txt

Ex: TMMFileName_trntrue_incempty.txt

_trntrue_

Truncate destination table before inserting new records.

Ex:

  • fileName_trntrue.txt


_incempty_

Include empty records in import. By default, if there are empty records, they are ignored.

Ex:

  • fileName_incempty.txt


_datecolN_

Force column index 'N' to be formatted as a date. This has been depricated as we auto detect date fields.

Ex:

  • To force column 3 to be a date
  • fileName_datecol3.txt


_skpNlns_

Skip the first 'N' lines of the file.

Ex:

  • Skip the first 10 lines of a file
  • fileName_skp10lns.txt


_skpcolN_

Ignore the Nth column in the file

Ex:

  • Ignore the first column of the file
  • fileName_skpcol1.txt


_brgroupGUID_

Run Data Process identified by GUID after file import is complete.

Ex:

  • Run Data Process guid 123456789qwer after import
  • fileName_brgroup123456789qwer.txt


_prebrgroupGUID_

Run Data Process identified by GUID before File import starts.

Ex:

  • Run Data Process guid 123456789qwer before import
  • fileName_prebrgroup123456789qwer.txt


_mrgcolN_

Insert new records and update existing records using column 'N' as the unique key. Can use multiple.

Ex:

  • Use column 3 and 5 of the file as a unique key for merging.
  • fileName_mrgcol3_mrgcol5.txt


_sysidN_

Update existing records using column 'N' as the system identifier.

Ex:

  • First column contains the system identifier to update table.
  • fileName_sysid1.txt


_insnewN_

Insert new records records using column 'N' as the unique key. Can use multiple.

Ex:

  • Use Column 5 as unique key for inserting new records.
  • fileName_insnew5.txt


_runcftrue_

Run custom fields on insert. Off by default. When uploading via Fix View or Forms, this is on be default.

Ex:

  • fileName_runcftrue.txt


_rtrnid_

Return the system identifiers for each row inserted. For API use.

Ex:

  • fileName_rtrnid.txt


_frcnull_

Force empty values to be NULLs in the Database, default is an empty string.

Ex:

  • fileName_frcnull.txt


_sheetN_

When importing an Excel file, Import Sheet number 'N'. Can use Multiple. All sheets selected will import into default destination table. Watch out for hidden sheets.

Ex:

  • Import the 3rd sheet of a workbook.
  • fileName_sheet3.xlsx


_sheetN[GUID]_

When importing an Excel file, Import Sheet number 'N' into the table specified by GUID. Can use Multiple.

Ex:

  • Import the 3rd sheet of a workbook into table selected. Import sheet 5 into table with guid 123456789qwer.
  • fileName_sheet3_sheet5[123456789qwer].xlsx


_encENCODING_

Used to specify the character encoding of the file. Assumed to be UTF8 by default.

Supported Encoding Types:

  • UTF16
  • UTF16LE
  • UTF16BE
  • ISO8859
  • WINDOWS1251
  • WINDOWS1252

Ex:

  • File with character encoding of ISO-8859-1
  • fileName_encISO88591.txt


_nopadtxt_

Do not add padding to newly created text fields. Force read of full file to determine datatypes/lengths without padding.  Padding added by default is the Length + 25% rounded up to a full character.

Ex:

  • fileName_nopadtxt.txt


_nopadnum_

Do not add padding to newly created number fields. Force read of full file to determine datatypes/lengths without padding. Padding added is 2 digits to the left of the Decimal. If it has digits to the right and it's not a Currency pad 1 digit to the right. If it is a Currency but has more than 2 digits to the right pad 1 digit to the right. Otherwise no padding to the right

Ex:

  • fileName_nopadnum.txt


_nopad_

Combination of _nopadtxt_ and _nopadnum_.

Ex:

  • fileName_nopad.txt


_dfltN[VAL123]_

Set VAL123 as a default value for Column 'N'. If the field in column 'N' is empty during import it is populated with 'VAL123'. Must be simple value for use in file name trigger, no special characters.

Ex:

  • If the field in column 3 of the file is empty, 'sample' will be used in it's place.
  • fileName_dflt3[sample].txt


_nst[FOLDERNAME]_

Create a new folder, named 'FOLDERNAME', that will contain a new table to import file into. If the file is a compressed file with multiple files, a table for each file will be created. Must be simple value for use in file name trigger, no special characters.

Ex:

  • Create a new folder called 'newFolder' at the root level, and create a table for each file in the zip.
  • fileName_nst[newFolder].zip


_estGUID_

Create a new table inside of folder identified by GUID to import file into. If the file is a compressed file with multiple files, a table for each file will be created. Can be used in conjunction with _nst[NEWFOLDER]_ to create a new folder in an existing folder.

Ex:

  • Create a New folder called 'SampleData' inside of folder identified by guid 123456789qwer. Create new tables for each file in the zip.
  • fileName_est1234456789qwer_nst[sampleData].zip


_mtlN_

Force all newly created text fields to have a minimum length of 'N'.

Ex:

  • Text fields created from this import will have a minimum length of 150.
  • fileName_mtl150.txt


_autocommit_

Do not use a transaction for insert. Can speed up large imports.

Ex:

  • fileName_autocommit.txt


_addnewcols_

Automatically add new columns to the table if they appear in the file (renamed columns will be treated as new).

Ex:

  • fileName_addnewcols.txt


_adjustfields_

Automatically Adjust Datatypes and Lengths if needed. Utilizes the _nopad_ options to force read of full file to determine datatypes/lengths without padding.

Ex:

  • fileName_adjustfields.txt


_autoadjust_

Combination of _addnewcols_ and _adjustfields_.

Ex:

  • fileName_autoadjust.txt