5: Formatting Statements

The DISCARDFIRSTNRECORDS,X statement tells the system to ignore the number of rows (X) in the data file, starting with row one. X represents the number of rows to be ignored (one or more), not counting blank lines.

If a line has spaces or blanks in it, it is discarded. If a line is empty, no spaces but a carriage return/line feed, the system skips it. Therefore, it is not counted as a discarded line and the first line of data is discarded.

DISCARDFIRSTNRECORDS,1

The GEN_CODING statement is used to have the system to automatically generate the line of the detail coding. This statement can be used in conjunction with the APPLY_OFFSETS statement to have the system automatically generate all lines. Do not use GEN_CODING if importing Coding Lines.

Formatting Statement Section of DEF

GEN_CODING

The APPLY_OFFSETS statement is used when only one side of a transaction is available. It tells the system to automatically apply offsets to transactions to create a balanced entry. If the data file contains a balanced entry for the transaction, it produces an error message and does not import that transaction.

APPLY_OFFSETS

From Data File

Gen Journal,01/28/00,01101400011011011,Jan. U. Way,-150.00

Gen Journal,03/28/00,01101400011011011,March U. Way,-200.00

System Generates Offsets

If you include the Apply_Offsets statement in the definition file, and you are using the preceding data file, the system might produce the following offset to an expense account:

Gen Journal,03/28/00,01101500011011011,Wages,+350.00

The CLEANLINE_READ statement tells the system how to deal with non-required segment information. When importing transactions, it will remove any segment that is not required by transaction entry.

A value of "1" enables this feature. Depending on the transaction type, the system updates any transaction lines to remove non-balancing segment and restriction segment information from lines where it is not required.

CLEANLINE_READ,1

The SUPPRESSWARNINGS statement tells the system not to include warnings in the import log; consequently, you would not be able to print the warnings either.

However, the system would display error messages that prevent the data from being imported.

SUPPRESSWARNINGS

If you include the SEGMENTNOTSTRING statement, the system reads the TETRANS_SEGMENT data as if it is in CSV format, using the field position. Otherwise, the system processes the file using the starting position and length.

SEGMENTNOTSTRING

If your DEF file is in CSV format and includes the SEGMENTNOTSTRING statement, it should be set up as follows:

TETRANS_SEGMENT_GL,10

TETRANS_SEGMENT_FUND,8

TETRANS_SEGMENT_GRANT,9

TETRANS_SEGMENT_PROGR,11

TETRANS_SEGMENT_DEPT,12

TETRANS_SEGMENT_117,13

If your file does not include SEGMENTNOTSTRING, it should be set up as follows:

TETRANS_SEGMENT_GL,10,,,1,5

TETRANS_SEGMENT_FUND,8,,,1,2

TETRANS_SEGMENT_GRANT,9,,,1,3

TETRANS_SEGMENT_PROGR,11,,,1,3

TETRANS_SEGMENT_DEPT,12,,,1,3

TETRANS_SEGMENT_117,13,,,1,1

Note: This statement only applies to CSV data files. Also, you must have previously created distribution codes using the Maintain>Distribution Codes form.

If you include the USEDISTCODE statement, the system uses both a general ledger code and a distribution code to complete the accounting transaction lines for each document line imported. You can include the general ledger and distribution codes in the data file or enter the default code in the definition file.

The distribution code should include all Fund, Balancing, Non-Balancing, and Restriction segment codes. In addition to including USEDISTCODE in the environment section of the definition file, the Field ID TETRANS_DISTCODE must be added to the transaction line context section. The Field ID should reference the distribution code in the data file or default to the distribution code to use for all transactions.

Formatting Statement Section of DEF

USEDISTCODE

Context ID Statement of DEF

TETRANS_DISTCODE

Note: This statement only applies to Maintain menu selections (such as vendors or customers) and timesheets; it does not apply to transactions.

If you include the UPDATEITEM statement, the system updates existing records with the data included in the data file. In other words, the field values in the existing record are replaced by the field values in the data file. (When importing timesheets the data in the data file appends, or is added to, the existing record.) Transactions continue to perform a replace import.

Note: When importing detail field data for records, such as Distribution Codes and 1099 Adjustments, it is recommended that you delete the master record and import the correct information, instead of updating the existing records using the UPDATEITEM statement. Header records can be imported using UPDATEITEM.

The UPDATEITEM statement is included after the data path and file name in the File Statement section of the definition file. The format of the statement is:

FILE,{Record Type},{path and filename of data file}, UPDATEITEM

To update timesheet records, for example, the File Statement would read:

FILE,TIMESHEET,C:\MIP Share\Import\CSV Samples\PRTS.csv, UPDATEITEM

Note: This statement does not apply to timesheets.

The UPDATEIGNOREBLANK statement tells the system to update fields in a master record with the contents of the .CSV (data file), except for cases where the data file contains a blank field. If the import encounters a blank field in the data file, the EXISTING field in the master record will not be replaced with a blank field. The currently existing value in the organization's database will be kept intact.

UPDATEIGNOREBLANK

Note: This statement only applies to the Timesheets menu selections; it does not apply to Maintain menu selections or transactions.

If you include the UPDATETS statement, the import updates the hours and/or rates/amounts on timesheets. When importing timesheets the data in the data file appends, or is added to, the existing record. For example: On Default timesheets, Earnings Hours can be appended and Distribution Codes can be modified, and new Codes can be added.

The UPDATETS statement is included after the data path and file name in the File Statement section of the definition file. The format of the statement is:

FILE,TIMESHEET,{path and filename of data file}, UPDATETS

Note: This statement only applies to Maintain menu selections (such as vendors or customers) and timesheets; it does not apply to transactions.

If you include the PREVALIDATE statement, the system tests the definition and data files without actually importing the data. If errors are detected, they are displayed in a message box. If there are no errors, the system displays a blank message. This statement should be on a separate line under the File Type statement.

PREVALIDATE