AFSDataImporter Utility

The Data Importer reads an ASCII file of data records and inserts them into the afstrade database.  (See Release Notes for updating and deleting records) This application requires the afstrade.ini file to determine to which database records are saved. 

Text is parsed as SDF formatted text. The file read has the following format:

• Line 1: Contains a single string, the name of the table into which the data is to be loaded.
• Line 2: Contains a comma delimited list of column names, into which data is to be loaded. The list does not require all the columns in the table, only those columns which are imported. The column order does not need to match the column order specified in the database table. The list must contain mandatory columns. 
• Line 3..x: Each line contains a comma delimited list of values for a row to be imported into the database. The position of each item in the list must correspond to the item of its corresponding column in Line 2.

For example

dbo.fund
fund_code,fund_name,is_edit_active
ABC,"ABC Fund",0
XYZ,"XYZ Fund",0

Running the Application

After double clicking on the AFSDataImporter, the main form is displayed.  To process a file of data, click on the Browse... button and select the file to process.  The file is read and the Total number of items is displayed.  If you do not want duplicate (i.e. existing) records to overwrite records already in the database, click the Auto Update checkbox off, otherwise click it on.  

Click on the Process button and the records in the file are saved to the afs:TRADE database.  Once started, you can click on the Cancel button to stop processing, but the process will not actually be stopped until after the current record is processed.  After you process a set of records, you may click Browse... again to select another file.  If you want to re-process the current file, and that file has changed, you need to select it through the Browse... button again.

While processing, status messages, indicating errors, are displayed in the scrolling text area.  These messages are also stored in a log file in c:\temp or in the directory pointed to by the LogFileDirectory item in the [Afstrade] section of afstrade.ini.   The name of the log file is of the form AFSDataImporter_yymmdd_hhmmss.log.   A corresponding .txt file is also created containing just the lines that failed, so one could edit this file and then reprocess it as is.

In addition to the standard AFS command line parameters, the following are valid command line parameters

Notes

• The database design from SDesigner provides the complete list of tables and available column names for each table.
• This utility is for initial data loading only, and will not support the loading of data during production.
• Mandatory column checking is not performed.
• Data validation is not performed except for the following: Data value must already exist in corresponding database table for columns with foreign key constraints on other database tables. (Example: specific industry code must exist in industry table in order for a product record containing that industry code to be inserted into the product table.)
• The importer skips duplicate records and prints them to the report file which states that the records were not inserted.

Release Notes

v2001.06.08

• Add Auto Update checkbox.  If on existing records are overwritten with new record.  If off, update only occurs is should_update exists and is set to 1.

v2001.05.31

• Fix problem where last character in data row was a comma.  Was skipping record and displaying error "Incorrect Number of Fields, or Blank Data Record."

v2001.04.30

• Line 2, the list of column names, can now include should_update and/or should_delete, to indicate if the record referenced by a row should be updated or deleted.  The record is updated if should_update = 1.  The record is deleted if should_delete = 1.  If both values are set to 1, an exception is raised.  The Data Importer uses a single primary key (the first column in a table) to determine which record to update/delete.