LOAD

LOAD (Short Name: LOA)

Use the LOAD command to add data to a table or to a single table view that can be updated.

Options

,
Indicates that this part of the command is repeatable.

AS ASCII
The LOAD AS ASCII command is designed strictly for speed of operation. LOAD AS ASCII checks rules and constraints. However, following is a list of the limitations of the LOAD AS ASCII command:

It does not check data types; therefore, invalid data will be loaded as null values into columns; no error messages about this conversion are displayed.

It does not display error messages when columns must be truncated, or when excess data exists on any line.

It does not echo data to the screen regardless of the setting for SET ECHO.

To achieve maximum speed of loading, the data must look like the data that R:BASE unloads with the UNLOAD DATA AS ASCII command. That is, the data must conform to the following:

The carriage return/line feed characters define the end of the line for a given row; the maximum row size is 4096 characters.

The data cannot include variables.

The data cannot include comments.

AS FORMATTED USING
Loads data from an ASCII file when the data is formatted in fixed column locations, with the following restrictions:

The carriage return/line feed characters define the end of the line for a given row; the maximum row size is 4096 characters.

The data cannot include variables.

The data cannot include comments.

You must specify the name of each column of the table to be loaded, and the starting and ending position of its data in the line, which is specified in the USING clause of this command.

CHECK
NOCHECK
CHECK turns on rule checking. When rule checking is on, R:BASE checks input against data validation rules. NOCHECK turns off rule checking. CHECK and NOCHECK override the current setting of the SET RULES command. The default is CHECK.

colname start end
Specifies the name of a column in the table and the starting and ending position of its data in the line; this option is used with the AS FORMATTED option.

data-block
Includes lines of data to be loaded, as well as the LOAD subcommands.

For these data types...
Use this format for data-block

All data types except BIT, BITNOTE, LONG VARBIT, and VARBIT
'string' or value

BIT, BITNOTE, LONG VARBIT, LONG VARCHAR, VARBIT, and VARCHAR
['filename.ext'] or ['filename.ext', filetype, offset, length] Note: When you use VARCHAR, the filetype is always TXT. When you use VARBIT, BIT, and BITNOTE, filetype refers to the standard graphical file types.

FILL
NOFILL
FILL makes null any columns that have not been assigned values. All of the missing values must be at the end of the row. If a rule specifies that a column requires an entry other than null, do not use FILL. NOFILL turns off FILL and requires a value for each column. The default is NOFILL.

FOR n ROWS
Directs R:BASE to stop processing after loading n rows, where n is a positive whole number. In the fourth syntax diagram, END is not used if FOR n ROWS is included.

FROM filespec
Loads data into the specified table with data from an external ASCII delimited file.

NUM
NONUM
NUM specifies that autonumbering columns will be numbered as they are loaded. NONUM turns off autonumbering while loading, thereby allowing loading of a specific value for autonumber columns. The default is NUM.

tblview
Specifies a table or view name to load.

USING collist
Specifies the column(s) to use with the command.

WITH PROMPTS
Loads data into the specified table from keyboard entries. R:BASE asks for the values of each column by displaying the column name and its data type. To end the loading session, press [Esc].

About the LOAD Command

You cannot load data into a multi-table view.

Instead of using LOAD, you can also use INSERT, the Data Editor, or a form to add data to a table.

You can use the LOAD command to load data into R:BASE from a file that was not created by R:BASE. The file must be an ASCII file, either delimited or fixed.

The LOAD command will differentiate between END and 'END'; FILL and 'FILL'; NOFILL and 'NOFILL'; CHECK and 'CHECK'; NOCHECK and 'NOCHECK'; NUM and 'NUM'; NONUM and 'NONUM'. So, make sure to use the proper syntax when creating LOAD statements.

To ensure that data is placed in the intended column, use the following guidelines:

Do not embed commas within entries for CURRENCY, DATE, DATETIME, DOUBLE, INTEGER, NUMERIC, or REAL data types. R:BASE automatically inserts commas and the current currency symbol.

When values for CURRENCY, DOUBLE, NUMERIC, or REAL or data types are decimal fractions, you must enter the decimal point. When values are whole numbers, R:BASE adds a decimal point for you at the end of the number. R:BASE adds zeros for subunits in whole currency values. For example, using the default currency format, R:BASE loads an entry of 1000 as $1,000.00.

