Multi-Value Field Normalization Prompt

This prompt is designed for data engineers and ETL developers who need to transform a single string containing multiple values into a clean, predictable array or delimited field for downstream ingestion. The ideal use case is a pipeline stage where you have already extracted a raw, delimited string from a source system (a CSV export, a legacy database column, a log file, or an API response) and you need to enforce a strict contract before loading it into a target data warehouse, search index, or operational database. The prompt handles the full normalization pipeline: delimiter detection, splitting that respects quoted substrings, whitespace trimming, deduplication, value-level normalization, and reassembly with a target delimiter. It also produces structured flags for ambiguous cases, making it suitable for pipelines that require a human review or quarantine queue for unparseable records.

Use this prompt when the source delimiter is unknown or inconsistent, when values may contain the delimiter character inside quotes, or when you need to apply a normalization rule (such as lowercasing, standardizing codes, or mapping to a canonical form) to each individual value after splitting. It is particularly valuable when you cannot rely on a simple SPLIT() function in your ETL tool because of these edge cases. For example, a field like "Austin, TX", "Dallas, TX"; Houston, TX contains mixed delimiters and quoted commas that would break a naive split. This prompt is designed to detect the primary delimiter, correctly parse the quoted values, and flag the mixed-delimiter anomaly in its output. You should also use it when you need an audit trail of what was cleaned, deduplicated, or normalized, as the prompt can be instructed to return both the final list and a change log.

Do not use this prompt for simple, predictable delimiters where a native function like STRING_SPLIT in SQL or .split(',') in Python is sufficient and you have full control over the input format. If your source system guarantees a single, consistent delimiter with no quoted values and no normalization requirement, a code-based solution will be faster, cheaper, and more deterministic. Similarly, avoid this prompt if you are dealing with structured formats like JSON arrays or XML lists that should be parsed with a dedicated library rather than treated as a delimited string. For high-volume streaming pipelines processing millions of records per second, the latency and cost of an LLM call may be prohibitive; in those cases, use this prompt to generate a robust parsing specification and then implement that specification in code. Finally, if the normalization rule requires a lookup against a massive, frequently updated reference dataset, it is more reliable to split and clean with this prompt and then perform the lookup in a subsequent, deterministic pipeline step rather than embedding the entire reference data in the prompt context.

This prompt template handles the normalization of a single delimited field containing multiple values. It detects the delimiter, splits the values, trims whitespace, deduplicates, normalizes each value according to your rules, and reassembles the list with a target delimiter. The template is designed to be pasted directly into your prompt layer—replace each square-bracket placeholder with your specific configuration before sending to the model.

textCopy
You are a data normalization engine. Your task is to standardize a multi-value field.

INPUT VALUE:
[INPUT_VALUE]

TARGET DELIMITER:
[TARGET_DELIMITER]

NORMALIZATION RULES:
[NORMALIZATION_RULES]

OUTPUT SCHEMA:
{
  "original_value": "string (the raw input)",
  "detected_delimiter": "string | null (the delimiter found: comma, semicolon, pipe, newline, tab, or null if single value)",
  "delimiter_confidence": "high | medium | low (confidence in delimiter detection)",
  "value_count": "integer (number of values after splitting)",
  "normalized_values": ["string"],
  "deduplicated": "boolean (whether duplicates were removed)",
  "parsing_ambiguities": ["string"] (list of any issues found, empty if none),
  "reassembled": "string (values joined with target delimiter)"
}

CONSTRAINTS:
- Detect the delimiter by analyzing the input. Common delimiters: comma, semicolon, pipe (|), newline, tab.
- If values are quoted (single or double), respect the quoting: do not split on delimiters inside quotes.
- Trim leading and trailing whitespace from each value after splitting.
- Remove exact duplicate values after trimming (case-sensitive deduplication by default).
- Apply the normalization rules to each individual value.
- Reassemble the normalized, deduplicated values using the target delimiter.
- Flag any parsing ambiguities: mixed delimiters, unbalanced quotes, empty values after trimming, delimiter appearing inside unquoted values.
- If no delimiter is detected, treat the entire input as a single value.
- Do not invent values. Do not fill gaps with assumptions.

EXAMPLES:
[EXAMPLES]

Return ONLY valid JSON. No markdown fences, no commentary.

To adapt this template, replace [INPUT_VALUE] with the raw field content, [TARGET_DELIMITER] with your desired output delimiter (e.g., , , ;, |), and [NORMALIZATION_RULES] with field-specific instructions such as 'lowercase all values', 'convert state abbreviations to full names', or 'map to canonical category codes'. The [EXAMPLES] placeholder should contain 2–4 few-shot demonstrations showing edge cases like quoted values containing delimiters, mixed delimiters, and duplicate detection. For high-risk ingestion pipelines, add a [RISK_LEVEL] placeholder that triggers additional validation flags or human-review routing when confidence is low.

Before deploying, validate the output JSON against your schema in the application layer. Common failure modes include the model missing a delimiter inside quoted values, normalizing values that should remain as-is, or failing to flag ambiguous inputs. Run this prompt against a golden test set that includes single values, empty strings, quoted delimiters, mixed delimiters, and near-duplicate values (e.g., trailing whitespace differences) to confirm the deduplication and trimming behavior matches your expectations.

This prompt is designed to be called as a single step within a broader data normalization pipeline. It expects a raw delimited string and a target delimiter, and it returns a structured JSON payload containing the normalized list, the detected source delimiter, and any parsing flags. The implementation harness should treat this prompt as a deterministic transformation function: same input and configuration should produce the same output. Therefore, set the model temperature to 0 and, where the provider supports it, use a structured output mode (e.g., JSON mode or function calling with a strict schema) to prevent the model from wrapping the result in explanatory text.

Before calling the prompt, the application layer should perform lightweight pre-validation: reject null or entirely empty inputs without wasting an API call, and check that the input string length is within the model's context window. After receiving the model's response, validate the JSON structure against the expected schema. Confirm that normalized_values is an array, that source_delimiter is a single character or the string 'mixed', and that parsing_flags is an array of known flag strings. If validation fails, implement a single retry with the error message appended to the prompt as a correction instruction. If the retry also fails, route the record to a dead-letter queue for human review rather than silently ingesting malformed data.

Logging and audit trail generation are critical for this workflow because delimiter detection can be ambiguous. For every record processed, store the original input, the detected delimiter, the normalized output array, and any parsing flags in an immutable audit log. This is especially important when the prompt flags ambiguous_delimiter or quoted_delimiter_conflict. Downstream systems consuming the normalized values should have access to these flags so they can decide whether to trust the split automatically or escalate for manual verification. For high-volume pipelines, consider batching multiple records into a single prompt call with clear record separators to reduce API overhead, but ensure each record's output is independently validatable.

Model choice matters here. This task requires precise instruction-following and structured output generation, not creative reasoning. Smaller, faster models (such as GPT-4o-mini, Claude Haiku, or Gemini Flash) are usually sufficient and will reduce cost and latency. Avoid models that are prone to adding conversational filler or refusing straightforward text-processing tasks. If your pipeline processes data that may contain PII or sensitive business information, ensure the model endpoint is configured for zero-data-retention and that your API calls do not log request payloads to observability tools that lack data masking. For on-premise or air-gapped deployments, this prompt works well with open-weight models that support structured output, but you should validate delimiter detection accuracy on a representative sample of your actual data before committing to a model.