---
title: "Detect column types with Auto Field in Strata | Oplema"
description: "Detect and convert column types with Auto Field. It checks every value, so codes with leading zeros stay text, and it can read 0 and 1 as booleans."
url: "https://oplema.com/docs/strata/prep/auto-field"
updated: "2026-11-04"
---

# Auto Field

Detect what text columns really contain, such as numbers or dates, and convert them to that type. Whole-number columns are narrowed to BIGINT, and numeric 0 and 1 can optionally become booleans. Unchecked columns are left as they are.

**Learn this in context:** [More data tools: when you'd use each one](https://oplema.com/learn/table-to-dashboard/more-data-tools) (Build and publish a dashboard)

## Detect column types

Manual Input supplies this order table; Select converts its columns to text before Auto Field. Auto Field detects numbers, booleans, dates and timestamps, while the codes and mixed quoted amounts stay as text.

Result
| code | quantity | amount | paid | order\_date | received\_at | quoted\_amount |
| --- | --- | --- | --- | --- | --- | --- |
| 001 | 2 | 12.5 | true | 2026-09-07 | 2026-09-07 09:15:00 | 12.50 |
| 002 | 1 | 8 | false | 2026-09-08 | 2026-09-08 14:30:45.123 | pending |
| 003 | 3 | 6.25 | true | 2026-09-09 | 2026-09-09 00:00:00 | 6.25 |

1. Clear the Infer type checkbox for `quantity`. The column stays in the output, but its type changes from `BIGINT` back to `VARCHAR`. Select Infer type again to restore numeric quantities.
2. Compare `order_date` with `received_at`. The first becomes `DATE`; the second becomes `TIMESTAMP` and keeps its time of day, including fractional seconds.
3. Look at `quoted_amount`: it mixes numeric text with `pending`, so the entire column stays `VARCHAR`. Select the Manual Input block and replace `pending` with `8.00`. Select Auto Field again: `quoted_amount` now becomes `DOUBLE`.
4. Look at the `code` column. Values such as `001` stay `VARCHAR` text with their leading zeros, even with Infer type selected.

## When to use

Use Auto Field when numbers and dates arrive as text and you want Strata to work out the types. To set a type yourself, use Select. Leave identifiers with leading zeros as text.

## Read the output

The example starts with three orders. Select converts Manual Input's inferred columns to **VARCHAR** text before Auto Field. Auto Field keeps the same rows, column names and the configured grid order, and changes the types of selected text columns when their values match.

Each column shows a different result:

- `code` stays **VARCHAR** text, preserving leading zeros in `001`, `002` and `003`.
- `quantity` becomes **BIGINT**, with whole numbers `2`, `1` and `3`.
- `amount` becomes **DOUBLE**, with decimal numbers `12.5`, `8` and `6.25`.
- `paid` becomes **BOOLEAN**, with `true` and `false` values.
- `order_date` becomes **DATE**, with dates from `2026-09-07` to `2026-09-09`.
- `received_at` becomes **TIMESTAMP**, preserving times such as `09:15:00`, fractional seconds in `14:30:45.123` and midnight in `00:00:00`.
- `quoted_amount` stays **VARCHAR** because it mixes `12.50` and `6.25` with the word `pending`.

The mixed `quoted_amount` column stays entirely as text; Auto Field doesn't convert just its numeric cells or replace `pending` with NULL. If you replace `pending` with `8.00`, every value can become a number and the column becomes **DOUBLE**. Auto Field changes its own output; the values from Select stay text.

## How Auto Field chooses a type

For each selected **VARCHAR** column, Auto Field checks all non-NULL values for **BOOLEAN**, then **BIGINT**, then **DOUBLE**, then **DATE**, then **TIMESTAMP**. It uses the first matching type.

Date-only values such as `2026-09-09` can match DATE. Values with an explicit time, such as `2026-09-09 09:15:00`, are skipped for DATE so they can become TIMESTAMP and keep their time. A column mixing dates and timestamps becomes TIMESTAMP when all its non-NULL values can be converted; the date-only values use midnight. TIMESTAMP doesn't store a timezone offset.

If no candidate matches every non-NULL value, the column stays as text. NULL values don't block conversion and stay NULL. A column with no non-NULL values keeps its current type.

Integer-like strings with leading zeros, such as `001` or `007`, are skipped for numbers so those codes stay text. Detection runs each time the block runs, so a change in the input can change the output type.

For numbers with a known locale or formatting, use [Number](https://oplema.com/docs/strata/parse/number). For dates with a known format, use [DateTime](https://oplema.com/docs/strata/parse/datetime).

To choose a specific output type yourself, use [Select](https://oplema.com/docs/strata/prep/select).

## Inputs and outputs

### Inputs

1 port

**In Required**

Accepts: Tables, Geospatial data.

Accepts one connection on this port.

### Outputs

1 port

**Out**

## Configure the block

The Fields grid lists the columns from the connected input. Choose which ones Auto Field should inspect. A chosen column missing from the input is skipped with a warning, and its settings are kept.

**Treat 0/1 as booleans**

Convert selected numeric columns containing only 0 and 1 to booleans, including numeric text. This also affects binary counts and identifiers. NULLs are preserved.

**Column**

Identifies the input column. Its name stays the same; list order determines output column order.

**Infer type**

Select to infer text or numeric types. Clear to preserve the column's values and type. New input columns are selected automatically.

## Auto Field FAQs

**Does Auto Field need an input connection?**

Yes. Connect the table to In. Auto Field keeps rows and column names, and the Fields grid sets the output column order.

**Which types can Auto Field detect?**

Selected text columns can become BOOLEAN, BIGINT, DOUBLE, DATE or TIMESTAMP. Whole-number FLOAT, DOUBLE and DECIMAL columns become BIGINT when conversion keeps every value exactly and fits its range. Other typed columns pass through unless numeric Boolean inference is on.

**Will timestamps keep their time of day?**

Yes. Text with a time in it, even midnight, is checked for TIMESTAMP rather than DATE. A column mixing dates and timestamps becomes TIMESTAMP if every non-NULL value can be converted; date-only values then use midnight. TIMESTAMP doesn't store a timezone offset.

**Why did my numeric-looking column stay as text?**

Every non-NULL value must fit the type. Mixed text, codes with leading zeros and whole numbers outside the BIGINT range all keep a column as text.

**Do missing values prevent conversion?**

NULL values are ignored when detecting types and stay NULL in the output. A column with only NULL values keeps its current type.

**Does Auto Field inspect just a sample?**

No. It checks all non-NULL values in each selected text column. If the input values change, the inferred type can change on a later run.

**Can zero and one become booleans?**

Turn on Treat 0/1 as booleans to read selected numeric columns and numeric text as true/false flags. Every non-NULL value must be exactly 0 or 1, and a column of all 0s or all 1s qualifies too. It's off by default because counts and IDs can also hold these values.

**Does numeric inference keep every value exact?**

Existing numeric columns narrow only when conversion is exact. Fractional text uses normal DOUBLE approximation. Empty and all-NULL columns keep their types; negative zero, NaN, infinities and fractional numeric values aren't narrowed to integers.

### Prepare your data. Build it in Strata.

Strata opens in your browser, with no account.

[Open Strata](https://oplema.com/editor?path=%2F%3Fopen%3Dstrata) [About Strata](https://oplema.com/strata)
