1.6. Database Attributes
Especially in SQL databases, there are certain functions that can be set with additional database attributes. For text import, the CONTINUE extended database import attribute allows additional control over the format of imported text.
Note: CONTINUE applies to all types except CSV. (Do not use NOTES unless using type LINE.)
One of UNIX, DITTO, LINE, NONE, or NOTES. The default, NONE, is to simply split the line/record according to the delimiter, with no possible spanning of records. Setting CONTINUE to UNIX appends the next line to the current when it encounters a backslash (\) at the end of a record, just like many UNIX commands and shells.
DITTO is invoked when the key field is blank. It adds the contents of following fields to the one above, separated by a new line character. This allows additional text to be added to a field beyond the 255 characters available with most spreadsheets and flat-file databases.
Example in catalog.cfg:
Database products products.txt TAB Database products CONTINUE DITTO
code price description 00-0011 500000 The Mona Lisa, one of the worlds great masterpieces. Now at a reduced price!
The description for product 00-0011 will contain the contents of the description field on both lines, separated by a new line.
Note: Fields are separated by tabs, formatted for reading convenience.
This will work for multiple fields in the same record. If the field contains any non-empty value, it will be appended.
LINE is a special setting so a multi-line field can be used. Normally, when using the LINE type, there is only data on one line separated by one blank line. When using CONTINUE LINE, there may be some number of fields which are each on a line, while the last one spans multiple lines up until the first blank line.
Example in catalog.cfg:
Database products products.txt LINE Database products CONTINUE LINE
code price description 00-0011 500000 The Mona Lisa, one of the worlds great masterpieces. Now at a reduced price! 00-0011a 1000 A special frame for the Mona Lisa.
NOTES reads a Lotus Notes "structured text" file. The format is any number of fields, all except one of which must have a field name followed by a colon and then the data. There is optional whitespace after the colon.
Records are separated by a settable delimiting character which goes on a line by itself, much like a "here document." By default, it is a form feed (^L) character. The final field begins at the first blank line and continues to the end of the record. This final field is named notes_field, unless set as mentioned below.
Interchange reads the field names from the first paragraph of the file. The key field should be first, followed by other fields in any order. If one (and only one) field name has whitespace, then its name is used for the notes_field. Any characters after a space or TAB are used as the record delimiter.
If there are none, then the delimiter returns to the default form feed (^L) and the field name reverts to notes_field. The field in question will be discarded, but a second field with whitespace will cause an import error. Following records are then read by name, and only fields with data in them need be set. Only the notes_field may contain a new line. It is always the last field in the record, and begins at the first blank line.
The following example sets the delimiter to a tilde (~) and renames the notes_field to description.
Example in catalog.cfg:
Database products products.txt LINE Database products CONTINUE NOTES
code title price image description ~ size color title: Mona Lisa price: 500000 code: 00-0011 image: 00-0011.jpg The Mona Lisa, one of the worlds great masterpieces. Now at a reduced price! ~ title: The Art Store T-Shirt code: 99-102 size: Medium, Large*, XL=Extra Large color: Green, Blue, Red, White*, Black price: 2000 Extra large 1.00 extra. ~
Microsoft Excel is a widely-used tool to maintain Interchange databases, but has several problems with its standard TAB-delimited export, like enclosing fields containing commas in quotes, generating extra carriage returns embedded in records, and not including trailing blank fields. To avoid problems, use a text-qualifier of none.
Set the EXCEL attribute to 1 to fix these problems on import:
Database products EXCEL 1
This is normally used only with TAB-delimited files.
Interchange databases containing many records can result in a noticeable slowdown when displayed by the UI.
Set the LARGE attribute to 1 to avoid this problem:
Database transactions LARGE 1
In this case the UI supplies only input boxes to search records in the database instead of drawing all the records from the database, sorting them and creating more lists.