Importing data into the central database from outside sources is a valuable capability of the system. For example, users may want to feed price updates or new items into the system from external sources and have transactions sent to the affected sites to make the changes there. This document discusses the requirements for this functionality.
The import file is a comma-delimited ASCII file consisting of a header row containing the column names followed by zero or more rows containing the data for the rows to be inserted, updated or deleted. Each row in the file contains the header columns listed in the next section plus whatever table columns are needed to perform the transaction.
|
Column Name |
Data Type |
Data Length |
Decimal Digits |
Description |
|
SiteID |
Integer |
|
|
ID of location to update |
|
TransactionType |
String |
7 |
|
“INSERT”, “UPDATE”, or “DELETE” |
|
PackageName |
String |
100 |
|
Package name |
|
PackageDescription |
String |
250 |
|
Package description |
|
EffectiveDate |
DateTime |
20 |
|
Date change should be made at site, now if null |
|
UserID |
String |
20 |
|
ID of user that generated the transaction |
|
ChangeDate |
DateTime |
20 |
|
Date the change was requested |
|
TableName |
String |
64 |
|
Name of the table being updated |
|
Column Name |
Data Type |
Data Length |
Decimal Digits |
Description |
|
ItemNum |
Integer |
|
|
Unique item number for price |
|
TimePeriod |
Integer |
|
|
ID of price daypart, at JIB default is 1 (All) |
|
Std |
Number |
19 |
4 |
Standard price |
|
Sun |
Number |
19 |
4 |
Sunday price, defaults to standard price if null |
|
Mon |
Number |
19 |
4 |
Monday price, defaults to standard price if null |
|
Tue |
Number |
19 |
4 |
Tuesday price, defaults to standard price if null |
|
Wed |
Number |
19 |
4 |
Wednesday price, defaults to standard price if null |
|
Thu |
Number |
19 |
4 |
Thursday price, defaults to standard price if null |
|
Fri |
Number |
19 |
4 |
Friday price, defaults to standard price if null |
|
Sat |
Number |
19 |
4 |
Saturday price, defaults to standard price if null |
|
Destination |
Integer |
|
|
Not currently used, default is 0 |
The first row contains the column names surrounded by double quotes and separated by commas. All the header columns must be included. Not all the columns of the table must be included but at least the identifying columns must be included. Here is an example of an update to the “Std” column to $5.99 in the price table at site 1234 where the identifying columns are the “ItemNum” 456 and “TimePeriod” 7:
“SiteID”,”TransactionType”,”PackageName”,”PackageDescription”,”EffectiveDate”,”UserID”,”ChangeDate”,”tableName””ItemNum”,”TimePeriod”,”Std”
1234,”UPDATE”,”A package”,”A package
description”,,”Fred”,”2008-04-21 23:00:00.000”,”tbl_ItemPricing”,456,7,5.99
String values are surrounded by quotes if they are not null and double-quotes (") inside strings must be represented by two double-quote characters (""). An empty string is represented by “”.
Numeric values are not surrounded by doubl-quotes.
Date values are surrounded by double quotes if they are not null and have the format 'yyyy-MM-DD'.
DateTime values are surrounded by double quotes if they are not null and have the format 'yyyy-MM-DD HH:mm:SS:iii' in military time where iii is the number of milliseconds.
Null values are representing by not putting any characters at all in the data.
1,"My package name","My package description","2008-04-21 23:00:00.000","Fred","2008-04-25 23:00:00.000",1234,1,5.99,0,0,0,0,0,0,0,0
1,"My package name",,"2008-04-21 23:00:00.000","Fred","2008-04-25 23:00:00.000",1234,1,5.99,0,0,0,0,0,0,0,0
1,"My package name","My ""package"" description","2008-04-21 23:00:00.000","Fred","2008-04-25 23:00:00.000",1234,1,5.99,0,0,0,0,0,0,0,0
The import function can be started from the main menu in the browser by calling the importTransactionsFromFile( filename ) function. The filename must exist on the server to be imported so any path information in the filename must be valid for the server.
When the system reads the file, it will update the central database appropriately and create transactions to be sent to the affected sites so that the same updates are made at the sites.
Updates that generate errors, such as trying to update a row that doesn’t exist, will be logged and ignored.