Map inconsistent export columns to target schemas

Match disparate, poorly named export headers to a structured target data schema. Use it when consolidating CSV reports from multiple platforms.

You getMapping Specification and unmapped Raw Columns

Prompt

Prompt
Act as a data integration specialist. Map the messy raw export column headers below to the provided target data schema.
- Match every raw field to the most appropriate target column based on name, semantic meaning, and data type.
- Identify unmapped fields, split fields (such as full name into first and last), and merged columns.
- Highlight columns that require type casting, unit conversion, or date parsing.
Output format:
1. Mapping Specification: Table with Raw Header, Target Column, Data Type, Transformation Logic
2. Unmapped Raw Columns: List of fields dropped with rationale
3. Missing Target Fields: Target fields that received no source data

Target schema and raw export columns are below:

Example output

Example for: mapping legacy CRM export fields to a unified customer analytics schema

1. Mapping Specification
Raw HeaderTarget ColumnData TypeTransformation Logic
cust_id_numcustomer_idSTRINGCast to string, trim leading whitespace
CustNamefull_nameSTRINGTrim extra spaces; parse into first/last downstream if needed
contact_email_addremailSTRINGConvert to lowercase, strip trailing periods
tot_rev_usd_centslifetime_valueDECIMAL(10,2)Divide integer cents by 100.0
reg_dt_rawcreated_atTIMESTAMPParse from format MM/DD/YYYY to ISO 8601
phone_intlphone_e164STRINGStrip non-numeric characters, prepend plus sign if missing
2. Unmapped Raw Columns
  • temp_flag_01: Internal legacy debug column; contains no persistent business value.
  • v_export_batch: ETL export tracker metadata from the source system; unneeded in analytical warehouse.
3. Missing Target Fields
  • subscription_tier: No source field available. Set default value to 'STANDARD' or populate via separate plan lookup table.
  • updated_at: No record update timestamp in legacy export. Default to created_at or current ingestion timestamp.
Written by AI from this prompt. Your results will vary.

Paste your target schema definition followed by the source headers or a small CSV sample into ChatGPT, Claude or Gemini. Ask the model for a Python dictionary or JSON mapping config if you are automating the ETL process.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in Data Analysis