When defining a file definition within the definitions table (or in the header rows of an Excel spreadsheet), use the following parameters to correctly define the file type and process required.
It is important to note that whether the commands are in an EXCEL template, or in an import definition, they are ordered correctly. FleetWave will start the column mapping exercise and subsequently expect everything in the definition after this point to be a FleetWave column where it finds the first defined field name.
So in an EXCEL template ensure all your commands are listed in rows above the data header row. In an import definition, ensure that the POSITION column is numbered such that the commands are all above the first field name reference.
| COMMAND | OPTIONS |
|---|---|
| ALLZERODATESASBLANKS |
When set to YES, the import will treat eight zeroes as a NULL date "00000000" becomes "NULL" |
| CHARTYPE |
Identifies file type. Options are: UNICODE ANSI (default) 1252 / WINDOWS-1252 (use this for imports with accents in the data) |
| DATEFORMAT |
Identify the default format for dates in this file. Options are: DE dd/mm/yyyy DA mm/dd/yyyy DR yyyy-mm-dd DN dd-MMM-yy DS yyyymmdd (MMM=Jan,Feb, etc) |
| DELETEFILE |
0/NO, 1/YES/DELETE, 2/MOVE If (1/YES/DELETE) then import file will be deleted on completion (useful to prevent duplicate uploading) If (2/MOVE) then the import file will be moved to the 'Processed' subdirectory under 'Inbox' |
| DELIMITER |
Identify the character used to delimit columns in a CSV file (usually Comma, Pipe or Tab) NOTE: To identify a TAB delimiter, use a value of \t |
| EOFMARKER | Identifies the characters which mark the end of the transactions in the import table |
| EXECUTE |
SQL Commands to execute immediately. Can be used when importing a process-spreadsheet, not normally used on transaction imports. |
| EXECUTEBEFORE |
SQL Commands to execute before the import has taken place Typically you would use this command to perform some sort of preparation process |
| EXECUTEAFTER |
SQL Commands to execute after the import has taken place Typically you would use this command to perform some sort of tidying–up process, for example to set fuel volumes to litres where not specified: UPDATE FUEL_ISSUES_FW SET UNIT_OF_MEASURE_FW = 'L' WHERE UNIT_OF_MEASURE_FW=NULL |
| EXECUTEAFTERVERIFY |
SQL Commands to execute after the import has taken place AND the standard FleetWave verification routines have run Typically you would use this command to perform some sort of tidying–up process once you have the records in a validated state, including lined up records and other validations, for example to update a table database with the latest data from an import, once it is a known vehicle / driver |
| EXPORTWHERE |
A SQL WHERE CLAUSE to filter relevant records on an EXPORT definition. Use either this or EXPORTSQL on an export definition, not both. VEHICLE_STATUS_FW = 'LIVE' |
| EXPORTSQL |
A FULL SQL clause to filter relevant records on an EXPORT definition. Use either this or EXPORTWHERE on an export definition, not both. SELECT VEHICLES_FW.VEHICLE_ID_FW, MANUFACTURERS_FW.DESCRIPTION_FW AS [MAKE] FROM VEHICLES_FW JOIN MANUFACTURERS_FW ON MANUFACTURERS_FW.CODE_FW = VEHICLES_FW.MAKE_CODE_FW WHERE VEHICLES_FW.VEHICLE_STATUS_FW = 'LIVE' |
| FILE_NAME / FILENAME |
Target table within FleetWave, (default is FUEL_ISSUES_FW) Multiple tables can be specified, separated by a comma, to load the same data into different tables. This is useful for creating Header and Detail records at the same time. For example: JOB_HEADERS_FW,JOB_DETAILS_FW |
| FILETYPE | SDF or CSV |
| GROUPBY | When loading into multiple tables such as headers and details, this value can be set to the field to be used for grouping the header and detail records together. For example ORDER_NUMBER_FW will load all detail lines with the same order number under one header record. |
| HEADINGS |
When used on an export definition, the generated file contains a header with all column titles. Example: HEADINGS=1 |
| HIERARCHYFROM | At the end of an import many tables trigger verification processes that also set standard columns (%COSTCENTRE_FW%, %DEPOT_ID_FW%, etc) from the allocations table, by default the vehicle ID is used for this, adding HIERARCHYFROM=DRIVER would cause the driver ID to be used instead |
| HIERARCHYDATECOLUMN | Can be used in conjunction with HIERARCHYFROM and can be set to a valid date from the imported table which is then used to find a valid allocation record to load standard hierarchy columns (%COSTCENTRE_FW%, %DEPOT_ID_FW%, etc), HIERARCHYDATECOLUMN=TRANSACTION_DATE_FW would find allocations where TRANSACTION_DATE_FW is between the allocation start and end |
| IGNORE | Identifies the number of header rows to be ignored. If your import file has three lines of header text then set this parameter to 3. |
| KEYCODE | Identifies the fuel card system ID that relates to this import |
| KEYFIELD |
Identifies the field that contains a unique value, used with Overwrite 'YES' mode. For example to import data into the vehicle table you would include: KEYFIELD=VEHICLE_ID_FW |
| KEYFIELD2 | Identifies a second field that can be used when obtaining a unique value (ie. Keyfield could be VEHICLE_ID_FW and KEYFIELD2 could be DATE_FW), used with Overwrite 'YES' and 'REPLACE' modes |
| KEYFIELD3 | Identifies a third field that can be used when obtaining a unique value, used with Overwrite 'YES' and 'REPLACE' modes. |
| OVERWRITE |
NO/YES/ALL/UPDATE/REPLACE/INSERT YES - Overwrite matching records otherwise create new records ALL - Drop all records from FILE_NAME before importing NO - Always insert a new record, regardless of any matching records (This is the DEFAULT option) INSERT - Only insert a new record if no matching record found (never update a matching record) UPDATE - Update an existing record but never insert a new record REPLACE -Drop existing record before re-inserting |
| STAMPFILENAME | Suffix to append to processed filenames. Use YYYY, YY, MM, DD, HH, NN, SS placeholders and literals. (default is null) |
| TRANSACTIONFLAG | Identify the value that indicates a row contains a transaction (rather than a header, footer or summary record) |
| TRANSACTIONFLAGOFFSET | Identify the column position of the transaction flag (default is 1) |
| UPDATEWHERE |
If a record matches the KEYFIELDS it will only be updated if it also matches the UPDATEWHERE criteria. If a record is found that matches the KEYFIELDS but does not match the UPDATEWHERE, a new record will NOT be created |
| WORKSHEET | The number of the worksheet in the Excel file to be imported (default is 1) |
| XREFTABLE |
Identifies the source table when importing data by Cross-reference. This can be used to set a value in the target table to a value identified from a search against another table. When to use: If you need to set the Model record number when importing model options, but your data file only contains the model name, you would set: XREFTABLE=MODELS_FW XREFMATCH=MODEL_CODE_FW XREFRETURN=RECORD_NUMBER_FW XREFTARGET=MODEL_RECORD_NUMBER_FW You would then set the column heading of the data to XREFTARGET XREFMATCH Identifies the column to match against when performng the cross-reference lookup XREFRETURN Identifies the column value to return from the cross reference lookup XREFTARGET Identifies the column to write to (in the target table) |
| CULTURE |
Culture is used to define the language of the import. This is important when importing data in a language that may use commas instead of points/dots to act as a decimal point e.g. In German ‘10,20’ actually represents ‘10.20’. For this command to work you must use the culture code of the required country, so ‘CULTURE=de-DE’ would set the import for Germany. |