CSV Contract Workbench

Define reusable YAML contracts for CSV files, edit them visually in VS Code, and run the same validations interactively or in unattended workflows.

Validator workflows in 0.15

Standalone, referenced and inline suite members share the visual contract editor. Add validation presets, edit nested predicates, capture and review versioned schema baselines, and execute full-scope rule previews. Explicitly enable Live tests to rerun valid draft changes; opening a contract never starts database queries. Suite controls include connection provenance and bulk editing, metadata preflight, selective reruns, searchable results, local history and same-connection cross-table checks. See Validator workflows for formats, defaults, examples and current limits; roadmap status identifies the remaining work. Baselines change only through explicit reviewed acceptance.

Build contracts visually

Create a contract from a CSV to start with its real column names, then set presence and data-quality rules from the workbench. Save one or more local paths or HTTP/HTTPS URLs as test targets when the same contract should run without prompting. Each configured CSV or SQL Server target has a compact Actions menu for enabling, disabling, editing, opening, or removing it. Disabled targets remain in YAML and are excluded from runs until re-enabled. URL targets open in a read-only VS Code document or in the browser. The YAML file remains the source of truth and can always be edited directly with schema-aware IntelliSense.

Configure required and optional columns, null handling, uniqueness, lengths, allowed values, and regular expressions.

See failures in context

Run a contract against a CSV and follow live per-target progress while large files are being checked. Review file, column, row, and cell failures directly below the run summary, before the column and row-test editors.

Choose Red / green or Gradient directly in the Latest results header. Gradient mode keeps PASS and FAIL semantics unchanged while categorizing results as Clean, Limited impact, Moderate impact, or High impact. On the run screen, the main progress bar changes to the result color when a target completes. Hover over a category or completed bar to see the underlying 0–100 health score and how it is calculated. A completely clean result is the only green state. The preference also applies to suite and workspace reports and is available as csvContract.resultColorMode in VS Code Settings.

Inspect the selected column and the latest validation result in one workspace.

Run workspace test suites

Running a suite opens a dedicated progress screen immediately. It rolls all test files into an overall suite bar, shows one member bar per referenced or inline contract, and displays compact live target progress beneath each member. Independent targets execute concurrently, and up to four test files run at once by default so large suites do not overwhelm the database. Change csvContract.suiteParallelMembers to tune that limit. Completed contract and suite runs offer Rerun failed when a target or test file fails.

Open CSV Contract in the Activity Bar to see every *.csvtest.yaml and *.csvtest.yml file in the workspace. Expand a contract to inspect and open its local or URL targets. Check the contracts you want and select Run Selected. A dedicated report opens with totals for the complete test run and expandable results for every contract-target pair. Select Last run to reopen the aggregate report, or select an individual green or red run to open the report focused on that result. Contracts that share a target reuse the same loaded CSV, and the selection remains checked for the workspace.

Compare CSV files semantically

Run CSV Contract: Compare CSV Files Semantically for an order-independent full-row multiset comparison or an explicit keyed comparison. Duplicate keys are surfaced without pairing ambiguous records. Trimming, case folding, blank/null equivalence, date and decimal normalization, ignored columns, keys, and context columns are all opt-in.

The aggregate result view keeps diagnostics redacted and bounded. Open complete normal-sized normalized results in the native VS Code diff editor, or save deterministic JSON, CSV, and Markdown evidence locally. The pure TypeScript engine runs in a real VS Code web host through vscode.workspace.fs; desktop automatically switches large local files to exact spill-to-disk comparison. See Semantic CSV comparison for limits, evidence files, and privacy guidance.

What you can validate

• Expected and optional columns, with control over undeclared extras
• Row and column counts
• Null values, uniqueness, minimum and maximum lengths
• Allowed values and regular-expression matches
• Composite identities across multiple columns
• Required or forbidden rows selected by exact values
• Exact cell values for selected rows
• Conditional row rules with nested predicates, cross-column checks, numeric comparisons, and warning severity
• Aggregate suite reconciliation and deterministic cross-source relationship checks across either CSV files or SQL Server tables, including filtered/ranked row sets, required third-source matches, portable key normalization, and target matrices that reuse logical contracts across environments
• Cross-row group completeness rules, such as requiring every employee/tax-area group to contain a full balance family
• Child contracts applied to each group or the whole input, with typed ordered event checks and bounded findings
• Exact header order when the receiving system requires a fixed layout
• Raw string values such as identifiers with leading zeroes

Generate SQL Server staging validation and validate tables

Use the same reviewed contract against SQL Server tables and views. A Windows integrated target can be declared directly with non-secret server and database names; SQL logins and custom connection strings continue to use a secret-backed named profile. In the Workbench, Add table or view uses a profile to browse objects. Execute the contract from the Workbench, Workspace Tests, or CSV Contract: Run Contract. Validation is read-only, runs translatable rules server-side, and feeds the normal report. JavaScript regex rules use an exact read-only client fallback.

sqlServer:
  integratedConnection:
    server: sqlhost\\instance
    database: Warehouse
    trustServerCertificate: true
  schema: staging
  table: PayrollImport
  objectType: table
  columnMap:
    EmployeeId: employee_id
    CompletionDate: completion_date
  rowLocator: [LoadId, SourceRow]
  detailLimit: 100
  scope:
    column: LoadId
    parameter: LoadId
    sqlType: nvarchar(100)

  conditionalRules:
    - id: completed-requires-date
      name: Completed rows have a completion date
      when:
        column: Status
        operator: equals
        value: Complete
      expect:
        column: CompletionDate
        operator: notNull
    - id: employee-category
      when:
        column: SourceType
        operator: equals
        value: Employee
      expect:
        column: Category
        operator: equals
        value: Labor

