importtext
importtext
TEXT
,
OPTION
,
VALUE

The importtext statement imports text into an existing database.

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 new value for this option.

Description

This statement imports text into the current database. The text should be 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:

    importtext fileload("Update 2014-08-12.csv")

Advanced Note: The procedure above will automatically handle the text properly if it is encoded using UTF-8 (the default for OS X applications), Mac OS Roman (the default for OS 9 applications), or UTF-16. If you need to import text that has been saved with a different encoding you must explicitly convert the data to text using the binarytotext( function. This example will import data that has been encoded in CP-1252, a format commonly used on Windows:

    importtext binarytotext(fileload("Update 2014-08-12.csv"),"WindowsCP1252")

If the imported text contains more columns than the database, the importtext statement normally adds the extra fields needed to the database automatically. This can be disabled, see the addfields option below. The new fields are named alphabetically: A, B, C, …, AA, AB, etc. This makes it very easy to create a new database with imported data. This example will create a new database and fill it with seismic data – automatically creating whatever fields are necessary.

    newdatabase
    importtext fileload("SeismicData.csv")

If different rows in the imported data contain different numbers of columns, the number of new fields generated will be based on the imported row that has the most columns. So if most of the imported text contains 6 columns, but row 749 contains 9 columns, the database will wind up with 9 columns (unless it had more than 9 to start with).

### IMPORT OPTIONS

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

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

The possible options are separator, quote, existingdata, 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 finds one or more tabs, it assumes the text is tab separated. If no tabs are found, it normally 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 or semicolon separated text, the importtext statement normally ignores commas or semicolons inside quoted text. This allows commas and semicolons 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:

    importtext Presidents,"Quote",""

Note: Special quote processing is disabled when importing tab delimited text (or any separator other than comma or semicolon), you can use this option to enable it.

#### EXISTINGDATA

The importtext statement normally appends the imported data to the end of the current database. You can use this option to tell it to replace the data instead.

    importtext Presidents,"ExistingData","Replace"

Be careful! This will erase all of the existing data in the database! (Note: The replace option is not available for shared multi-user databases, attempting to use this option will cause an error.)

If you want to append the imported data, use append instead of replace (or just leave this option off).

#### ADDFIELDS

As described above, the importtext statement normally adds extra fields as necessary to hold all of the imported data columns. If you don’t want this to happen, set this option to false (or `"NO"`). For example, if you have a carefully crafted inventory database, you won’t want a text file with stray fields to add extra fields on the end.

    importtext fileload("Inventory Update.csv"),"AddFields","NO"

Note: If adding fields is disabled, Panorama will generate an error if the text contains extra columns that won’t fit in the existing fields. However, it will still import the data that does fit.

Note: If you are importing into a shared multi-user database (connected to a server), you must disable this option, since fields can’t be added to a shared database (except through the “new generation” process.) Because of this restriction, when using a shared database this option defaults to NO if the option is omitted.

#### FIELDTYPE

Any extra fields added by this statement are usually text fields. However, if you are absolutely sure that the data you are importing is numeric, you can use the fieldtype option to specify that any new fields created are either integers or floating point. This saves you the additional task of converting the field type after the import. The available options are either `"INTEGER"` or `"FLOAT"`, as shown in this example:

    importtext fileload("Monthly Sales.csv"),"FieldType","Float"

Remember, this only applies to any new fields that are added at the end of the database, it does not affect any existing fields. You can only specify one field type, so you can’t mix integer, float and text fields. If there is any doubt of the data type of the imported information the safest approach is to import the data as text and then convert it to numeric afterwards.

#### REARRANGE

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

Suppose you have a text file named Sam’s Contacts that contains data like this:

    Smith,John,World Widgets,124 W. Olive St,San Jose,CA,95134
    Lee,Susan,Industrial Metals,2347 N. Riverside,Cambridge,MA,02139
    Marklee,Lance,Zipper Technologies,687 E. Dorothy Lane,Bothell,WA,98011
    Anders,Fred,Acme Fireworks,5672 Lakewood Drive,Salinas,CA,93908

You want to import this data into a database that contains these 8 fields in this order:

    Company, First Name, Last Name, Title, Address, City, State, Zip

The data needs to be rearranged as it is being imported, otherwise the last name will wind up in the Company field, the street address will end up in the Title field, etc. Here’s a procedure that will append the data in Sam’s Contacts into the current database. (Note: The separator in the rearrange formula must always be a tab, even if you are importing comma delimited text.)

    importtext fileload("Sam's Contacts"),
        "REARRANGE",
            importcell(2)+tab()+
            importcell(1)+tab()+
            importcell(0)+tab()+
            tab()+
            importcell(3)+tab()+
            importcell(4)+tab()+
            importcell(5)+tab()+
            importcell(6)

The formula rearranges the incoming data so that third column in the input text goes into the first field, the 2nd column goes into the 2nd field, the first column goes into the 3rd field, the 4th field is empty, the 4th column goes into the 5th field, the 5th column goes into the 6th field, the 6th goes into the 7th field and the 7th column goes into the 8th field.

In the example above, each column in the input corresponds with one field in the final database. However, you could split up a column into multiple fields, or combine multiple columns in the input text into a single field in the final database. For example, suppose you wanted to import Sam’s Contacts into a database with the fields:

    Name, Address, City, State, Zip.

Here’s the procedure you would need.

    importtext fileload("Sam's Contacts"),
        "REARRANGE",
            importcell(1)+" "+importcell(0)+tab()+
            importcell(3)+tab()+
            importcell(4)+tab()+
            importcell(5)+tab()+
            importcell(6)

This example simply concatenates the first and last names with a space, but you can use any function you want, including the ?(, sandwich(, upper(, lower(, even lookup( functions. Here’s a slightly revised example:

    importtext fileload("Sam's Contacts"),
        "REARRANGE",
            upper(importcell(0))+", "+importcell(1)+tab()+
            importcell(3)+tab()+
            importcell(4)+tab()+
            importcell(5)+tab()+
            importcell(6)

With this procedure, the Name field will wind up with names looking like this:

    SMITH, John
    LEE, Susan
    MARKLEE, Lance
    ANDERS, Fred

The import( function returns the raw imported line. For example, suppose you wanted all of the imported data to be converted to upper case.

    importtext somedata,"REARRANGE",upper(import())

Advanced Note: Since the import( function returns the raw text, no special quote processing will be done. So the example above will not work properly if your imported data contains comma separated data with quotes around any of the fields. You can fix this with the csvtotsv( function, which converts comma separated text to tab separated text, and does the special quote processing.

    importtext somedata,"REARRANGE",csvtotsv(upper(import()))

The import( function is especially useful for importing text with no delimiters at all, for example text that is formatted into fixed width columns (with each column padded by space characters) like this. The first column is 16 characters, the second is 10, and the third is whatever is left.

    100 Meter Dash  Gold      Edwards, Mark
    100 Meter Dash  Silver    Franklin, Lee
    100 Meter Dash  Bronze    Gonzales, Esteban
    Pole Vault      Gold      George, Michael
    Pole Vault      Silver    Rafferty, Robert
    Pole Vault      Silver    Delgado, Marquise

This procedure will import this fixed width data into a database containing the fields Event, Medal, and Name.

    importtext results,"REARRANGE",
        strip(import()[1;16])+tab()+
        strip(import()[17;10])+tab()+
        strip(import()[27,-1])

This procedure uses text funnels to extract the fixed width data, see Text Funnels for more information.

See Also

- Importing a Panorama 6 Database -- Importing a Panorama 6 Database
- addfield -- adds a new field to the current database (on the end).
- addlines -- adds a specified number of records to the end of a database.
- addrecord -- adds a new record at the end of the current database.
- arraybuild -- builds an array by scanning a database and creating an array element for every record (including invisible records) in the database (see Text Arrays).
- arraybuild( -- builds an array by scanning a database and creating an array element for every record (including invisible records) in the database (see Text Arrays).
- arrayfilter( -- processes each element of a text array or a data array with a formula (see Text Arrays and Data Arrays).
- arrayselectedbuild -- builds an array by scanning a database and creating an array element for every visible (selected) record in the database (see Text Arrays).
- arrayselectedbuild( -- builds an array by scanning a database and creating an array element for every visible (selected) record in the database (see Text Arrays).
- autoallfieldwidths -- automatically sets the width of all fields based on the data in each field.
- autofieldwidth -- automatically sets the width of the current field based on the data in it.
- automaticfieldchoices -- updates the current field's Choice list with actual data in the database.
- Auxiliary Databases -- automatically opening multiple databases associated with each other.
- cell -- enters a value into the currently active field (i.e. cell).
- checkdesignlock -- checks if field structure can be changed, if not, returns an error.
- clearcell -- deletes the contents of the current field.
- clearrecord -- deletes the currently selected record.
- closedatabase -- closes a specified database.
- closefile -- closes the current database.
- commonfieldspopup -- pops up a list of common fields, and changes the current field specifications when a field is chosen from this menu.
- constructfields -- creates one or more new fields based on a template.
- copycell -- copies the contents of the current field onto the clipboard, replacing anything previously on the clipboard.
- copyrecord -- copies the contents of the current record onto the clipboard.
- Creating a New Database -- creating a new database.
- csvtotsv( -- converts comma separated text into tab separated text.
- Custom Database Initialization -- using an .Initialize procedure to automatically run custom code when a database opens.
- cutcell -- delete the contents of the current cell and places the deleted data on the clipboard.
- cutline -- copies the current record to the clipboard and deletes it.
- cutrecord -- copies the current record to the clipboard and deletes it.
- Data Input Validation -- validation of data entry (checking for duplicates, etc.)
- Database Integrity Checks -- checking database integrity when opening and saving.
- Database is Damaged Alert -- how to fix the "database is damaged" alert when opening a database.
- databaseconsoledump -- dumps the raw contents of the specified database to the console in comma delimited format.
- databaseexportcsv -- exports selected records in the current database in CSV format (comma separated) to a text file.
- databaseexportjson -- exports selected records in the current database in JSON format to a text file.
- databaseexporttsv -- exports selected records in the current database in TSV format (tab separated) to a text file.
- databaseexportusingformula -- exports selected records in the current database to a text file, using a formula.
- datatype( -- determines what kind of data is in a field or variable: text, number, etc.
- datavalidationalert -- displays an alert when invalid data has been input.
- datavalue( -- returns the value of a field or variable: text, number, etc.
- dbcheckopen( -- returns true if the specified database is currently open, false if it is not.
- dbinfo( -- gets information about a database: what forms it contains, what fields, what flash art pictures, etc.
- deleteabove -- copies the current record to the clipboard and deletes it, then moves to the record above.
- deletefield -- deletes the current field from the database.
- deleterecord -- deletes the currently selected record.
- downloadpartialdatabase -- downloads specified components of the current database from the server. The current database must be shared.
- downrecord -- moves the cursor down one visible record.
- duplicaterecord -- duplicates the current record.
- editcell -- opens the edit window for the currently active field (or cell), highlighting the data, and allows you to edit that field.
- editcellstop -- opens the edit window for the currently active field (or cell), highlighting the data, and allows you to edit that field.
- editcellwith -- opens the edit window for the currently active field (or cell), using the supplied alternate text.
- editfield -- begins editing of the specified field.
- emptydatabase( -- returns true if the current database is completely blank.
- emptyfield -- tells Panorama to move to the next available (empty) field in a series of line item fields.
- emptyline( -- returns true if the entire current record (all fields) is blank, otherwise it is false.
- excludefromrecentmenu -- excludes the next opened database from the Recent menu.
- export -- exports the selected rows in the current database into a text file.
- exportjson -- exports the current database in JSON format.
- fieldname -- changes the name of the current field.
- fieldtype -- changes the data type of the current field.
- fieldvalue( -- grabs the contents of a field in the current record of a database. You can grab data from the current database, or from another database.
- firstrecord -- makes the first visible (selected) record in the database the active record.
- formulacalc -- allows you to evaluate a formula that you were not able to code into the procedure when it was being written.
- formulastrings( -- returns a data array of strings (text constants) used in a formula.
- formulavalue -- calculates the result of a formula. Usually this is done with an assignment statement (for example `x=2 * y` ), but the *formulavalue* statement gives you more flexibility. You can specify what database is to be used for the calculation (an assignment statement always uses the current database) and you can specify the formula using a variable, making it easy to change on the fly. This statement also gives you more control over how errors are handled.
- formulavalue( -- calculates the result of a formula based on data in the current record of any open database. This is similar to the fieldvalue( function, but allows any formula to be used, rather than just a single field.
- getautonumber -- returns the automatically generated number for the next record that will be added to the database.
- getautonumber( -- returns the automatically generated number for the next record that will be added to the database.
- getfieldproperties( -- returns a dictionary containing all of the properties of the specified field. (See the setfieldproperties statement if you want to change one or more field properties.)
- getproceduretext -- gets the contents (source) of a procedure and places it in a variable.
- growl -- displays a notification.
- growlmessage -- displays a notification.
- 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.
- Importing Binary Data from Panorama 6 -- Importing Binary Data from Panorama 6
- importjson -- imports a JSON array into the current database.
- importjsonline -- imports a JSON record into the current record.
- importline -- imports a line of text into the current record.
- importrawdata -- imports raw binary data into an existing database.
- info("databasefilename") -- returns the name of the current database.
- info("rawdatabasedata") -- returns the binary data of the current database.
- info("rawrecord") -- returns the binary data of the current record.
- info("rawrecordid") -- returns a number that represents the location of the current record within the database.
- insertbelow -- inserts a new record below the current record.
- insertbelowcarriagereturn -- inserts a new record below the current record and moves to the first column.
- insertfield -- inserts a new field into the database in front of the current field.
- insertrecord -- inserts a new record above the current record.
- join -- joins data from another database into the current database.
- joinonerecord -- joins matching data from another database into the current record.
- lastlookupdatabase( -- returns the name of the database referenced in the most recent lookup.
- lastrecord -- makes the last visible (selected) record in the database the active record.
- left -- makes the previous field active in the active window.
- listchoices( -- builds a text array containing a list of all the values stored in a specified field.
- lookup( -- searches a database for a value, then returns other information from the same record. For example, the *lookup(* function can look up a phone number given a customer name, or look up a price given a part number.
- lookupall( -- builds a text array containing one item for every record in the target database where the data in the keyField matches the keyData. Each item in the text array contains the value extracted from the dataField for that record. If the data field is a numeric or date field, it is converted to text using the default patterns for that field.
- lookupalldouble( -- is similar to the lookupall( function, but it returns two fields from the target database instead of just one.
- lookupalloctet( -- is similar to the lookupall( function, but it returns eight fields from the target database instead of just one.
- lookupallquadruple( -- is similar to the lookupall( function, but it returns four fields from the target database instead of just one.
- lookupallquintuplet( -- is similar to the lookupall( function, but it returns five fields from the target database instead of just one.
- lookupallseptuplet( -- is similar to the lookupall( function, but it returns seven fields from the target database instead of just one.
- lookupallsextet( -- is similar to the lookupall( function, but it returns six fields from the target database instead of just one.
- lookupalltriple( -- is similar to the lookupall( function, but it returns three fields from the target database instead of just one.
- lookuplast( -- searches the selected records in a database for a value, then returns other information from the same record. For example, the *lookuplast(* function can look up a phone number given a customer name, or look up a price given a part number. Unlike the lookup( function which searches from the top of the database, the *lookuplast(* function searches backwards from the bottom.
- lookuplastselected( -- searches the selected records in a database for a value, then returns other information from the same record. For example, the *lookuplastselected(* function can look up a phone number given a customer name, or look up a price given a part number. Unlike the lookupselected( function which searches from the top of the database, the *lookuplastselected(* function searches backwards from the bottom.
- lookupmoredata( -- looks up an additional data field based on the previous lookup.
- lookupmoreformula( -- looks up additional data based on a previous lookup.
- lookupselected( -- searches the selected records in a database for a value, then returns other information from the same record. For example, the *lookupselected(* function can look up a phone number given a customer name, or look up a price given a part number.
- movefieldbefore -- moves the current field to a new position.
- moverecorddown -- moves the current record down one line.
- moverecordup -- moves the current record up one line.
- newdatabase -- creates a new database.
- newdatabasewithfields -- creates a new database with one or more fields.
- newdatabasewithjson -- creates a new database using a JSON file.
- newdatabasewithtemplate -- creates a new database with a template.
- 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.
- opendatabase -- opens a database file.
- openfile -- opens a database file. It can also import data from a text file, another database, or a variable into the current database.
- Opening a Database in Diagnostic Mode -- opening a database so that no implicit code is triggered.
- openplain -- opens a database without opening any pre-saved windows, just the data sheet.
- opensecret -- opens a database invisibly, without opening its windows.
- opentextfile -- imports data from a text file or a variable into the current database.
- panoramastartupdatabases -- opens any databases specified for startup.
- partialdatabaseupdate -- updates one or more components of an existing database.
- pastecell -- pastes the contents of the clipboard into the current cell.
- pasteline -- inserts a new record containing the contents of the clipboard.
- pasterecord -- inserts a new record containing the contents of the clipboard.
- printpdf -- prints the current database to a PDF file.
- printpreview -- previews what printing the current window will look like.
- printpreviewonerecord -- previews what printing the current record will look like.
- recalculatefield -- recalculates all values in current field based on field's formula (selected records only).
- recompile -- recompiles all procedures in a database.
- reorderfieldsdialog -- opens the Reorder Fields dialog.
- right -- moves the cursor to the next field in the active window.
- runfieldcalculations -- performs any automatic calculations associated with the current field.
- runfieldcode -- runs the automatic code associated with the specified field.
- runfieldsideeffects -- performs any side effects associated with the current field (calculations, procedures, etc.).
- saveandclose -- saves and closes a database file.
- saveblueprint -- save a blueprint of a database's structure.
- savepartialdatabase -- saves specified components of the current database, leaving other components out.
- seq( -- returns sequential numbers (1, 2, 3, etc.).
- serverdatabaseconsoledump -- dumps the raw contents of the specified database on the server to the console in comma delimited format.
- serverlookup( -- searches a shared database for a value, then returns other information from the same record. Similar to the lookup( function, but the search is performed on the server instead in the local computer's memory.
- setactivedatabase -- makes a database active (without changing the configuration of the windows).
- setautonumber -- changes the automatically generated number for the next record that will be added to database. This allows you to generate numbers out of sequence, or to start the sequence at a specific value.
- setdatabaseoptions -- modifies one or more properties of a database.
- setfieldnames -- changes the names of all database fields at once.
- setfieldproperties -- modifies one or more properties (name, data type, formula, etc.) of the current field.
- sizeof( -- calculates the amount of memory used by a field or a variable.
- superarraybuild -- scans a database to create a text array. This statement is is similar to arraybuild, but with a number of additional options (see Text Arrays).
- superlookup( -- searches a database for a record that matches a query formula, then uses a second formula to return other information from the same record. This is similar to the lookup( function, but instead of using an individual field for the key and data, any valid true/false (Boolean) formula can be used.
- table( -- searches a database for a value, then returns other information from the same record. Unlike the lookup( function, the *table(* function does not require an exact match. If it does not find an exact match the *table(* function will use the closest match. For example, the *table(* function can look up a tax rate given an income amount, or look up a shipping price given a zip code and weight.
- 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.
- uniqueid( -- generates ID codes with a text root and a numeric suffix (for example `Jeff261`).
- unlockrecord -- unlocks the currently active record.
- uploadrecord -- uploads the current record to the server, even if it was not locked (for emergency use only).
- uprecord -- moves the cursor up one visible record.

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. Also, if the database is shared (multi-user), you cannot use the ADDFIELDS or REPLACE option with this statement, and ADDFIELDS defaults to NO for shared databases.
10.0 | New | New in this version, but includes features that were part of the *OpenFile* and *ImportUsing* statements in Panorama 6 and earlier versions.