Data Import & Export: IMPORT DATA

IMPORT DATA

The fleet import data option allows the user to import data to specific tables or groups of tables. Refer to each sub menu option for further details.

Whole Table

Red Book Data

Red Book Photos

Glass Data

Contributions

1Link Approvals

Insurance Data

Contract Budgets

Service Profiles

EXPORT DATA

Main Page > FLEET > EXPORT DATA

The export data options allow the user to export:

Whole Tables of records.

Report Data, specific pre-programmed reports of data.

Exports are generated in a .csv format which can easily be opened using applications such as Microsoft Excel.

A future enhancement is to give users the ability to edit and save their own Structured Query Language (SQL) scripts for commonly run downloads.

Import Table Data

Create or update table records in the system by importing data files.

Audit records are created for these records and they are marked as external (X).

Visit the Data File page for more information about how to prepare a file for importing.

This function can also be run using the Request a table data import) API.

This function can also be paired with a rules-based query used in the scheduler to update tables using the Method executeImportTableData.

Warning: 1. Importing data can disrupt tables in your system, so we recommend that only expert users use this feature.
2 Always test your upload files by uploading them to your Staging or Support first.
3.Contact your Account Manager for training on this feature.

Dependent Records

You can use a UUID placeholder in the import files to manage data where there are child records that have a key field dependency,
E.g. .
Visit the Dependent Records page for the details of how to use this feature.

Field Descriptions

Action buttons

• Browse — The browse button is displayed next to "Upload File" field once you have nominated the table you want to import. Navigate your directory to select the desired file for uploading. Once you have selected the table you want to import, the "Select" upload button is enabled. Press "Select" to confirm your table choice and proceed.
• Upload — The upload button is enabled when the table to upload has been selected and the "Upload File" has been selected. If the process validates and then moves on to Import. If validation fails, the alert message "Data validation has failed…" will appear and Error Report and Re-Upload buttons appear.
• Error Report — Error Report will enabled ,If validation fails, the alert message "Data validation has failed…" will appear and Error Report and Re-Upload buttons appear.
• Re-Upload — Re upload button will be enabled to correct all the errors based on the Validation File and re-upload the file.
• Import — The file validates and ready to import , If the validation is successful the alert message "Data validation has completed…"
• Next — Next button is enabled to import another file if the user needs , by click on the next button Upload button will enabled

Validations and Alerts

| Upload | | | Data validation has failed as follows: 0 record(s) found, 0 record(s) failed to validate Click 'Error Report' to get a complete list of errors or 'Re-Upload' to try again with a new file. | If the file uploads but the validation returns no records, there are two common problems you can check you don't have a header in the file. Add the required header. you may have uploaded an excel file by mistake. Check and make sure your file is a csv file and then Re-Upload. you have blank columns in your file. Go to the Error Report page for two methods you can use to find and remove the blanks. | | Data validation has failed as follows: 3 record(s) found, 3 record(s) failed to validate Click 'Error Report' to get a complete list of errors or
'Re-Upload' to try again with a new file. | The file will not import until the validation errors have been fixed. Click on the Error Report button to download the file that lists the errors that have been found. |
| Import timeout exceeded. Review timeout set in global control importtabledatatimeout | The upload and validation process is taking longer than the system will allow. The allowable time is set in ). This control governs both the Upload time and the Import time. The default setting is 200 seconds and the recommended maximum is 1,200 seconds. You can fix this problem by Increasing the value stored in ). Break your data into smaller files and try again. | | The data validation process has failed unexpectedly! Please try again shortly. If the problem persists contact your system administrator. | Possible causes The table does not have a primary key. Import Table Data cannot be used to update the table. Contact Catch-e Support if this is the case. || The uploaded file exceeds the uploadmaxfilesize directive in php.ini | The file size is larger than 256Mb. Break the file down to a size less than 256Mb || Import | || Import timeout exceeded. Review timeout set in global control importtabledatatimeout | The import process is taking longer than the system will allow. The allowable time is set in ). This control governs both the Upload time and the Import time. The default setting is 200 seconds and the recommended maximum is 1,200 seconds. You can fix this problem by Increasing the value stored in ). Break your data into smaller files and try again. |

) controls how long an Upload on an Import can run for before being stopped.
A timeout is required to prevent large uploads from straining system resources. If this timeout happens you will be alerted.
The default setting for the control is 200 seconds (3.33 minutes) and can be increased to up to 1200 seconds (20 minutes).

