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


History

VersionStatusNotes
10.0No ChangeCarried over from Panorama 6.0