CSV Field Count Mismatch Repair Prompt

This prompt is for data engineers and integration developers who receive LLM-generated CSV outputs that downstream ETL pipelines, databases, or analytics tools reject due to inconsistent row lengths. The core job is to take a malformed CSV string, a known header row, and a set of repair constraints, and produce a clean, valid CSV where every row has the correct number of fields. The prompt also generates a structured repair log so operators can audit which rows were altered and why. Use this when your validation layer detects field count mismatches, quote-escaping errors, or misaligned columns that prevent ingestion—not for general CSV formatting or initial generation.

The prompt requires three concrete inputs: the raw CSV string with mismatched rows, the expected header row as a comma-separated list, and a set of repair rules such as whether to drop extra fields, pad missing fields with nulls, or attempt semantic realignment using column name similarity. It works best when the header is stable and known ahead of time. If the CSV has no header, or the header itself is ambiguous, this prompt is not the right tool—use a schema inference prompt first. The repair log output must include the row index, the original field count, the corrected field count, the action taken (drop, pad, realign), and a confidence flag for any semantic mapping decisions.

Do not use this prompt for CSVs with structural corruption beyond field count mismatches, such as binary encoding errors, multi-line field values that break row parsing, or delimiter collisions inside quoted fields. Those failures require pre-processing at the parsing layer before this prompt can operate. Also avoid this prompt when the CSV is too large to fit in a single context window—chunk the file and process it in batches, or use a streaming repair approach. In regulated environments where CSV data feeds compliance or financial systems, always route the repair log and the corrected CSV through human review before ingestion. Wire this prompt into a retry harness that validates the output row counts against the header length before accepting the repair.

This prompt template is designed to be dropped into a repair harness that feeds a malformed CSV string, its expected header row, and any validation errors. The model's job is to produce a clean, valid CSV and a structured repair log. All dynamic inputs are represented as square-bracket placeholders, making the template easy to parameterize in code before sending it to the model. The instructions force the model to reason about each row's structure against the header before making changes, preventing silent data corruption.

textCopy
You are a precise CSV repair engine. Your task is to analyze a malformed CSV input, detect rows where the number of fields does not match the expected header count, and produce a corrected CSV along with a structured repair log.

## INPUTS
- Raw CSV: [RAW_CSV]
- Expected Header: [EXPECTED_HEADER]
- Delimiter: [DELIMITER]
- Quote Character: [QUOTE_CHAR]
- Validation Errors (optional): [VALIDATION_ERRORS]

