Import Or Export Files Troubleshooting
Import Or Export Files Troubleshooting
I Can't See the File I Need
Some import or export files are marked as only editable by Catch-e staff.
If you cannot see the file you want to edit, contact Catch-e Support to update the access settings for the file.
Data File
You can create new records or update existing records in a table with the data file you upload.
When you build the data file for your import, the file doesn't need to contain all of the fields that exist in the table:
if changing existing records in a table, only add the fields you are changing to the file
if creating new records, only put in the fields you had information for, other fields will be filled with the database's default values
If you are using executeImportTableData, the query you create needs to conform to the Data File requirements or the job will fail.
Recommended Steps
The steps below are useful when building a new data file:
Export sample data from a table to get a sample file of the table you want to update
Visit the Data File page to check the data requirements for your upload file
Visit the Table Data page where known required fields have been documented
When you build the data file for your import, the file doesn't need to contain all of the fields that exist in the table:
if changing existing records in a table, only add the fields you are changing to the file
if creating new records, only put in the fields you had information for, other fields will be filled with the database's default values
Update Existing Records
Create a base file that contains the records you want. Below are some approaches to this:
Export the entire table. (This is only suitable if the table is small)
Run a report from Contracts / Search or Quotes / Search
Run an existing scheduler job that contains the required fields
Run a select query in MySQL Workbench
unique_id(s) - locate the unique id for the table. This is usually at the start of the table. Some tables require more than one id in the data file. Visit Table Data to check a list of known tables like this
If there are records that you are not going to update in the file, find these and delete these rows. (This reduces the risk of updating other records by mistake)
Locate the fields you want to update and remove all other fields from the file that are not needed. (This reduces the risk of updating other fields by mistake)
Update the file fields you want to change
Save your file in CSV format
Import data to a table
Example Update
You want to update the Contracts / Insurance "Start Date" and "Policy Due Date" for a selected list of contracts.
Module: 'fm | Fleet Management'
Table: 'fm_contracts'
The contract_id is the unique_id here
Only the unique_id and the two fields being updated are also in the file
When the file has been imported, the Contracts / Insurance "Start Date" and "Policy Due Date" fields will have been updated for the selected contracts
The selected contracts will also have audit records recording those changes
No other fields in those contracts, or any other contracts will be updated by the import
Create New Records
Create a base file that contains the records you want. Below are some approaches to this:
Export sample data from a table
Run a select query in MySQL Workbench
unique_id(s) - locate the unique id for the table. This is usually at the start of the table. Leave this blank. Some tables require more than one id in the data file. Visit Table Data to check a list of known tables like this. If you try to add new unique id number, you will get the 'Record does not exist' error on the Error Report
Locate the fields you want to update and remove all other fields from the file that are not needed. Fields can be removed from the file if you want to use the system's default value populate into the field
Update the file fields as required
Save your file in CSV format
Import data to a table
Example Create
You want to add new records to the table for selection in the Quotes / Finance "Finance Types" field.
Module: 'qt | Quotes'
Table: 'qtfinancetypes'
Create a data file for the table that contains contract_id, insurance_start, insurance_renewal
Populate the file with the selected contracts and dates you want
When the file has been imported, the Contracts / Insurance "Start Date" and "Policy Due Date" fields will have been updated for the selected contracts
The selected contracts will also have audit records recording those changes
No other fields in those contracts, or any other contracts will be updated by the import
All Fields
The finance_type_id is blank here as these are new records. The system will allocate the ids automatically. The new records will have audit records recording their creation.
Some Fields
The status_flag field is missing, but will be populated automatically as 'active' as this is the system's default setting for this field.
Preparing Your Import File
Header Row (Text)
All files must contain a header row that lists each field that is being created or updated. Example: To update roadside details, you could have this header: contract_id, roadside_managed_flag, roadside_start_date
Primary Key (Num)
If the table has a Primary Key, the file must contain a column for this field. This is the field that uniquely identifies each record. Example: for fm_contracts, it would be contract_id.
When updating existing records, the Primary Key column must be populated
When creating new records, the Primary Key column must still be present. It's possible to upload the unique id as a blank field, null, or NULL. We recommend you populate this field with the value 'null'
Be careful to make sure you do not populate this column. This may result in over-writing existing records (if the key value already exists), or potentially creating future data corruptions
During the import process, the table is checked and corrected if it is out of synch before the upload is done. This ensures that new records are given the correct next id
Unique Key (Various)
Some tables do not have a Primary Key. In this case, the file needs to contain all of the fields that make up its Unique Key. Visit the Table Data page where known required fields are documented.
Date (Date)
Date fields must be in either 'YYYY-MM-DD' or 'DD/MM/YYYY' format. Examples: '2019-12-24' or '24/12/2019'.
If you want to remove an existing date, the entry can be '00/00/0000', '0000-00-00', or null or NULL
If the field is blank, or the date is invalid, the upload will fail and you will get an error. Bad entries include: blank, 01/13/2019, or 2022-12-35
File Requirements
The file must be in csv format. The maximum file size is 32MB (32768KB)
The file must have a header row that contains field names from the import table and the field names must exactly match field names of the import table
The primary key is a required field in all import files. It should be 'null' for new records and should be populated for existing records
If the table contains unique keys, those fields are required in all import files
The file cannot contain blank rows
If you are importing data using a file you have exported using the Export Table Data menu, check to see no extraneous line has been added to the end of the file. This can be achieved by converting the file to a .txt file. Be sure to remove the line and save to .csv again prior to importing file
The file you are uploading cannot be identical to one that has been imported previously
The file can contain all or only some of the fields that exist in the import table
The fields can be in any order
Data Updates for New Records
A primary key value is added to each new record
Fields that are not in the import file will be created using the database default
Audit records are written for all fields in the table, even if the field was not in the import file
Data Updates for Existing Records
Fields are only changed if they are in the import file and the value is different to the stored value
Audit records are only written if the import has changed the field's value
Table Data
Overview
Some tables do not have a Primary Key and in this case, updates needs to contain all of the fields that make up the Unique Key.
Imported data files and Insert or update a record with auditing API calls will fail if those fields are missing.
Below is a list of tables with a known requirement for a Unique Key.
fm | Fleet Management
fmcards - If you are importing card records in bulk, you cannot import a card record if there is already a record for the supplier_id and card_number combination. If you see the following error in your Error Report: (23000 - 1062 - Duplicate entry 'cellvalue-cellvalue' for key 'column'), do the following: Lookup the Card Number in your records and update as required. Remove the reported record from your upload file. Required fields: card_id, supplier_id, card_number
fmclients - Required fields: client_id, client_code
fmdriverevents - Required fields: driver_event_id, driver_id
fmworkcodes - Required fields: work_code_id, work_code
qt | Quotes
qttyres - You cannot have multiple instances of the same "tyrecode" in the qttyres table. If a change is needed to an existing "tyrecode" the current record can only be updated using this feature. Required fields: tyre_id, tyrecode
sp | Salary Packaging
spemployeepackage_items - Required fields: employee_package_item_id, contract_id
File Name Timestamp
Check to append the file creation timestamp to the file name to make it unique. Add the #timestamp# placeholder to your file name to control the location of the timestamp.
File Name Examples
Quote Templates
Overview
Our recommended approach to setting up your quotes is to create templates using HTML mark-up and our standard set of placeholders. The output is then rendered into PDF files for viewing or emailing. To deliver this, we can either manage the design process for you, or if you have a preferred web-designer, you can do this yourselves. The attached design guidelines provide detail of how your quote templates should be assembled and managed in Catch-e. Please read this carefully.
An alternative approach is to deliver Web Quotes. This generates a URL link in the Quote email. The quote is then rendered in a browser using our GetQuote web service in conjunction with your own web-page design.
See also the Upload a new quote template process page.
Template Maintenance
Once in place, you can update templates in Setup / Import/Export Files. We recommend you update your TEST or STAGING environment first and review it carefully. Also, maintain a version control of your files as the system only retains the most recent one.
Types of Quote Template
Usually, you need to set up different quote templates for different types of Contract. Here are some suggestions:
Novated leases - put in the packaging analysis you want, we can create the results on the quote template
Other leases including Fully Maintained Operating, CHP, Chattel Mortgage - we recommend one template with budget items that switch off when not relevant
Vehicle purchase only
Quote templates can also be set up for different Channels.
Sample Quote Template
Use this sample quote template as a starting point for designing your own templates. Quote templates can be portrait or landscape.
The Catch-e application supports templates written in HTML. If you wish engage a third party web designer or wish to undertake this process in-house, Catch-e will provide design guidelines for you to follow.
Once we have a sample of the quote template you want, Catch-e further develops the template based on your design.
Troubleshooting
Tip: Visit the main Troubleshooting page for a list of all the available problem-solving tips.
Catch-e Setup
Contact your Account Manager for further assistance with this feature as changes need to be made to the table gb_files in order for you to update the quote template from the user screen Setup / Import/Export Files.
Also refer to Quote Template Setup.
Download
Files can be downloaded when:
A record or report is selected and the Download button is available, or
The file icon is shown
Click on Print or the Icon to start the file download.
Browsers
Your browser determines how the downloads are presented to you.
Edge
When a download is initiated, a download bubble is shown in the header of your browser window.
Click on this to see recent downloads and pick the file you want to open.
Visit the Operating System and Browser settings page to check and update your download settings for Edge.
Chrome
The file is saved into your download folder.
Click on Ctrl+J to open a download folder tab in Chrome, or
Keep Windows Explorer open in the background and check the Download folder for your file
Visit the Operating System and Browser settings page to check and update your download settings for Chrome.
Troubleshooting
Tip: Visit the main Troubleshooting page for a list of all the available problem-solving tips.