importcell( function

importcell(

COLUMNNUMBER

)

The importcell( function returns one cell of imported data.

Parameters

This function has one parameter:

columnnumber – is the column of data from the imported text that you want to return. The text being imported is separated into columns by either tabs or commas. The first column is column 0, the next is column 1, etc.

The importcell( function always returns a text type data item. When it is used with the ImportText statement, the importcell( function returns the contents of the specified column from the line that is currently being imported. If the text being imported is comma delimited, the importcell( function will strip off any quotes around the data before returning it. Using this function you can process and re-arrange the data as it is being imported.

When it is used at any other time, the importcell( function returns empty text. It will also return empty text if you specify a column number that does not exist in the text being imported.

Description

This function returns one cell of imported data. It is designed to be used in conjunction with the rearrange option of the ImportText statement to facilite rearranging data as it is being imported.

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.

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 re-arranges 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 Sam’s Contacts into a database with the fields,

Name, Address, City, State, Zip.

You could use this procedure:

importtext fileload("Sam's Contacts"),
    "REARRANGE",
        importcell(1)+tab()+" "+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.

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.
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.
importline -- imports a line of text 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.0	No Change	Carried over from Panorama 6.0