Import Data Redbook Photos

This menu option is used to import the monthly Red book photograph updates. The updates are provided by Red Book via an ftp site in a zipped file. This menu option allows the user to import the zipped file from their local (or network) location after downloading it from the ftp site and to then validate the data and update the database with the additional photographs.

Scheduler

RedBook data is usually processed by using scheduled jobs to import data from their FTP site.

Filename

The importVehicleData files are stored on the FTP site in a zip file with a static name that is nominated in the scheduler job.

This zip file contains both the data files updated here, and also the data files that are updated in the Redbook Data screen.

Photo Files

Import Data Whole Table

Deprecated: This screen has been replaced by a newer variation of this feature located at Setup / Import Table Data.

You can import whole tables of data using this screen. These imports are typically performed during a system set-up.

Warning: Importing data can disrupt tables in your system. Because of this we recommend that only expert users who have received training from Catch-e use this feature.

Workflow

The import process uses the following steps:

1. Select Table - Drop down list of available tables

2. Upload File - Select the file using the browse feature

3. Validate File - The system will check the number of fields in the file and accept or reject

4. Import File - The system will import the file

5. Repeat - Another file may be selected for importing

Field Descriptions

Action Buttons

Attaching Files

This screen will only accept one file at a time as they are process-driven screens.

Click on the Attach to get more details about how to attach a file.

Upload Business Rules

1. The file must be in csv format. Maximum file size is 32MB (32768KB).

2. The number of columns in the file must match the selected system table.

3. Any column heading row(s) will be ignored. A row is considered to contain column headings if the text in the first column (ignoring case) equals the name of the first field in the table exactly.

   Note: if the first column heading does not match the field name exactly the system will import the row, potentially with a zero id.

4. Zip files are supported. The file size when unzipped should not exceed 95MB (97280KB). This effectively allows up to 3 times larger files to be imported.

5. Example importing into table fm_client_events:

   "client_event_id","client_id","client_event_code_id","event_date","description",... 100000,100222,100002,17/01/2008,"Need to arrange formal training for Client Events.",... 100001,100222,100002,21/01/2008,"Need to arrange formal training for Client Events.",... 100002,100222,100004,23/01/2008,"Need to arrange formal training for Client Events.",... "client_event_id","client_id","client_event_code_id","event_date","description",... 100003,100222,100001,27/01/2008,"Need to arrange formal training for Client Events.",... 

   Four rows of data will be imported. The first and fifth rows will be ignored as the first field in table 'fm_client_events' is 'client_event_id'.

6. For tables appearing in the gbuniqueids table the system will automatically allocate the uniqueid if the imported file has '0', 0, or ' ' in the uniqueid column. Anything else will be assumed to be the unique_id.

   Note 1: For new records, we recommend leaving the ID column blank and letting the system perform the ID allocation. Most tables begin the sequence at 100000.

   Note 2: Care should be taken if loading existing records with existing ID's, particularly if transferring from one environment to another as the ID sequence may be different.

7. Each file that is successfully imported will also be stored with a log record written into gb_imports. This records the user, file md5 and timestamp.

8. Fields that have allowances (for example: default values, 'null' or '0' definitions in the schema, blank, etc.) where left blank in the import file these will populate based on these allowances.

   For example, a field has a default entry of 'email'. Where this field is empty in the import file, on upload it will enter the default value into the respective field.

9. You may see a system error on the bottom left of the screen. For example: File does not contain the correct number of fields!

   If this happens, you will need to review the file and try again.

Hints and Tricks for Creating Your File

To create a template of a particular table, download the existing table using the Export Data function.

Date Formats

Dates must be in one of the following formats:

• D/M/YYYY

• D/MM/YYYY

• DD/M/YYYY

• DD/MM/YYYY

• YYYY-MM-DD

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

NULL Entries

To import a NULL entry into a field, record the cell data as the word NULL in capitals surrounded by square brackets, for example: [NULL]

Encrypted Fields

Note: the Whole Table Import does not automatically encrypt data for encrypted fields such as BSB and Bank account numbers. If you need to upload to these fields contact Catch-e support to update the data in an encrypted format.

File Validation and Troubleshooting

When you validate a file you are importing and there is a data error, you will see a message on the bottom left side of the screen.

If the error is "Error data contains scientific notation" (for example: 7.07E+15) it is likely that you have saved a file in excel and the data has been corrupted.