Troubleshooting uploaded files

Edit online

As a system administrator, you can perform some basic checking to determine whether a file has failed to upload and why.

When an account or setup file is uploaded to the system, the files are available on the File Processed - Accounts & Setup page. To access the page, from the main menu, click Manage > File Processed - Accounts & Setup. By default, files that loaded in the last 24 hours are displayed on this page. You can select another time range, for example, Last 7 days.

Loading process

When a file is loaded, each row in the file is scanned by a connector in IBM® ESG Suite, which determines whether the row can be loaded. The connector checks whether the row is an empty row, is a column header, is flagged to be skipped, or is in a row format that is not recognizable by the connector.

The connector tries to load only those rows that are identified as valid rows, that is, rows in, which correspond to Records In. During the loading process, if a row is flagged as having load errors, it is not loaded.

The relationship between rows is defined as:

Total rows = Rows with process errors + skipped rows + rows in + rows with load errors + loaded rows

Where:

Process errors include column headers, empty rows, and invalid rows
Rows in are Records In
Rows out are Records Out

As a general rule of thumb, if a file has two or more process errors, or there are any number of load errors, you must troubleshoot the file.

Advanced troubleshooting steps involve the following:

Step 1 - Search for a file

When file processing starts, you can find the file on the File Processed - Accounts & Setup page. If the file is not listed, it was not processed by the server.

The file might not meet the file specification in terms of:

The file name
For an Excel file, the Tab name
The column headings
The order of the columns
The number of columns

Alternatively, your file might be in a queue waiting to be processed by the server. In this case, check this page later.

Step 2 - Check the status of the file

If a file is found on the File Processed - Accounts & Setup page, check the status in the File Status column. If the status is loaded, the file was processed and the data is loaded into the system. If an error is displayed in the File Status column, file loading has failed. Identify the error, fix it, and resubmit the file.

Possible reasons for errors include:

None of the records in the file have loaded. For example, a record might fail to load because an account style is invalid.
The format of one of the columns in invalid, for example, the date format is invalid.

Details of the errors are displayed on the Show Parse Errors and the Show Load Errors pages.

Note: Sometimes, a file might load and display a status of loaded but some of the records might have errors. Usually, the errors are minor, for example, related to the open or close date of the account, while the other records in the file loaded successfully.

In other cases, the entire file fails to load and Error is displayed in the File Status column. Critical errors must be fixed before you can resubmit the file for loading. If the whole file fails to load, review the parse errors by navigating to Load Errors > File Details > Parse Errors.

Step 3 - Check the number of records in and records out

Records in and out:

Records In: The number of valid rows in the file that are loaded for processing. The number excludes rows to be skipped, rows that do not conform to the specification, the column header row, or empty rows.
Records Out - The number of new records that are loaded into the system.

Generally, the number of records in is the same as the number of records out. For example, if your file contains 10 electricity bills, 10 records are loaded. However, sometimes records out is less than records in. Possible reasons are:

A multi-read connector is used, which means several rows of records are combined into one record and then loaded.
The connector is unable to find the location information for a record in the file.
The connector is unable to find the account information for a record in the file.
For system configuration, an update operation is performed rather than a create operation, which means that 0 new records are added.

Step 4 - Check the file details

You can check for additional details about the file loading from the preview panel. Details include:

Total rows
Skipped rows
Load errors
Loaded rows
Result

For setup files, the Result field shows additional diagnostic information the configuration. For example, the number of new accounts that are created is shown.

The File Details section is only available for files that are loaded in the last 48 hours.

After you complete basic troubleshooting of the file, you have a good understanding of the file loading status. If you still have issues with the file, you can complete more comprehensive troubleshooting.

Advanced troubleshooting - Step 1 - For account data, check compilation status

When you load account data, account statistics are generated after the file is loaded. Generating statistics is a separate process from file loading. Check the Compilation Status column to see the status of statistics processing.

When an account file loads successfully, the compilation states are similar to the following:

n/a, the file is still being physically loaded
0%; Estimated to start in 5 minute(s)
65%
100%

If an error occurs during the compilation stage, the compilation states are similar to the following:

n/a the file is still being physically loaded
0%; Estimated to start in 5 minute(s)
65%; 53 Success, 1 Fail
100%: 87 Success 1 Fail

Note: The main purpose of displaying the compilation status is for clients who want to check whether the data they have recently loaded is available for reporting. Therefore, the compilation status is only retained for 72 hours after which this status will change to n/a.

If other files that require compilation are ahead of the current file, an estimated compilation start time is provided for the file. The estimation is based on the assumption that one account typically requires one second of time to compile statistics for the data.

Compilation errors are also available in the preview panel. Although errors during the statistics compilation state are rare, they might occur when data is corrupted.

Advanced troubleshooting - Step 2 - Check parse errors

Files are scanned by the connector before they are loaded and errors are captured in the Parse Errors page. On the File Processed - Accounts & Setup page, select a file and from the pop-up menu, click Show Parse Errors.

Some of the possible reasons for parse errors include:

A record row does not match the specification for a row.
Invalid date format found in date fields.
Empty rows were found.
Some rows were skipped, in accordance with the specification.
Column header row was found.
A summary line with the total number of rows was found in the file.

For parse and load errors, a maximum of 10 errors of the same type are shown on the page. The total number of errors of the same type is displayed in each message, for example, 8 of 24 errors of this type.

Advanced troubleshooting - Step 3 - Check load errors

After files are scanned and valid records are identified, the file is loaded. Errors are captured in the Load Errors page. On the File Processed - Accounts & Setup page, select a file and from the pop-up menu, click Show Load Errors.

Some of the possible reasons for load errors include:

Loading records into a closed account.
Including non-numeric values in the consumption or cost fields.
The location that is associated with the record was not found.
The account that is associated with the record was not found.

Fix the errors found and resubmit the file for loading, if required.

Different connectors have different file loading behaviors. If a file is loaded but there is a mismatch between records in and records out, the file might still be valid for the connector, provided there are no unusual parse errors, loading errors, or errors in the Result column on the File Details page.