Column Types & Data Detection

One of DB Table's most powerful features is its ability to automatically detect and format different types of data. This guide explains how the detection system works and how to work with each supported data type.

Automatic Type Detection

How It Works

DB Table analyzes your data using a sophisticated detection algorithm:

1. Sample Analysis - Examines the first 20 values in each column
2. Pattern Recognition - Identifies common data patterns and formats
3. Validation - Verifies detected types against the entire column
4. Confidence Scoring - Assigns confidence levels to each detection
5. Fallback Logic - Defaults to safer types when uncertain

Detection Priority

The system follows a specific order to avoid false positives:

1. Boolean - Checked first (most specific)
2. Numbers - Simple integers and decimals
3. Currency - Numbers with currency symbols
4. Percentage - Numbers with % symbols or 0-1 range
5. Dates - Various date formats with validation
6. String - Default fallback for everything else

Supported Column Types

🔤 String (Text)

Purpose: General text data, names, descriptions, categories

Detection Criteria:

• Contains non-numeric characters
• Mixed alphanumeric content
• Default fallback type

Examples:

"John Smith"
"Product Description"
"Category A"
"Mixed123Content"

Formatting: Displayed as-is with no special formatting

🔢 Number

Purpose: Numeric data, quantities, measurements, IDs

Detection Criteria:

• Contains only digits and decimal points
• Matches pattern: -?\d+(\.\d+)?
• No currency symbols or percentage signs

Examples:

42
-17.5
1234.56
0.001

Formatting:

• Decimal places preserved
• Negative numbers shown with minus sign
• Large numbers may include thousand separators

📅 Date

Purpose: Dates, timestamps, temporal data

Detection Criteria:

• Matches common date patterns
• Creates valid Date objects
• Year between 1900-2100 (reasonable range)

Supported Formats:

2024-01-15        (ISO format)
01/15/2024        (US format)
15/01/2024        (European format)
Jan 15, 2024      (Long format)
2024-01-15T10:30  (ISO with time)

Formatting:

• Standardized display format
• Timezone handling
• Invalid dates shown as "Invalid Date"

✅ Boolean (Yes/No)

Purpose: True/false values, yes/no questions, binary states

Detection Criteria:

• All values match boolean patterns
• Case-insensitive matching

Recognized Values:

true/false
yes/no
y/n
1/0
True/False
YES/NO

Formatting:

• Displayed as "Yes" or "No"
• Consistent capitalization
• Color-coded indicators

📊 Percentage

Purpose: Rates, ratios, percentages

Detection Criteria:

• Values end with % symbol
• Numeric values between 0-1 (converted to percentages)
• All values are numeric when % is removed

Examples:

25%
0.75    (displayed as 75%)
100%
-5.5%

Formatting:

• Always displays % symbol
• Decimal places preserved
• Values 0-1 automatically converted to percentages

💰 Currency

Purpose: Monetary values, prices, costs

Detection Criteria:

• Starts with currency symbols ($, €, £, ¥, etc.)
• Numeric content after symbol removal
• Consistent currency symbol across column

Supported Symbols:

$ (Dollar)
€ (Euro)
£ (Pound)
¥ (Yen)
₹ (Rupee)
₽ (Ruble)
₩ (Won)
R$ (Real)

Examples:

$25.99
€1,234.56
£99.00
¥500

Formatting:

• Currency symbol preserved
• Decimal places for cents/pence
• Thousand separators for large amounts

Manual Type Override

When to Override

Consider manual overrides when:

• Automatic detection is incorrect - Mixed data confuses the algorithm
• You need specific formatting - Force currency display for plain numbers
• Data context matters - ID numbers should stay as text to preserve leading zeros
• Consistency is important - Standardize similar columns across sheets

How to Change Column Types

1. Locate the column you want to change
2. Click the type indicator next to the column name
3. Select new type from the dropdown menu
4. Formatting updates immediately across all cells
5. Reset option available to return to auto-detected type

Type Change Effects

Advanced Detection Features

Column Name Recognition

DB Table recognizes common column names and applies appropriate types:

Mixed Data Handling

When columns contain mixed data types:

1. Majority rules - Most common type is selected
2. Conservative approach - Defaults to String when uncertain
3. Manual review - AI profiler can suggest better types
4. Validation warnings - Highlights potential issues

Performance Optimization

For large datasets:

• Sample-based detection - Analyzes subset for speed
• Lazy validation - Full column validation on demand
• Caching - Remembers detection results
• Progressive enhancement - Improves accuracy over time

Best Practices

Preparing Data for Better Detection

1. Consistent formatting - Use the same format throughout each column
2. Clear headers - Use descriptive column names
3. Clean data - Remove extra spaces and formatting
4. Separate mixed types - Split columns with multiple data types

Optimizing Type Detection

1. Review AI suggestions - Use the AI profiler for better accuracy
2. Manual verification - Check auto-detected types for critical data
3. Test with samples - Verify detection with representative data
4. Document decisions - Note why you chose specific type overrides

Common Pitfalls

Leading zeros disappear

• Problem: ID numbers like "001" become "1"
• Solution: Keep as String type to preserve formatting

Dates not recognized

• Problem: Unusual date formats aren't detected
• Solution: Standardize format or use manual override

Currency without symbols

• Problem: Plain numbers that represent money
• Solution: Manually change to Currency type

Percentages as decimals

• Problem: 0.25 should be 25%
• Solution: Change to Percentage type (auto-converts)

Type Validation & Errors

Validation Rules

Each type has specific validation rules:

• Numbers: Must be valid numeric values
• Dates: Must create valid Date objects
• Booleans: Must match recognized boolean values
• Currency: Must be numeric after symbol removal
• Percentages: Must be numeric values

Error Handling

When validation fails:

• Error indicators show in affected cells
• Tooltip messages explain the issue
• Fallback display shows original value
• Edit mode allows correction

Next steps: Learn how to use the AI Data Profiler for even better type detection, or explore data editing to work with your properly typed data.