When values for NOTE or TEXT data types contain commas, you can either enclose the entries within quotes, or use SET DELIMIT to change the default delimiter (comma) to another character.

When values for NOTE or TEXT data types contain single quotes ('), and you are using the default QUOTES character ('), use two single quotes ('') in the text string. For example, 'Walter Finnegan''s order.'

When a value you specify for a column is not the same data type as the column's data type, R:BASE displays an error message and you need to re-enter the entire row.

When values for NOTE or TEXT data types exceed the maximum length of a column, R:BASE truncates the value and adds it to the table. A message is displayed that tells you which row has been truncated.

Loading with a USING Clause
A USING clause is helpful when you do not have all the information that is to be added to a table. The following example lets you enter some information for a product but does not require that all columns be entered. The model and listprice columns are the first and last columns in the product table. The prodname and proddesc columns are not included in the command and are loaded with null values. You can later edit the product table to enter data into the columns that have null values.

LOAD product USING model listprice

Loading with the CHECK Option
The SET RULES command does not have any effect on the CHECK option because CHECK has precedence over a RULES setting. When RULES is set off, the CHECK option still verifies data entry against existing rules.

When a user identifier has been assigned to the database owner, you must enter the owner's user identifier with the CONNECT or SET USER command before you use the CHECK or NOCHECK option. R:BASE does not accept the CHECK or NOCHECK option unless the owner's user identifier has been entered.

Loading Computed Columns
You cannot load data directly into a computed column. After you load the column values that are used to calculate the computed column, R:BASE fills the computed column with the computed value.

The setting of the SET ZERO command affects the calculation of numeric computed columns. To have null values treated as zeros in expressions, set ZERO on. When ZERO is set off, if the value of a column used in a expression is null, the computed value will be null.

Loading Negative CURRENCY values
When loading negative CURRENCY values into a table, the format must include the hyphen, i.e -$500.00. Negative CURRENCY values encased in parenthesis are not recognized, i.e. ($500.00).

Loading with Prompts
When you run the LOAD command using prompts, you load one row of data at a time into the table you specified. (See Syntax 1). For each new row you add, R:BASE displays the name and data type of the row's column as prompts. At each prompt, you enter the value that you want the column to contain. You are prompted for each column in the row beginning with the first column, unless you used a USING collist clause to limit the number of the columns to load, or to change the order in which the columns are loaded. Any columns not listed in the collist are given null values when the rows are entered.

When you load data with prompts, the default length for a text entry is 58 characters. To enter columns with a NOTE or TEXT data types that contain more than 58 characters, load the data without prompts, make a custom data-entry form, or set the WIDTH so you can enter more characters.

R:BASE does not prompt you for computed or autonumber column values.

Loading without Prompts
Loading without prompts (See Syntax 4) is faster but requires that you remember the order of the columns in the table. When you load without using prompts and not from an ASCII file, the LOAD command provides its own distinctive prompt, which is L>. The following options can be entered at this prompt: CHECK, NOCHECK, FILL, NOFILL, NUM, and NONUM.

Loading from an ASCII File
Use the LOAD command from the R> prompt or a command file to load data into an existing table from both delimited and fixed field ASCII files. (See Syntax 2 and 3 diagrams.) Each record in the ASCII file corresponds to one row of data in a table, and each item of data in a record corresponds to one column value in a row. Therefore, organize data in the file in the same order as the columns in the table to be loaded.

Items of data in a line of the ASCII file must be delimited to be properly placed within the columns of a row. The delimiter character must be the same as the current delimiter character specified with the SET DELIMIT command. (The default delimiter is a comma.) R:BASE also accepts a blank space as a delimiter, regardless of the setting of the SET DELIMIT command.

Data can be loaded in a fixed-field formatted ASCII file with the AS FORMATTED option. The column name and the start and end positions within the file must be specified for each value in the row of data that is to be loaded. When the start and end positions are specified, the delimiter character does not have any effect because the start and end positions for each column identify the data.

When loading from a file, be sure that the current null symbol is not a blank. If the first four characters of a field in a file are blank, R:BASE adds the column as a null column and does not read any additional characters that make up the field value.
When loading data from an ASCII file, make sure the file meets the following requirements listed in the table below.

Elements in an ASCII File
Requirement

