What's in this page?
File Format
High-Level File Structure
Header Row Details
Data Row Details
Supported Fields and Special Rules
Available Header Import Fields
Fields that will be populated but not imported
Fields that will not be imported
File Format
The input data file should be a CSV-formatted text file. A comma (“,”) will be used to separate each column. A carriage-return+line-feed character combination will be used to terminate each row. If a carriage-return+line-feed character combination must be included as part of the data of a single column, the column’s data should be enclosed in double-quotes. The same is true if a comma must be included as part of the data of a column. If a double-quote character must be included as part of the data of a column, then it should be “escaped” by being immediately proceeded by another double-quote character. (e.g. "Hello world" would be encoded as ""Hello World"" if the double-quotes were required as part of the value for a field.)
High-Level File Structure
The file will consist of one header row followed by one or more data rows. The header rows explicitly state which fields will be populated (and, by inference, which tables). The header row will include fields from two tables. The row does not need to list all the fields in a table, nor does it need to reference each available table; it should only list as many fields as will be imported. The order in which it lists these fields will dictate the order in which the data will appear in the subsequent data rows.
Immediately following the header row there should be one or more data rows. Each data row shall contain the necessary command, key and data values needed to import one row of data into the database table(s). There must be one value column for each field-definition column declared in the header (though the columns can be left blank when appropriate).
Header Row Details
The first non-blank row in the import file shall be interpreted as the header row. The header row shall be used to define which fields will be imported (as such, they are referred to as field-definition columns). The first column of the header row needs to specify what data will be used to identify the assignment. This can be the Assignment Number or any of the five From or To Employee Identifiers on the Assignment screen.
The second column of the header row is the first “field-definition” column. Each field to be imported shall occupy one column. There should be at least one such column, and there is no upper limit to the number of columns. Every such column should be unique within the header row (no duplicate columns).
The format for each field-definition column is <table name>!<field name>. For example, in order to populate the CAR_YEAR field of a Car Lease record, the field-definition column should say “CAR_LEASE!CAR_YEAR”.
Data Row Details
Following the header row are one or more data rows. Each data row shall contain the Assignment Identifier in addition to one or more data columns. The data columns much exactly match the layout defined in the header row.
Supported Fields and Special Rules
The tables that can be inserted using this import are: CAR_LEASE, CAR_LEASE_CF. Generally speaking, the import can populate any field in any of these tables other than the ID field and any foreign key fields that link these tables to each other. For lookup-type fields (any field that appears as a drop-down list on the screen) the value given to the import is the full text of the value the user sees on the screen (NOT the ID or code that is looked up and stored in the database). A field that appears as a checkbox on the screen should be imported with a value of “Yes” if the box should be checked, and “No” otherwise. Also, very few fields are required apart from the fields used to identify a record.
Available Header Import Fields
Available Assignment Identifier Field Names
|
Available Assignment Identifier Field Names
|
Available Assignment Identifier Field Names
|
Available Assignment Identifier Field Names
|
ASSIGNMENT!FROM_EMPLOYEE_ID
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
From Employee HR ID
|
ASSIGNMENT!FROM_NATIONAL_ID_NUMBER
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
From Employee Tax ID
|
ASSIGNMENT!FROM_NATIONAL_TAX_ID
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
From National Tax ID
|
ASSIGNMENT!FROM_PAYROLL_ID
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
From Payroll ID
|
ASSIGNMENT!FROM_SOCIAL_INSURANCE_ID
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
From Social Insurance ID
|
ASSIGNMENT!TO_EMPLOYEE_ID
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
To Employee HR ID
|
ASSIGNMENT!TO_NATIONAL_ID_NUMBER
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
To Employee Tax ID
|
ASSIGNMENT!TO_NATIONAL_TAX_ID
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
To National Tax ID
|
ASSIGNMENT!TO_PAYROLL_ID
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
To Payroll ID
|
ASSIGNMENT!TO_SOCIAL_INSURANCE_ID
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
To Social Insurance ID
|
ASSIGNMENT!NUMBER
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Select One from this grouping to identify the assignment the car lease data should be imported for.
|
Assignment Number
|
|
|
|
|
Field Name
|
Format
|
Length
|
Field Rule(s) / Notes
|
CAR_LEASE!AUTHORIZED_DRIVERS
|
nvarchar
|
255
|
|
CAR_LEASE!CAR_MAKE
|
nvarchar
|
50
|
|
CAR_LEASE!CAR_MODEL
|
nvarchar
|
50
|
|
CAR_LEASE!CAR_YEAR
|
nvarchar
|
50
|
|
CAR_LEASE!CONTACT_EMAIL
|
nvarchar
|
100
|
|
CAR_LEASE!CONTACT_FAX
|
nvarchar
|
50
|
|
CAR_LEASE!CONTACT_NAME
|
nvarchar
|
50
|
|
CAR_LEASE!CONTACT_PHONE
|
nvarchar
|
50
|
|
CAR_LEASE!DAMAGE_ACTUAL_PAYMENT_AMOUNT
|
money
|
8
|
|
CAR_LEASE!DAMAGE_ACTUAL_PAYMENT_CURRENCY
|
nchar
|
3
|
Supply the three-character currency code.
|
CAR_LEASE!DAMAGE_ASSESSED_AMOUNT
|
money
|
8
|
|
CAR_LEASE!DAMAGE_ASSESSED_CURRENCY
|
nchar
|
3
|
Supply the three-character currency code.
|
CAR_LEASE!DAMAGE_ASSESSED_TO_EMPLOYEE
|
nvarchar
|
50
|
Supply the value in the DISPLAY_VALUE column of the CLASSIFICATION table for group code EQBOOL
|
CAR_LEASE!DAMAGE_COLLECTED_THROUGH
|
nvarchar
|
50
|
Supply the value in the DISPLAY_VALUE column of the CLASSIFICATION table for group code EQCOLT
|
CAR_LEASE!DAMAGE_EMPLOYEE_PAID_AMOUNT
|
money
|
8
|
|
CAR_LEASE!DAMAGE_EMPLOYEE_PAID_CURRENCY
|
nchar
|
3
|
Supply the three-character currency code.
|
CAR_LEASE!DAMAGE_PAID_DATE
|
datetime
|
8
|
|
CAR_LEASE!DELIVERY_DATE
|
datetime
|
8
|
|
CAR_LEASE!ENGINE_SIZE
|
nvarchar
|
50
|
|
CAR_LEASE!FINAL_PAYMENT_AMOUNT
|
money
|
8
|
|
CAR_LEASE!FINAL_PAYMENT_CURRENCY
|
nchar
|
3
|
|
CAR_LEASE!LEASE_AMOUNT
|
money
|
8
|
|
CAR_LEASE!LEASE_CURRENCY
|
nchar
|
3
|
|
CAR_LEASE!LEASE_END_DATE
|
datetime
|
8
|
|
CAR_LEASE!LEASE_PER
|
nvarchar
|
50
|
Supply the value in the DISPLAY_VALUE column of the CLASSIFICATION table for group code EQTMFM
|
CAR_LEASE!LEASE_START_DATE
|
datetime
|
8
|
|
CAR_LEASE!LICENSE_TAG_NUMBER
|
nvarchar
|
50
|
|
CAR_LEASE!LOCKED
|
nvarchar
|
50
|
Supply the value in the DISPLAY_VALUE column of the CLASSIFICATION table for group code EQBOOL
|
CAR_LEASE!LOCKED_SOURCE
|
nchar
|
6
|
|
CAR_LEASE!LOCKED_DATE
|
datetime
|
8
|
|
CAR_LEASE!LOCKED_BY
|
nvarchar
|
50
|
|
CAR_LEASE!NAME_ON_LEASE
|
nvarchar
|
50
|
Supply the value in the DISPLAY_VALUE column of the CLASSIFICATION table for group code EQLSBY
|
CAR_LEASE!REFUNDABLE_DEPOSIT_AMOUNT
|
money
|
8
|
|
CAR_LEASE!REFUNDABLE_DEPOSIT_CURRENCY
|
nchar
|
3
|
Supply the three-character currency code.
|
CAR_LEASE!USED_FOR
|
nvarchar
|
50
|
Supply the value in the DISPLAY_VALUE column of the CLASSIFICATION table for group code EQCUFR
|
CAR_LEASE!VIN
|
nvarchar
|
50
|
|
Fields that will be populated but not imported
Field Name
|
Format
|
Length
|
Field Rule(s)
|
CAR_LEASE!ID
|
int
|
4
|
|
CAR_LEASE!ASSIGNMENT_ID
|
int
|
4
|
|
CAR_LEASE!IMPORT_HISTORY_ID
|
int
|
4
|
|
CAR_LEASE!CREATE_DATE
|
datetime
|
8
|
|
CAR_LEASE!CREATE_BY
|
nvarchar
|
50
|
|
CAR_LEASE!LAST_UPDATE_DATE
|
datetime
|
8
|
|
CAR_LEASE!LAST_UPDATE_BY
|
nvarchar
|
50
|
|
Fields that will not be imported
Field Name
|
Format
|
Length
|
Field Rule(s)
|
CAR_LEASE!SERVICE_ORDER_ID
|
int
|
4
|
|
CAR_LEASE!VENDOR_ID
|
int
|
4
|
|