R:BASE 11 Help

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 AS ASCII command parameter is designed strictly for speed of operation. AS ASCII checks rules and constraints. However, following is a list of the limitations of the AS ASCII command parameter:

 

•It does not check the 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 32,768 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 32,768 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.

 

AS CSV

Loads data from a comma-separated values (CSV) file. AS CSV checks rules and constraints. The following is a list of the limitations of the AS CSV command parameter:

 

•It does not check the 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.

 

The data must look like the data that R:BASE unloads with the UNLOAD DATA AS CSV 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 32,768 characters.

•The data cannot include variables.

•The data cannot include comments.

 

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 condition. 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.

 

 

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 condition 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

By default, 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. To load data into a computed column, the NOCALC setting must be set ON.

 

The setting of the SET ZERO condition 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, e.g. ($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 Example 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 80 characters. To enter columns with a NOTE or TEXT data types that contain more than 80 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 autonumbered column values.

 

Loading without Prompts

Loading without prompts 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. 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. 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 setting. (The default delimiter is a comma.) R:BASE also accepts a blank space as a delimiter, regardless of the setting of the DELIMIT setting.

 

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.

 

 

 

Loading a Data Block

The data block shown in the 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 dialog 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 dialog 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

 

Example 01:

The following command line allow you to load rows containing new customer information to the customer table. R:BASE asks with prompts 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 the [Esc] key or the "Cancel" button before you have finished entering data in a row, the row is not added to the table. You will be prompted to add another row.

 

To continue, press the "Yes" button. To exit, press the "No" button or the [Esc] key.

 

Example 02:

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

 

LOAD customer FROM cust.dat FOR 5 ROWS

 

Example 03:

In the following example, the command line tells R:BASE to start loading data for the customer table. A dialog prompt is displayed for each new row. Each column value would be entered in this one dialog and separated with a comma, or the current delimiter. The legnth of the text available to fit in the dialog is 4096 characters.

 

LOAD customer

 

Example 04:

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

 

LOAD customer FOR 5 ROWS

 

Example 05:

The following command lines show you how to load data into the company, custaddress, custcity, custstate, and custzip columns 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

 

Example 06:

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).

 

LOAD transdetail

6000,1,'CX3000',100,$1900

END

 

Example 07:

The following loads an image file into a table. The square brackets "[ ]" tell R:BASE that an image is being loaded, rather than text. The file name within the brackets must be within quotes using the value for the QUOTES setting. The column for the image must be defined as the VARBIT data type.

 

LOAD ImageTable

1,['image.bmp']

END