CSV Normalization Prompt for Data Warehouse Loads

This prompt is for analytics engineers and data pipeline builders who need to transform unstructured or semi-structured text into a clean, well-formed CSV file ready for direct ingestion into a data warehouse via COPY, INSERT, or bulk load utilities. The job-to-be-done is strict normalization: taking a messy document, log, report, or transcript and producing a single CSV output with consistent quoting, escaped delimiters, aligned headers, and safe encoding. The ideal user already knows their target table schema and needs the AI to handle the tedious, error-prone work of text-to-CSV conversion without introducing silent corruption that breaks downstream ETL.

Use this prompt when the source text contains multiple records that map to a known set of columns, when the output must survive automated ingestion without manual cleanup, and when encoding issues, embedded delimiters, or inconsistent newlines could otherwise cause load failures. It is particularly valuable for daily batch jobs, one-off data migrations, and any pipeline where a human previously spent time manually aligning text to CSV format. The prompt includes explicit instructions for RFC 4180 compliance, UTF-8 encoding, and newline safety—details that general-purpose extraction prompts often omit.

Do not use this prompt when the source text is a single unstructured blob with no clear record boundaries, when the output schema is deeply nested and better suited to JSON or Parquet, or when the data volume exceeds a single model context window and requires a chunking strategy. This prompt is not a substitute for a proper ETL framework when transformations involve complex business logic, multi-table normalization, or streaming data. It is also inappropriate for regulated data that requires per-field audit trails and confidence scores—use the confidence-annotated payload or source lineage annotation prompts instead. If your warehouse expects a specific binary format like Avro or Protobuf, start with the Avro Schema Extraction or Protobuf Message Extraction playbooks, then convert.

Before using this prompt, confirm your target column list, delimiter choice, quoting rules, and encoding requirements. The prompt template includes placeholders for these constraints so you can lock them in before generation. After running the prompt, always validate the output with a CSV linter or a test COPY statement against a staging table before promoting to production. The next section provides the copy-ready template and explains how to adapt each placeholder to your ingestion contract.

This template is the core instruction set you'll send to the model. It is designed to be copied directly into your prompt management system, IDE, or orchestration code. The prompt forces the model to act as a strict CSV serializer, not a conversational assistant. It separates the extraction logic from the formatting logic, ensuring the output is a valid, load-ready CSV string every time. Before using it, you must populate the placeholders with your specific target schema, input text, and any business rules for normalization.

textCopy
You are a strict CSV serialization engine. Your only job is to convert the provided unstructured text into a single, clean CSV output that matches the target schema exactly. Do not add commentary, explanations, markdown fences, or conversational text.

### TARGET SCHEMA
You must produce a CSV with a header row followed by one or more data rows. The columns, in order, are:
[COLUMN_NAMES]

For each column, adhere to these type and normalization rules:
[COLUMN_RULES]

### INPUT TEXT
Below is the unstructured text to extract data from:
[INPUT_TEXT]

### FORMATTING RULES
1.  **Delimiter:** Use a comma (`,`) as the field delimiter.
2.  **Quoting:** Wrap any field containing a comma, a double quote, or a newline in double quotes (`"`).
3.  **Escaping:** Escape any double quote character within a quoted field by doubling it (`""`).
4.  **Newlines:** All newlines within a field must be preserved inside the quoted field. The only unquoted newlines should be the row separators.
5.  **Header:** The first row must be the exact column names as specified in [COLUMN_NAMES].
6.  **Nulls:** Represent a SQL NULL value with an empty, unquoted field (e.g., `col1,,col3`). Do not use the string "NULL" or "None".
7.  **Encoding:** Output must be valid UTF-8. Replace or remove any characters that cannot be encoded.
8.  **Trailing Newline:** The output must end with a single newline character.

### OUTPUT
[CSV_OUTPUT]

To adapt this template, replace the square-bracket placeholders with concrete values. [COLUMN_NAMES] should be a comma-separated list like id,full_name,email,signup_date,total_spend. [COLUMN_RULES] must be a detailed, line-by-line specification for each column, for example: - id: A UUID. If missing, generate a new v4 UUID. - full_name: Title Case. - email: Lowercase. If missing, leave NULL. - signup_date: ISO 8601 format (YYYY-MM-DD). If unparseable, leave NULL. - total_spend: A decimal number with two places (e.g., 100.00). Remove currency symbols. If missing, default to 0.00. The [INPUT_TEXT] placeholder is where you inject the document, email, or log snippet. The [CSV_OUTPUT] token at the end is a strong positional anchor, instructing the model to begin the CSV immediately after that label. For high-stakes financial or healthcare data, add a [RISK_LEVEL] constraint like: If any value is ambiguous or unparseable, leave the field NULL and do not guess.

This prompt is designed to be the final normalization step before raw extracted text hits a COPY or INSERT statement in your data warehouse. It should sit inside a data pipeline stage that receives unstructured or semi-structured text, passes it through the prompt, validates the resulting CSV, and either writes the clean payload to a staging table or routes it to a dead letter queue for manual inspection. The prompt itself does not connect to your warehouse; your application code handles the database write only after validation passes.

Wire the prompt into a Python, Go, or Java ETL worker that calls your LLM provider's API with a timeout of at least 30 seconds for large inputs. After receiving the model response, run a strict validation layer: (1) confirm the output is valid CSV by parsing it with a library like Python's csv module or Apache Commons CSV, (2) verify the header row matches the expected column list exactly—order and spelling, (3) check that every data row has the same number of fields as the header, (4) scan for unescaped delimiters or newlines inside quoted fields, and (5) apply a row count sanity check against the input record count if known. If any validation fails, log the failure with the raw model output and the specific validator error, then retry once with the same prompt plus the validation error message appended as a correction instruction. If the retry also fails, route the record to a human review queue—do not silently insert malformed CSV.

For high-volume pipelines, batch multiple documents into a single prompt call where the [INPUT] placeholder contains a list of records, and instruct the model to produce one CSV with all rows. Set a batch size limit (e.g., 50 records) to stay within token limits and keep validation manageable. Use a model with strong instruction-following and structured output capabilities—GPT-4o, Claude 3.5 Sonnet, or Gemini 1.5 Pro are good starting points. Avoid smaller or older models that may drop quotes or mishandle escape sequences. If your warehouse requires specific encoding (e.g., UTF-8 with BOM for Excel compatibility), add that as a post-processing step in your application layer rather than relying on the model to produce byte-level encoding guarantees. Always log the prompt version, model version, input hash, and validation result for every run to support debugging and audit trails.