importline statement

importline

TEXT

OPTION

VALUE

The importline statement imports a line of text into the current record.

Parameters

This statement has three parameters:

text – text to import. (If you want to import text from a file, use the fileload( function to generate this parameter value).

option – An option for customizing the way the import works. See below for descriptions of each option. The option can be specified in either upper or lower case.

value – The value for this option.

Description

This statement imports text into the current record, replacing whatever is already in the record. The text should be a single line organized into fields separated either by commas (often called comma separated text, or CSV), or by tabs. In its simplest form you simply supply the data to be imported:

importline "Wilson,Smith,289 Markle Place,Pleasantville,AX"

IMPORT OPTIONS

In addition to the text to import, you can also specify multiple option/value pairs in a single importline statement, like this:

importline text,option1,value1,option2,value2,option3,value3

The possible options are separator, quote, excessfieldserror, addfields and rearrange. Each of these options is described in detail below.

SEPARATOR

This option is used to explicitly specify the column separator character, for example comma, tab, or semicolon.

importtext sometext,"Separator","," ☞ comma separated
importtext sometext,"Separator",tab() ☞ tab separated
importtext sometext,"Separator",";" ☞ semicolon separated

If the separator parameter is omitted, Panorama will automatically choose the separator to use. It does this by scanning the first line of the text – if it finds one or more tabs, it assumes the text is tab separated. If no tabs are found, it normally will assume the text is comma separated. However, if the decimal point is configured to comma instead of period, it will assume the text is semicolon separated. (The decimal point character is controlled by the Language & Region panel in the System Preferences window.)

QUOTE

When importing comma separated text, the importline statement normally ignores commas inside quoted text. This allows commas to appear in the data itself, like this:

"Washington, George",Virginia
"Adams, John",Masachusetts
"Jefferson, Thomas",Virginia

Quoted text can also contain quotes, like this:

"Washington said ""I cannot tell a lie""",false

This option allows you to change the quote character, or you can specify "" to disable this special quote processing altogether, like this:

importline Presidents,"Quote",""

Note: Special quote processing is normally disabled when importing tab delimited text, you can use this option to enable it.

EXCESSFIELDSERROR

If the imported text contains more columns than the database, the extra columns are normally ignored. Use this option if you would like an error to occur in this situation:

importline Presidents,"EXCESSFIELDSERROR","TRUE"

The next option describes an alternate way to handle excess fields – add them to the database!

ADDFIELDS

The importline statement normally ignores any extra fields in the imported text. Another option is to actually add additional fields to the database to accomodate the extra data.

importline meterReadings,"AddFields","YES"

Any new fields added will be assigned alphabetical field names – E, F, G, H, etc.

Note: The importline statement handles this option differently than the importtext statement. For the importline statement, this option defaults to OFF, and no error is generated if there are excess fields (unless you set the excessfieldserror option, see above).

REARRANGE

The importline statement normally imports data in left to right order – the leftmost column of the imported text is imported into the first database field, the next column is imported into the second field, etc. The rearrange option allows this order to be changed, or can even be used to combine, ignore, or otherwise manipulate the imported data as it is brought into the database. (Legacy note: The rearrange option performs the same function as the importusing statement in previous versions of Panorama.)

Using the rearrange option, you specify a formula that is used to process the import data. This formula is “inserted” into the middle of the import process. The formula must be designed to take in a line of text, then transform it into a different line of text. The importline statement uses this formula to transform each raw line of the import data into a new, manipulated line. This new manipulated line is then imported into the database instead of the original line.

Panorama has a two special functions that allow the import translation formula to access the line that has been read from the disk: import( and importcell(. The import( function returns the entire line that has been imported. The importcell( function has one parameter that specifies the number of the cell you want, for example importcell(1) or importcell(14). (Note that importcell( uses 0 for the leftmost field, not 1).

For more information and examples of this option, see the importtext statement.

See Also

arrayfilter( -- processes each element of a text array or a data array with a formula (see Text Arrays and Data Arrays).
csvtotsv( -- converts comma separated text into tab separated text.
Import Database -- importing another database into the current database.
import( -- returns a line or element of imported data. This function works in conjunction with repetitive operations like importtext, arrayfilter(, characterfilter( etc.
importcell( -- returns one cell of imported data.
importdatabase -- imports data from another database into the current database.
importjson -- imports a JSON array into the current database.
importjsonline -- imports a JSON record into the current record.
importrawdata -- imports raw binary data into an existing database.
importtext -- imports text into an existing database.
newdatabasewithtextfieldnames -- creates a new database from a text file. The text file must contain the field names in the first line.
newdatabasewithtextfile -- creates a new database from a plain text file. The fields will be named A, B, C, etc.
partialdatabaseupdate -- updates one or more components of an existing database.
seq( -- returns sequential numbers (1, 2, 3, etc.).
Text Import -- importing text into an existing database.
tsvtocsv( -- converts tab separated text into comma separated text.
tsvtojson( -- converts tab separated text into JSON formatted text.

History

Version	Status	Notes
10.2	Update	This statement now uses semicolon as the default CSV separator if the system is configured to use comma as the numeric decimal point.
10.0	New	New in this version.