Data Import & Export: 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 created 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

1. Export sample data from a table to get a sample file of the table you want to update.

2. Visit the Data File page to check the data requirements for your upload file.

3. 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 created 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

1. Create a base file that contains the records you want. Below are some approaches to this
2. Export the entire table. (This is only suitable if the table is small).
3. Run a report from Contracts / Search or Quotes / Search
4. Run an existing scheduler job that contains the required fields
5. Run a select query in MySQL Workbench
6. unique_id(s) - locate the unique id for the table. This is usually at the start of the table.
7. Some tables require more than one id in the data file. Visit Table Data to check a list of known tables like this.
8. 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).
9. 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).
10. Update the file fields you want to change.
11. Save your file in CSV format
12. 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'

File

1. The contractid is the uniqueid here.

2. Only the unique_id and the two fields being updated are also in the file.

3. When the file has been imported, the Contracts / Insurance "Start Date" and "Policy Due Date" fields will have been updated for the selected contracts.

4. The selected contracts will also have audit records recording those changes.

5. No other fields in those contracts, or any other contracts will be updated by the import

Create new records

1. Create a base file that contains the records you want. Below are some approaches to this
2. Export sample data from a table
3. Run a select query in MySQL Workbench
4. unique_id(s) - locate the unique id for the table. This is usually at the start of the table. Leave this blank.
5. Some tables require more than one id in the data file. Visit Table Data to check a list of known tables like this.
6. If you try to add new unique id number, you will get the 'Record does not exist' error on the Error Report.
7. Locate the fields you want to update and remove all other fields from the file that are not needed.
8. Fields can be removed from the file if you want to use the system's default value populate into the field.
9. Update the file fields as required.
10. Save your file in CSV format
11. 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'

1. Create a data file for the table that contains contractid, insurancestart, insurance_renewal
2. Populate the file with the selected contracts and dates you want
3. When the file has been imported, the Contracts / Insurance "Start Date" and "Policy Due Date" fields will have been updated for the selected contracts.
4. The selected contracts will also have audit records recording those changes.
5. No other fields in those contracts, or any other contracts will be updated by the import

All fields

The financetypeid 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. E.g. To update roadside details in , you could have this header; contractid, roadsidemanagedflag, roadsidestart_date yes
• 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. E.g. for , 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. if present
• 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 it's Unique Key. Visit the Table Data page where known required fields are documented. if no Primary Key
• Date (Date) — Date fields must be in either 'YYYY-MM-DD' or 'DD/MM/YYYY' format. E.g. '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. e.g. blank, 01/13/2019 or 2022-12-35 are all bad entries. no

File requirements

1. The file must be in csv format. The maximum file size is 32MB (32768KB)
2. 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
3. The primary key;
4. Is a required field in all import files
5. Should be 'null' for new records
6. Should be populated for existing records
7. If the table contains unique keys, those fields are required in all import files
8. The file cannot contain blank rows
9. 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.
10. This can be achieved by converting the file to a .txt file.
11. Be sure to remove the line and save to .csv again prior to importing file.
12. The file you are uploading cannot be identical to one that has been imported previously
13. The file can contain all or only some of the fields that exist in the import table
14. The fields can be in any order

Data updates for new records

1. A primary key value is added to each new record.
2. Fields that are not in the import file will be created using the database default.
3. 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

1. Fields are only changed if they are in the import file and the value is different to the stored value
2. Audit records are only written if the import has changed the field's value

Table Data

Oveview

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 supplierid and cardnumber 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. cardid, supplierid, cardnumber
• fmclients clientid, client_code
• fmdriverevents drivereventid, driver_id
• fmworkcodes workcodeid, 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 tyreid, tyrecode

sp | Salary Packaging

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.
Eg. The following file names would yield the following results;

Note: The #timestamp# placeholder will only generate if "File Name Timestamp" is checked

File Name Examples

Flowchart

HTML zip

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 Get Quote) 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:

1. Novated leases – put in the packaging analysis you want, we can create the results on the quote template.
2. Other leases including Fully Maintained Operating, CHP, Chattel Mortgage - we recommend one template with budget items that switch off when not relevant.
3. 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

1. A record or report is selected and the ** button is available or,
2. 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

1. Click on Ctrl + J to open a download folder tab in Chrome or
2. 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.