`EditTsvWithDiff` — paste TSV, validate, review diff, confirm

EditTsv

Try on Binder

These docs requires a python kernel to run. Try on Binder

This notebook showcases two widgets for working with tabular data using ipyautoui:

EditTsvWithDiff: paste TSV/CSV-style text (e.g., from Excel), validate against a Pydantic model, review changes with DeepDiff, then confirm or cancel.
EditTsvFileUpload: upload an Excel (.xlsx) file; data is parsed/validated just like the text editor version, but the text area is disabled.
EditTsvWithDiffAndKeyMapping: Similar to EditTsvWithDiff, but instead of diffing being done based on a primary key, a composite key is used, based on the unique_fields trait. It also modifies the pydantic model to exclude the fields specified in the exclude_fields_from_model trait.

In all cases you must ensure the Pydantic model (schema) matches the expected file columns and constraints.

Define the Pydantic schema

We declare a Test model for one row and a TestArray root model (list of rows).
Key points:

Numeric constraints (e.g., ge=1, le=10) are captured in the schema and used for validation and Excel data validation.
Enums (e.g., MyColor) become dropdowns in Excel.
b_calcfloat is frozen/read-only and can include a formula hint (used by the Excel writer).
json_schema_extra carries UI hints (e.g., section, formula, datagrid_index_name).

The same schema is used by both widgets.

# Imports and schema
from typing import Optional, List, Literal
from datetime import date, datetime, time, timedelta
from enum import Enum
from pydantic import BaseModel, Field, RootModel, ConfigDict, StringConstraints, conint, constr
from ipyautoui.custom.edittsv import EditTsvWithDiff, EditTsvFileUpload
from ipyautoui.custom.edittsv_with_diff_and_key_mapping import EditTsvWithDiffAndKeyMapping
from ipyautoui.custom.fileupload import TempFileUploadProcessor
from typing_extensions import Annotated
import xlsxdatagrid as xdg
import pathlib
import ipywidgets as w
from IPython.display import display

class MyColor(str, Enum):
    red = 'red'
    green = 'green'
    blue = 'blue'

class Test(BaseModel):
    a_constrainedint: Annotated[int, Field(ge=1, le=10)] = Field(
        3,
        title="A Constrainedint",
        json_schema_extra=dict(section="numeric"),
    )

    a_int: Optional[int] = Field(
        1,
        title="A Int",
        json_schema_extra=dict(section="numeric"),
    )

    b_calcfloat: Optional[float] = Field(
        None,
        title="B Calcfloat",
        description="calc value",
        frozen=True,
        json_schema_extra=dict(section="numeric", formula="a_int * b_float"),
    )

    b_float: Optional[float] = Field(
        1.5,
        title="B Float",
        json_schema_extra=dict(section="numeric"),
    )

    c_constrainedstr: Annotated[str, StringConstraints(min_length=0, max_length=20)] = Field(
        "string",
        title="C Constrainedstr",
        json_schema_extra=dict(section="unicode"),
    )

    c_str: Optional[str] = Field(
        "string",
        title="C Str",
        json_schema_extra=dict(section="unicode"),
    )

    d_enum: MyColor = Field(
        "red",
        title="D Enum",
        json_schema_extra=dict(section="unicode"),
    )

    e_bool: Optional[bool] = Field(
        True,
        title="E Bool",
        json_schema_extra=dict(section="boolean"),
    )

    f_date: Optional[date] = Field(
        "2024-06-06",
        title="F Date",
        json_schema_extra=dict(section="datetime"),
    )

    g_datetime: Optional[datetime] = Field(
        "2024-06-06T10:42:54.822063",
        title="G Datetime",
        json_schema_extra=dict(section="datetime"),
    )

    h_time: Optional[time] = Field(
        "10:42:54.822257",
        title="H Time",
        json_schema_extra=dict(section="datetime"),
    )

    i_duration: Optional[timedelta] = Field(
        "PT2H33M3S",
        title="I Duration",
        json_schema_extra=dict(section="datetime"),
    )

    model_config = ConfigDict(
        title="Test",
        json_schema_extra=dict(required=["d_enum", "b_calcfloat"]),
    )