INTEGER data types
Items of data to be loaded into columns with INTEGER data types cannot contain internal commas unless the item is enclosed in quotes. The default QUOTES character in R:BASE is a single quote ('); if your ASCII file uses double quotes ("), change the QUOTES setting before you load the file. If the file does not have quotes around the integer values containing commas, you must edit the ASCII file to remove any commas from the integer values, or enclose each integer value in quotes.

Embedded punctuation
Items of data containing ampersands, commas, embedded blanks, plus signs, equal signs, or semicolons must be enclosed in quotes if they are to be loaded into columns with a TEXT or NOTE data type. The default QUOTES character in R:BASE is a single quote ('); if your ASCII file uses double quotes ("), change the QUOTES setting before you load the file.

Embedded quotes
Items of data requiring quotes can also contain embedded quotes. For example, the item 'Basic' Keyboard contains both a blank space and embedded quotes. Using single quotes ('), which is the default QUOTES setting, to add enclosing quotes, the item would looks like this: '''Basic'' Keyboard'

Currency
R:BASE automatically adds a currency symbol, commas, and zeros for currency units. For example, using the default currency format, R:BASE loads an entry of 1000 as $1,000.00. Negative currency values loaded into

Dates
The SET DATE sequence command sets the sequence for the date-dates in the file are loaded if the dates match the current date sequence established with the SET DATE command.

Computed columns
If the table being loaded has computed columns and the file contains values for the computed columns, R:BASE tries to load the computed column's value from the file into the column following the computed column. This results in an error because the data type of the next column might not be the correct data type, or the file will have too many values for the table because R:BASE does not load the computed column's value from the file.

Rules processing
Unless you run the SET RULES OFF command before loading the file, rules processing is in effect. When an incoming data item violates a rule, R:BASE does not load the row. Instead, R:BASE displays the message for the rule that has been violated. To see the data that causes a rule violation, set ECHO on when loading a table and use the [Pause] key to stop the screen from scrolling when the rule violation occurs.

Loading a Data Block
The data block shown in the Syntax 4 diagram can include lines of data and any of the options available with LOAD-CHECK/NOCHECK, FILL/NOFILL, and NUM/NONUM. You can intersperse the options with data lines, and you can enter more than one option on a line if you separate the options with semicolons. However, you cannot combine data and options on the same line.

R:BASE displays the L> prompt to accept data-block entry. LOAD adds data to a table, row by row, without using a data-entry form and without prompting for each data item.

You can enter the options for the LOAD command at the L> prompt at any time during data loading. Or you can include them on the command line, separated from the command by semicolons, as shown in the example below. (Do not use this format in command or procedure files. All options must follow the LOAD command on separate lines in command or procedure files.)

LOAD transdetail ; CHECK ; NUM

You can use global or system variables instead of constant values in the data block.

To enter values properly, use the following guidelines.

Enter column values in the order that columns are defined in the table, and separate the values with a delimiter character. The default delimiter character is the comma.

You can enter up to 75 characters on a single line. If a row is longer than 75 characters, continue on to the next line by typing past the end of the current line or by entering a plus (+) sign at any point on the current line. The plus sign must be the last entry on the line. The new line will begin with a +> prompt to indicate the continuation of the current line. If you are using this form of the LOAD command in a command file, you must use a + to continue on the next line; the lines will not automatically wrap.

For other requirements on loading data, see "Loading from an ASCII File" earlier in this entry.

Examples

The following command lines allow you to load rows containing new customer information to the customer table. R:BASE asks for each column by column name and data type. Two columns in the table, custid and custphone, are omitted from the list. R:BASE automatically supplies a number for the custid column because it is an autonumbered column. R:BASE leaves the custphone column empty (null) when data is loaded, and does not prompt for either column.

LOAD customer WITH PROMPTS USING company, +
custaddress, custcity, custstate, custzip

After the above command is run, the WITH PROMPTS option displays the message below. If you press [Esc] before you have finished entering data in a row, the row is not added to the table. When you are prompted to exit, press [Esc].

Press Esc to end, Enter to continue
company (TEXT):

The following command loads five rows of data into the customertable from CUST.DAT, a delimited ASCII file. The data in the ASCII file must be in the same order as the columns in the customertable. Only the first five lines from the file will be loaded:

LOAD customer FROM cust.dat FOR 5 ROWS

In the following example, the first command line starts the loading process. When loading begins, an L> prompt is displayed for each new line. The second command line, which contains the FILL option, tells R:BASE to give any additional columns at the end of the row null values. The third command line loads the values from three global variables into a new row. The values fill the first three columns of the new row, while the remaining columns (if any) are given null values. The last command line tells R:BASE to end the loading session:

R>LOAD bonusrate
L>FILL
L>.v1, .v2, .v3
L>END

In the following example, the first command line tells R:BASE to start loading data for the customer table. The second command line tells R:BASE to number any defined autonumbered columns in the table. A value for the autonumbered column is not designated because the row might not be loaded if a value is included.

R>LOAD customer
L>NUM

After the command line in the following example is run, R:BASE expects the next five lines entered at the L> prompt to contain data to be loaded into the customer table. After the fifth line of data is entered, the R> prompt returns and loading ends. To end loading before five rows of data are entered, enter END.

R>LOAD customer FOR 5 ROWS

The following command lines show you how to load data into the company, custaddress, custcity, custstate, and custzipcolumns of the customer table. The custid and custphone columns in the customer table will not have data loaded and will be given null values.

LOAD customer FROM customer.fix AS FORMATTED +
USING company 11 50, custaddress 51 80, +
custcity 81 100, custstate 101 102, custzip 103 112

When you use the LOAD command, you must omit values for computed or autonumbered columns. Instead, enter the value for the next column in the data list. In the following example, to add a row to the transdetail table, which has a computed column, you would only enter data for the first five columns; the sixth column is a computed column based on the fourth and fifth columns. The columns entered are transid, detailnum, model, units, and price. The computed column is extprice and has the expression (units * price).

R>LOAD transdetail
L>6000,1,'CX3000',100,$1900
L>END

For these data types...	Use this format for data-block
All data types except BIT, BITNOTE, LONG VARBIT, and VARBIT	'string' or value
BIT, BITNOTE, LONG VARBIT, LONG VARCHAR, VARBIT, and VARCHAR	['filename.ext'] or ['filename.ext', filetype, offset, length] Note: When you use VARCHAR, the filetype is always TXT. When you use VARBIT, BIT, and BITNOTE, filetype refers to the standard graphical file types.

Elements in an ASCII File	Requirement
INTEGER data types	Items of data to be loaded into columns with INTEGER data types cannot contain internal commas unless the item is enclosed in quotes. The default QUOTES character in R:BASE is a single quote ('); if your ASCII file uses double quotes ("), change the QUOTES setting before you load the file. If the file does not have quotes around the integer values containing commas, you must edit the ASCII file to remove any commas from the integer values, or enclose each integer value in quotes.
Embedded punctuation	Items of data containing ampersands, commas, embedded blanks, plus signs, equal signs, or semicolons must be enclosed in quotes if they are to be loaded into columns with a TEXT or NOTE data type. The default QUOTES character in R:BASE is a single quote ('); if your ASCII file uses double quotes ("), change the QUOTES setting before you load the file.
Embedded quotes	Items of data requiring quotes can also contain embedded quotes. For example, the item 'Basic' Keyboard contains both a blank space and embedded quotes. Using single quotes ('), which is the default QUOTES setting, to add enclosing quotes, the item would looks like this: '''Basic'' Keyboard'
Currency	R:BASE automatically adds a currency symbol, commas, and zeros for currency units. For example, using the default currency format, R:BASE loads an entry of 1000 as $1,000.00. Negative currency values loaded into
Dates	The SET DATE sequence command sets the sequence for the date-dates in the file are loaded if the dates match the current date sequence established with the SET DATE command.
Computed columns	If the table being loaded has computed columns and the file contains values for the computed columns, R:BASE tries to load the computed column's value from the file into the column following the computed column. This results in an error because the data type of the next column might not be the correct data type, or the file will have too many values for the table because R:BASE does not load the computed column's value from the file.
Rules processing	Unless you run the SET RULES OFF command before loading the file, rules processing is in effect. When an incoming data item violates a rule, R:BASE does not load the row. Instead, R:BASE displays the message for the rule that has been violated. To see the data that causes a rule violation, set ECHO on when loading a table and use the [Pause] key to stop the screen from scrolling when the rule violation occurs.