Tip: Temporarily extend this timeout to upload a large file as an exceptional event.
Upload large files out of hours or out of peak times of system use to avoid disruption to users.

Warning: Increasing this setting beyond 1200 is not recommended.
Doing so may impair the system response times and slow down users work.

Export Table Data

A sample or a complete data extract of the contents of the selected table can be exported to a CSV file.

If no records are stored in the table, the file will simply contain column headings. This can be used as a template for importing tables.

If the selected table contains encrypted fields, the columns containing encrypted data will be removed during the export process regardless of role.

This screen replaces an older variation of this feature located at Fleet / Export Data / Whole Table.

Field Descriptions

• Module (List) — Select the table you want to export.
• Table (List) — List of tables appears in the selected module

Output Options

• Include Field Headers (Check box) — This is checked by default.
• 5 Sample Rows Only (Check box) — Check this to only export 5 sample rows.
• Remove Null Values (Check box) — Check this to remove 'null' from the export file fields that are 'null'. Do this if you are exporting a file for presentation. If it is to be used as the basis for creating an import file, leave this checked, as 'null' fields must be imported with the 'null' values intact.

Action buttons

Validations and Alerts

Below are alerts that you may receive.

Import data to a table

Qualifiers

1. You are updating existing records or adding new records to an existing system table
2. We recommend first testing your import file in the Staging or Support environment

Import data to a table

1. Go to Setup / Import Table Data
2. Module - select the module that the data belongs to
3. Table - select the table to import the data to
4. Drop or browse to import the data file
5. Click on Upload
6. The uploaded file content will be validated.
7. If there are problems, the Error Report button will be enabled.
8. Download the Error Report, review, fix your file
9. Re-Upload
10. A file validation message is displayed
11. Click on **
12. Navigate to the system and check the data you have imported

Export data from a table

Qualifiers

1. You want to export all the records from a table to review what they all are
2. Use the data as a template to build a Data File so you can Import data to a table
3. File size limits to the export file process apply

In Live

1. Go to Setup / Export Table Data
2. Module - select the module that the data belongs to
3. Table - select the table to import the data to
4. Include Field Headers - leave checked
5. 5 Sample Rows Only - leave un-checked
6. Remove Null Values - leave un-checked
7. Click on **
8. Save your file

Export sample data from a table

Qualifiers

1. You want to export sample records from a table to use as a template to build a Data File so you can Import data to a table

In Live

1. Go to Setup / Export Table Data
2. Module - select the module that the data belongs to
3. Table - select the table to import the data to
4. Include Field Headers - leave checked
5. 5 Sample Rows Only - check
6. Remove Null Values - leave unchecked
7. Click on **
8. Save your file

Import Or Export Files

Administrators can update the Drivers User Guide, Quotes using html, Forms and Screen Reports by uploading new templates here. Driver user guides can be configured and customized for Channels

This is done by exporting and importing template files in the following formats: css, docx, pdf, html, xlsx and xml.

Your Account Manager will configure the file settings you need and help you build your first set of documents (if required). Once established, you can maintain your own documents.

If you cannot see a file you are using and want to change it, contact your Account Manager to configure it so that you can access it here.

Warning: We recommend that you;

Section Heading

• Import (Radio) — Select if you want to import a file
• Export (Radio) — Select if you want to export a file
• Platform (List) — Choose the platform the file relates to. This is normally 'Catch-e' (for this system), or 'Catch-e Driver Portal' for the Driver Portal. 'Catch-e' is the default entry.
• File Type (List) — Select the file type. Once selected, the "File Name" list will display all the available files. Only records with an active status will be shown. Files containing a *.php or *.phpo extension will be hidden.
• File Name (List) — Select the file you want to import or export. Only one file can be imported at a time. The name of the file being imported must match the "File Name" here exactly.