integratedConnection uses the Windows identity running the desktop extension or CLI and requires ODBC Driver 18 for SQL Server by default. Override odbcDriver when another installed driver is required. No connection profile is needed.

For SQL logins, use connection: warehouse-readonly. Connection strings are stored in VS Code Secret Storage, not contract YAML. The CLI resolves that profile from CSV_CONTRACT_SQLSERVER_WAREHOUSE_READONLY and supports repeated specs, multiple tables/connections, JSON output, and scoped runs:

node .\dist\cli\csv-contract.cjs dbtest --spec .\examples\sql-server-staging.csvtest.yaml --scope LoadId=2026-08-24

columnMap maps canonical contract names to physical SQL Server names for that target. CSV headers and report output continue to use the canonical names. The optional scope limits every check to one load or batch and binds the value as a query parameter. Server-side translation covers row counts, null, length, allowed-value, uniqueness, shared and SQL-specific conditional rules, row tests, and grouped completeness rules. Use sqlServer.targets to apply one contract to multiple tables, views, or connection profiles.

You can still generate a standalone read-only script with CSV Contract: Generate SQL Server Staging Validation or csv-contract sql. The script creates a rule summary plus bounded failing-row samples and does not connect automatically.

The bundled CLI supports unattended generation without a database connection:

node .\dist\cli\csv-contract.cjs sql `
  --spec .\payroll.csvtest.yaml `
  --out .\payroll.validation.sql

See SQL Server staging validation for the complete predicate reference and load-scoping behavior.

Import a known staging schema

Run CSV Contract: Import SQL Server Table Schema or select Import table schema in the Workbench. Choose an offline CREATE TABLE script, a Database Tracking *.structure.json table model, or a compact Database Knowledge snapshot. If the source contains several tables, select the one you want before the preview.

The preview reports added and existing columns, contract-only columns that will remain in place, inferred technical constraints, preserved conflicts, table identity changes, and primary-key identity initialization. Nothing is written until Import Schema is confirmed. Existing descriptions, allowed values, regexes, conditional rules, load scope, detail limit, identities, and manually reviewed constraint values are preserved.

Imported SQL type, nullability, length, precision, scale, identity, computed-column, and ordinal metadata remains under sqlServer.importedSchema. Identity and computed columns are optional in CSV validation because they may be generated after loading.

The bundled CLI supports the same offline merge:

node .\dist\cli\csv-contract.cjs schema `
  --source .\staging-table.sql `
  --spec .\payroll.csvtest.yaml `
  --out .\payroll.csvtest.yaml

Omit --spec to create a new contract. For a multi-table source, add --table staging.PayrollImport.

Get started

1. Install CSV Contract Workbench from the VS Code Marketplace.
2. Run CSV Contract: Create Contract from CSV from the Command Palette.
3. Review the generated *.csvtest.yaml file in the visual workbench or YAML editor.
4. Use the saved target, add more file paths or URLs, or choose temporary targets with Select test CSV.
5. Select Run tests to validate every active target.

The Workbench opens a run view while targets execute in parallel. It shows rows read and groups validated for each target; once a target has been read, grouped checks show progress against its known row count. Workbench runs retain all available issue details for export by default. Set csvContract.maxRetainedIssues in VS Code settings to a positive number if you want a retention cap. The results table shows the first 500 matching details while export includes every retained detail. SQL aggregate checks report failure counts rather than one detail for every matching row.

Generated contracts are deliberately conservative: they capture the observed columns and include sample row and cell assertions without guessing business rules. You decide which columns are required, unique, nullable, length-limited, or restricted to known values.

Reuse the same contract

Contracts are plain YAML files designed to live beside the data workflow they protect. A broad schema contract and focused spot-check contracts can be applied to the same CSV.

The included Node CLI and PowerShell wrapper use a streaming validator for unattended runs. Compatible contracts share one CSV pass, diagnostics remain bounded, and exact uniqueness checks spill to temporary disk instead of retaining the entire CSV in memory. Group rules are finalized only after EOF: normalized group observations are hash-partitioned to temporary disk during the stream, then reduced partition by partition so required companion rows may appear anywhere in the file.

Local by design

CSV contents and contracts are processed locally. CSV Contract Workbench does not upload validation data to a hosted service.

License

The extension code and documentation are licensed under Apache-2.0. CSV Contract Workbench and its product marks are Incursa brand assets and are not granted under that license.

Run from PowerShell

The installed extension includes the PowerShell wrapper and its bundled validation engine. Node must be available on PATH, but no separate tooling download is required.

Locate the currently installed extension and run every target configured in a contract:

$extensionRoot = (& code --locate-extension incursa.csv-contract-vsce).Trim()
$testScript = Join-Path $extensionRoot 'scripts\Test-CsvContract.ps1'

& $testScript `
  -Contract 'C:\path\to\customers.csvtest.yaml'

Run several contracts together:

& $testScript `
  -Contract @(
    'C:\path\to\customers-general.csvtest.yaml',
    'C:\path\to\customers-spot-checks.csvtest.yaml'
  )

Configured file paths and URLs are used automatically. Supply -Csv only when you want to override them for a particular run.

Compound suites

Use an ordered *.csvsuite.yaml master to reference contracts with different schemas and tables, or combine them into a portable inline bundle. Both forms run through Workspace Tests and the CLI. See Compound suites for formats, connection precedence, combine/split commands, round-trip guarantees and execution reporting.

Save and reload comparisons

Version 0.15.3 adds Save comparison setup in comparison results and CSV Contract: Load Comparison Setup in the Command Palette. Shared keyed CSV setups can also run in SSMS Database Tools 0.27.5. See the format and compatibility guide for supported conversions, relative paths, SQL limitations and legacy options.