Database

123 works best as an interactive workbook for entering, managing and querying moderate amounts of data.

However, 123 can be used as a simple interactive database!

This page works through an example of some of the database features.

Importing Data

123 can import comma (or tab) separated values, and parse plain text such as logfiles.

CSV

To import a CSV file, use /File Import Numbers.

All fields that are not numbers must be quoted, or 123 will discard them.

You can use the utility csvtool to reformat the CSV if necessary.

Format Lines

If you have a column of labels that you want converted into values, Format Lines tell 123 how to translate them.

Let's start with a simple example, a column that contains a label that should be a value.

In the screenshot below you can see the Marketcap column contains labels instead of values.

1. Move to the top of the column, and use /Data Parse Format Create, this will create a Format Line.
2. You can edit the format if you like, but in this case the default is correct.
3. Choose an Input-Column and Output-Range, and then choose Go
4. The column should be converted, you can now delete the Format Line.

Formats

Format lines can be used to parse complex input lines, and are especially useful with /File Import Text, as arbitrary plain text can be parsed.

Symbol	Description
L	Represents the first character of a label block.
V	Represents the first character of a value block.
D	Represents the first character of a date block.
T	Represents the first character of a time block.
S	Skips the data block below the symbol when parsing data.
>	Represents any character in a data block.
*	Represents a blank space between data blocks.

Tips

Two tools that you might find useful when importing datasets into 1-2-3 are csvtool and split.

csvtool

You can use csvtool to quote columns that should contain labels. For example,

$ csvtool format '"%2","%3","%5","%8"\n' dataset.csv > import.csv

split

You can use the standard split utility to break up a large dataset into sheets. For example,

$ split -l 8192 import.csv

Lookup Tables

3-D Ranges

Data Queries

Criteria

Linked Files

Linked files allows you to reference ranges from other worksheets in your formulas, the syntax is simply <<FILENAME>>RANGE.

Range can be a cell range like A:B12..A:C32, or a named range like TABLE.

Libraries

Here is a selection of useful library files you can use in your worksheets. To use them, simply place them in your default directory.

You can choose your default directory with /Worksheet Global Default Dir.

STOCKS

This is a collection of NASDAQ ticker symbol names, sector, and market cap.

https://lock.cmpxchg8b.com/files/stocks.wk3

Usage

• @VLOOKUP("GOOG",<<stocks>>$STOCKS,1) ; Returns "Alphabet Inc."
• @VLOOKUP("GOOG",<<stocks>>$STOCKS,2) ; Returns "Communication Services"

	A	B
1	Sector	Marketcap
2	Healthcare	>1e12

• @DSUM(<<stocks>>$STOCKS,3,A1..A2) ; Returns the total marketcap of all Healthcare companies.
• @DCOUNT(<<stocks>>$STOCKS,3,B1..B2) ; Returns the number of companies valued over $1 Trillion (1e12).