Using load files from multiple different systems and platforms can often be confusing. There are several reasons by a load file may be failing to ingest properly into ESI Analyst.
Some of the most frequent issues we see with improperly formatted load files are:
Missing column headers & empty columns
Improperly formatted delimiters
Improperly formatted date fields
Backslashes in column values
Missing Column Headers & Empty Columns
When creating a load file so that you may import metadata into ESI Analyst, be sure that all of the header rows have the field names you need and that there are no empty field titles or empty columns. Below is an example of an empty column when opening the file in a spreadsheet program. Notice the empty column value in the first image.
Without a column header, the import mechanism fails to recognize the column and this causes the import engine to have a mismatch between values and mapped columns. When this occurs, the row is rejected. If the value is empty, but the column header is present, if the field is not a required field, then the column will be accepted.
Improperly Formatted Delimiters
Currently ESI Analyst accepts the following delimiters:
DC4 (\x14 - standard Concordance style delimiter)
Note that if you select the "\x14" delimiter (DC4 Concordance Delimiter), the quoted text identifier is assumed to be the "þ" character (thorn) , which are standard Concordance delimiters used in most DAT files. All other delimiters assume double quotes (") as the quoted text identifier.
If the column being parsed contains quotes and your delimiter expects quotation marks as the quoted text identifier, you will need to escape those quotes using a backslash "\" character.
Improperly Formatted Date Fields
There are many ways to format a date and they can often cause confusion! ESI Analyst can accept a wide variety of date time formats. Here are the two basic formats most widely encountered:
When formatting dates, if your date value separator is a hyphen "-" your date value's first digits will be interpreted as the year. If your date value separator is a forward slash "/" your date value's first digits will be interpreted as the month. (https://en.wikipedia.org/wiki/Date_format_by_country)
If you are using Microsoft's Excel to format your load file beware that Excel likes to drop the seconds off of the timestamp! Unless you modify your date time format directly in Excel this can be problematic. To prevent Excel from munging dates, you can use a "T" value between the date and time values (e.g. "yyyy-mm-ddThh:mm:ss" - in Excel you must select "custom" format an input this as the pattern)
When providing your date time stamp in a load file, it is important to separate your timezone value from the date time stamp. This allows ESI Analyst to employ the proper offset methods to the original date time value to provide review in UTC or your user's profile timezone setting. See our article about acceptable timezone formats for further information.