class TestArray(RootModel[List[Test]]):
    model_config = ConfigDict(
        title="TestArrayTransposed",
        json_schema_extra=dict(datagrid_index_name=("section", "title", "name")),
    )
    root: List[Test]

What it does - Accepts TSV text (e.g., copy from Excel → paste). - Parses and validates rows using TestArray. - On Save (💾), shows a DeepDiff view of changes. - You can confirm ✅ to finalize (and call your fn_upload), or cancel 🚫 to go back and edit.

How to use 1. Paste TSV data into the text area. The first row should be the headers derived from your schema. 2. Click the save button. 3. Review the diff; click ✔️ to confirm or ⛔ to cancel.

Make sure primary_key_name, header_depth, and transposed match how your data is structured.

# Example: EditTsvWithDiff
edit_tsv_w_diff = EditTsvWithDiff(
    model=TestArray,
    transposed=False,
    primary_key_name="a_int",
    header_depth=3,
    exclude_metadata=True,
)
display(edit_tsv_w_diff)

`EditTsvFileUpload` — upload Excel, validate, review diff, confirm

What it does - Disables manual text editing. - Lets you upload a .xlsx file. - Parses and validates rows using TestArray. - On Save, shows a DeepDiff of changes before confirming.

How to use 1. Click the upload button to pick an Excel file. 2. The widget reads the sheet using the same schema validations. 3. Click save to review and confirm changes.

The Excel must match the schema (column names and constraints). If your schema is transposed in display, set transposed=True.

# Example: EditTsvFileUpload
edit_tsv_upload = EditTsvFileUpload(
    model=TestArray,
    transposed=True,
    primary_key_name="a_int",
    header_depth=3,
    exclude_metadata=True,
)
display(edit_tsv_upload)

# Example: EditTsvWithDiffAndKeyMapping
value= [
    {
        'a_int': '1',
        'a_constrainedint': '3',
        'b_float': '1.5',
        'c_str': 'string',
        'c_constrainedstr': 'string',
        'd_enum': 'green',
        'e_bool': 'TRUE',
        'f_date': '2025-11-03',
        'g_datetime': '2025-11-03T13:17:53+00:00',
        'h_time': '13:17:53+00:00',
        'i_duration': 'PT2H33M03S',
        'b_calcfloat': '1.5'
    },
    {
        'a_int': '2',
        'a_constrainedint': '3',
        'b_float': '2.5',
        'c_str': 'asdf',
        'c_constrainedstr': 'string',
        'd_enum': 'green',
        'e_bool': 'TRUE',
        'f_date': '2025-11-03',
        'g_datetime': '2025-11-03T13:17:53+00:00',
        'h_time': '13:17:53+00:00',
        'i_duration': 'PT2H33M03S',
        'b_calcfloat': '5'
    },
    {
        'a_int': '3',
        'a_constrainedint': '3',
        'b_float': '3.5',
        'c_str': 'bluey',
        'c_constrainedstr': 'string',
        'd_enum': 'blue',
        'e_bool': 'FALSE',
        'f_date': '2025-11-03',
        'g_datetime': '2025-11-03T13:17:53+00:00',
        'h_time': '13:17:53+00:00',
        'i_duration': 'PT2H33M03S',
        'b_calcfloat': '10.5'
    }
]

edit_tsv_w_diff_and_key_mapping = EditTsvWithDiffAndKeyMapping(
    model=TestArray,
    value=value,
    transposed=False,
    unique_id_fields=["c_str", "c_constrainedstr", "d_enum"],
    exclude_metadata=True,
)
display(edit_tsv_w_diff_and_key_mapping)

Notes & Troubleshooting

If constraints (e.g., enum dropdowns or min/max) don’t show up in Excel exports, check that your Pydantic fields carry the appropriate constraints (e.g., Field(ge=..., le=...), Enum, or StringConstraints).
frozen=True on a field makes it read-only and is treated as readOnly in JSON Schema.
Ensure your headers match the schema; mis-ordered or missing headers will produce validation errors.
primary_key_name should point to a unique field in your data to show meaningful row-level diffs.

EditTsv

Define the Pydantic schema

EditTsvFileUpload — upload Excel, validate, review diff, confirm

Notes & Troubleshooting

`EditTsvFileUpload` — upload Excel, validate, review diff, confirm