Loading data from external files

This is a topic that has received almost complete documentation in the SDK. The usual scenario is that a file arrives from an external source; this may be an Excel file (xlsx or csv) or some other format. The file has to be converted into a tab delimited text file and placed in the system\load directory; Priority comes with a program that converts xlsx to tab delimited (EXL2TXT) so theoretically this should not be a problem. Both the file name and its source directory name have to be in English: Priority gets confused with Hebrew in the full file name. After the file has been converted, it has to be copied to the system\load directory where its name must be the same as the interface program that loads it. The interface program is usually invoked as a step in a procedure. So far, so good.

I have been working with a file that sometimes causes errors when loaded; as far as I can establish, this is due to there being quotation marks in one of the fields that causes fields in the tab delimited file not to align with the definitions in the interface. This causes an error message to be displayed, explaining the error (normally 'Conversion failed when converting character string to smalldatetime data type'); unfortunately this error message does not state on which line the error occurred. This obviously is not a problem when only a few lines are in the file, but is problematic when there are a few hundred lines.

Reading the SDK chapter again on load files, I came across the section that explains that the interface can be invoked with flags, one of which (-E) causes non-loaded lines to be exported to an error file. Exactly what I need. I couldn't figure out at first how to use this flag when the interface is invoked as a separate step, but I could use the alternative syntax of invoking the interface directly (EXECUTE DBLOAD '-L', <interface name>, '-E', <output file>. Whilst the interface was invoked, the error file was empty.

Today I had the time and lack of pressure to find examples of invoking an interface as a procedure step with flags; I found gold with the procedure BANKPAGE_AMEXCARD. The way one defines the flags is to define a parameter (let's call it E although the name itself is irrelevant) in position 10 (very important) whose value is '-E' and is of type CHAR. Following this, a second parameter is defined (named DBF, again not important) in position 11 (very important - it must follow the previous parameter) with value 'name of error file' (this will normally be stored in system\load), again of type CHAR.

I quickly wrote an interface for a very simple file consisting of one integer field and one date field. I then created such a file where the second line had the integer 16 in the date field - an obvious error. I wrote a procedure to empty the work space then invoke the interface. As expected, I received the error message but more importantly the error file contained the line that was not loaded by the interface. Exactly what I needed.

I will try and figure out why the bad lines in the real file did not get copied to the error file. But more importantly, this is a very useful technique that I will introduce to most, if not all, of my interface programs, especially those that receive external files (as opposed to those whose layout I defined).