Import or Export File Options

The file name may contain an apostrophe, however please contact your Account Manager for additional development to display the report name in the drop box of the desired screen.

Hints and tricks for creating your import file

To create a template of a particular table, download the existing table using the Export Table Data function and remove the columns not required.

Dates must be in one of the following formats:

D/M/YYYY

D/MM/YYYY

DD/M/YYYY

DD/MM/YYYY

YYYY-MM-DD

0000/00/00 will also be accepted. This will result in the relevant screen date displaying as blank.

To import a NULL entry into a field, record the cell data as the word NULL in capitals.

Action buttons

• — An audit icon will appear when a particular file has been selected for Import or Export. The audit pop-up displays records for the file identifier (md5) and identifies the user who uploaded the file. Downloads are not recorded.
• Import — The import button is enabled when the radio button "Import" has been selected and the "Import File" has been selected.
• Export — The export button is enabled when the radio button "Import" has been selected.

Import Failed

• Bad setup! missing leading slash. Contact Catch-e Support — The paths contained in the table gbfiletypes must start with a leading slash "/" i.e. /clients/[client_name]/core/fleet/administration/quotes/templates.
• Bad setup! wrong client folder specified. Contact Catch-e Support — The paths contained in the table gbfiletypes must also contain the correct client name i.e. /clients/[client_name]/core/fleet/administration/quotes/templates.
• File must be called {file.ext} — The file name and extension must match the one selected on the screen. The name of the file being imported must match the "File Name" here exactly.
• File not found. Contact Catch-e Support. — You've clicked on the Export radio button and selected a file that is configured, but has never been loaded into Catch-e.
• Identical File! Last imported by {user_name} on {timestamp}. — A duplicate file cannot be imported. I.e. if the file is exactly the same, you will be alerted and the existing file will not be overwritten.

Import Successful

• File successfully imported — You'll see this if your import was successful.

Export Failed

• Bad setup! missing leading slash. Contact Catch-e Support — The paths contained in the table gbfiletypes must start with a leading slash "/" i.e. /clients/[client_name]/core/fleet/administration/quotes/templates.
• Bad setup! wrong client folder specified. Contact Catch-e Support — The paths contained in the table gbfiletypes must also contain the correct client name i.e. /clients/[client_name]/core/fleet/administration/quotes/templates.

Troubleshooting

Tip: Visit the main Troubleshooting page for a list of all the available problem-solving tips.

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.

Adding new file type records

1. Add a new record when required to set-up new files.
2. Check that the address is set-up correctly. Files will not import if this is malformed. They must be set-up to point to one of the two folders detailed below;
3. /clients/CLIENTNAME/../templates
4. /clients/CLIENTNAME/../attachments

Channels

Create a channel variation for aa file by created a new record for the channel and then the linked record.
The record must contain the correct in the directory path. E.g. /clients/CLIENTNAME/channels/CHANNEL_CODE/core/fleet/reports/attachments

Adding new file records

1. Add the required record that you want to use.
2. Set as ‘active’
3. The file can now be selected from the "File Name" drop-down list

Hiding file records

1. Hide files if you are no longer using them.

2. To stop a file from appearing in the "File Name" drop-down list, update to ‘inactive’.

3. Doing this will not prevent the files from being visible and available on the system screens. To removed them from user screens, the actual file must be deleted from the system folder. Contact your Account Manager to arrange this for you.

Legacy files

1. If you want to make changes to a file and cannot find it in the screen lists, you likely have a legacy file that is not stored in .
2. To enable changes, the old version must be removed from the application and replaced with a stored file here. Contact your Account Manager to arrange this for you.

Catch-e only files

Access to some files are restricted to Catch-e staff only. This is the case when is set to 'yes'. When this flag is set to 'no', 'admin' users can manage the file.

Useful Queries

select * from gb_files;start transaction;update gb_files set super_flag = 'no' where name = 'file_name_here.xlsx';commit;