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
_mstGUID_
Merge sub files from a compressed file into a folder. If a table exists in the folder, specified by the GUID, with the same name as the file, it will import into that table, otherwise create a new table using the filename. If no GUID is provided then it will use the Folder of the table being imported into.
Ex:
- Search folder that the table that the file is being imported for table names that match filenames in the zip file. Import into those that match, or create new tables for the ones that do not.
- fileName_mst.zip
- Search folder identified by GUID 1234567890qwer for table names that match filenames in the zip file. Import into those that match, or create new tables for the ones that do not.
- fileName_mst1234567890qwer.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