## CONSTRAINTS
- Do not modify the header row.
- Preserve all original data values unless a destructive fix is required. If a destructive fix is necessary, flag it in the repair log.
- Handle quote-escaping errors: unescaped quotes inside quoted fields, mismatched quote pairs, and embedded delimiters.
- For rows with too many fields: merge the last N fields into the final expected column, using the delimiter to join them, unless the content suggests a different structural error.
- For rows with too few fields: pad missing trailing fields with empty strings.
- If a row cannot be repaired with high confidence, prepend it with a comment character (#) and document the reason in the repair log.
- Do not invent data to fill gaps.

## OUTPUT_SCHEMA
You must return a valid JSON object with the following structure:
{
  "repaired_csv": "<full corrected CSV string with newlines escaped as \n>",
  "repair_log": [
    {
      "row_index": <0-based integer of the data row, excluding header>,
      "original_field_count": <integer>,
      "expected_field_count": <integer>,
      "issue": "<one of: TOO_MANY_FIELDS, TOO_FEW_FIELDS, QUOTE_ERROR, UNREPAIRABLE>",
      "action_taken": "<brief description of the fix applied>",
      "confidence": "<HIGH | MEDIUM | LOW>"
    }
  ],
  "unrepairable_rows": <integer count of rows that were commented out>
}

## EXAMPLES
Example Input:
Raw CSV: "Name,Age,City\nJohn,30,New York\nJane,25\nBob,40,Boston,Extra"
Expected Header: "Name,Age,City"
Delimiter: ","
Quote Character: "\""

Example Output:
{
  "repaired_csv": "Name,Age,City\nJohn,30,New York\nJane,25,\nBob,40,Boston,Extra",
  "repair_log": [
    {
      "row_index": 1,
      "original_field_count": 2,
      "expected_field_count": 3,
      "issue": "TOO_FEW_FIELDS",
      "action_taken": "Padded missing trailing field with empty string.",
      "confidence": "HIGH"
    },
    {
      "row_index": 2,
      "original_field_count": 4,
      "expected_field_count": 3,
      "issue": "TOO_MANY_FIELDS",
      "action_taken": "Merged last 2 fields into final column 'City'.",
      "confidence": "MEDIUM"
    }
  ],
  "unrepairable_rows": 0
}

## RISK_LEVEL
[HIGH | MEDIUM | LOW]

If RISK_LEVEL is HIGH, you must be conservative: comment out any row you are not certain about and set confidence to LOW.

Now repair the provided CSV.

To adapt this template, replace each square-bracket placeholder with the corresponding runtime value. The [RAW_CSV] should be the exact malformed string, not a file path. If your upstream validator already provides structured error messages, pass them into [VALIDATION_ERRORS] to give the model additional context. The [RISK_LEVEL] parameter acts as a global behavior switch: set it to HIGH for financial, healthcare, or compliance data where a false repair is worse than a rejected row. For low-stakes data cleanup, LOW allows the model to make reasonable inferences. Always validate the model's output JSON against the OUTPUT_SCHEMA before accepting the repaired CSV, as the model may occasionally return the CSV in an unexpected escaping format.

The CSV Field Count Mismatch Repair Prompt is designed to sit inside a data ingestion pipeline as a post-generation repair step, not as a standalone interactive tool. After an LLM generates CSV output—or after a file passes through an extraction workflow—a validator checks each row's field count against the expected header length. Rows that fail this check are batched and sent to the repair prompt along with the header row and any available schema context. The prompt returns a corrected CSV and a structured repair log, which your harness must parse, validate, and merge back into the clean dataset before downstream consumption.

To wire this into an application, implement a three-stage harness: detect, repair, and verify. In the detection stage, parse the raw CSV with a robust library that handles quote-escaping edge cases (Python's csv module with QUOTE_MINIMAL is a safe default). Count fields per row against the header length. Rows with mismatched counts are extracted into a mismatched_rows array, while clean rows pass through to a staging buffer. In the repair stage, construct the prompt request by injecting the header, the mismatched rows, and any known field descriptions or expected data types into the [HEADER], [MISMATCHED_ROWS], and [SCHEMA_HINTS] placeholders. Set [MAX_REPAIR_ATTEMPTS] to a low number like 2 or 3—CSV repair is deterministic enough that repeated retries rarely improve results and often indicate a deeper structural problem. In the verification stage, parse the model's output CSV and validate that every repaired row now has the correct field count. Rows that still fail after the retry budget is exhausted should be logged to a dead-letter queue with the original raw text, the repair attempt history, and a reason code (FIELD_COUNT_STILL_MISMATCHED, QUOTE_ESCAPE_UNRESOLVED, SEMANTIC_DRIFT_DETECTED).

Model choice matters here. CSV repair is a structured, low-creativity task where smaller, faster models like Claude 3.5 Haiku, GPT-4o-mini, or open-weight models fine-tuned on data-cleaning tasks often perform as well as larger models at a fraction of the cost and latency. If your pipeline processes high-volume CSV streams, consider batching multiple mismatched-row groups into a single prompt request to reduce API call overhead, but keep batches small enough that the model doesn't confuse row boundaries—20 to 50 rows per request is a practical starting range. Always log the repair log returned by the prompt alongside the corrected CSV. This log is your audit trail: it records which rows were altered, what changes were made, and the model's confidence in each repair. For high-risk data pipelines (financial transactions, clinical records, compliance filings), route any row where the repair confidence is below a threshold to a human review queue before merging into the clean dataset.

Avoid wiring this prompt directly into a streaming CSV parser without buffering. Streaming row-by-row repair introduces ordering complexity and makes it harder to detect systemic issues like a misconfigured upstream extractor that is consistently producing 12-field rows for an 11-field header. Instead, accumulate mismatched rows over a configurable window—by row count, time interval, or file segment—and repair them in batches. If the same repair pattern appears across multiple batches (e.g., every row has an extra trailing comma), your harness should surface that as an operational alert rather than silently repairing it forever. The prompt is a recovery tool, not a permanent fix for broken upstream generation.