ImportCSV - AI-native CSV importer for developers

CSV Column Mapping: Auto-Match Headers with AI

Users upload CSV files with headers like "email_addr", "E-Mail", or "emailAddress". Your system expects "email". Matching these mismatched headers to your schema is called column mapping, and getting it wrong means corrupted data or failed imports.

This tutorial covers three approaches to solving this problem: traditional fuzzy matching algorithms, modern AI-powered techniques, and hybrid strategies that combine both. You'll walk away with working TypeScript code you can drop into your React application.

Prerequisites

Node.js 18+
React 18+
TypeScript 5+
OpenAI API key (for AI-assisted mapping section)

What you'll build

A column mapping system that:

Uses Levenshtein distance for typo tolerance
Applies token-based matching for reordered words
Leverages LLM intelligence for semantic understanding
Combines all three with confidence scoring
Renders a React UI for user confirmation

Step 1: Understanding the Problem

When users upload CSV files to your application, their headers rarely match your expected schema. Common mismatches include:

User Uploads	Your Schema	Mismatch Type
email_addr	email	Abbreviation
First Name	firstName	Case + spacing
DOB	birthdate	Abbreviation
phone_number	phone	Extra words
Emial	email	Typo

A robust column mapping system must handle all of these cases automatically while letting users confirm or override suggestions.

Step 2: Levenshtein Distance for Typo Tolerance

Levenshtein distance measures the minimum single-character edits (insertions, deletions, substitutions) needed to transform one string into another. It has O(n*m) complexity where n and m are string lengths.

// levenshtein.ts
export function levenshteinDistance(a: string, b: string): number {
  const matrix: number[][] = [];

  // Initialize matrix
  for (let i = 0; i <= a.length; i++) {
    matrix[i] = [i];
  }
  for (let j = 0; j <= b.length; j++) {
    matrix[0][j] = j;
  }

  // Fill matrix
  for (let i = 1; i <= a.length; i++) {
    for (let j = 1; j <= b.length; j++) {
      const cost = a[i - 1] === b[j - 1] ? 0 : 1;
      matrix[i][j] = Math.min(
        matrix[i - 1][j] + 1,      // deletion
        matrix[i][j - 1] + 1,      // insertion
        matrix[i - 1][j - 1] + cost // substitution
      );
    }
  }

  return matrix[a.length][b.length];
}

export function levenshteinSimilarity(a: string, b: string): number {
  const distance = levenshteinDistance(a.toLowerCase(), b.toLowerCase());
  const maxLength = Math.max(a.length, b.length);
  return maxLength === 0 ? 1 : 1 - distance / maxLength;
}

This implementation normalizes the distance to a 0-1 similarity score. "email" vs "emial" returns ~0.8 similarity (1 edit in a 5-character string).

For production use, packages like fast-levenshtein or fuzzball.js (which ports Python's fuzzywuzzy library) are popular options as of this writing. Check npm for current maintenance status before adopting.

Step 3: Token-Based Matching for Reordered Words

Levenshtein fails when words are reordered: "First Name" vs "name_first" scores poorly despite being the same concept. Token-based matching solves this by comparing sets of words.

// tokenMatch.ts
function tokenize(str: string): string[] {
  return str
    .toLowerCase()
    .replace(/[_\-\.]/g, ' ')  // Convert separators to spaces
    .split(/\s+/)              // Split on whitespace
    .filter(Boolean)           // Remove empty strings
    .sort();                   // Sort alphabetically
}

export function tokenSortRatio(a: string, b: string): number {
  const tokensA = tokenize(a);
  const tokensB = tokenize(b);

  // Compare sorted token strings
  const sortedA = tokensA.join(' ');
  const sortedB = tokensB.join(' ');

  return levenshteinSimilarity(sortedA, sortedB);
}

export function tokenSetRatio(a: string, b: string): number {
  const setA = new Set(tokenize(a));
  const setB = new Set(tokenize(b));

  // Calculate Jaccard similarity
  const intersection = new Set([...setA].filter(x => setB.has(x)));
  const union = new Set([...setA, ...setB]);

  return intersection.size / union.size;
}

Token Sort Ratio handles "First Name" vs "name first" by sorting tokens alphabetically before comparing. Token Set Ratio measures overlap between unique tokens, useful when one header has extra words.

Step 4: Synonym Dictionary for Domain Knowledge

Fuzzy matching struggles with abbreviations and domain-specific terms. A synonym dictionary provides explicit mappings for common variations.

// synonyms.ts
const synonymDictionary: Record<string, string[]> = {
  'email': ['email_address', 'e-mail', 'email_addr', 'emailaddress', 'mail'],
  'name': ['full_name', 'fullname', 'customer_name', 'user_name', 'contact_name'],
  'firstName': ['first_name', 'fname', 'given_name', 'forename'],
  'lastName': ['last_name', 'lname', 'surname', 'family_name'],
  'phone': ['phone_number', 'tel', 'telephone', 'mobile', 'cell', 'contact_number'],
  'address': ['street_address', 'street', 'addr', 'address_line_1'],
  'city': ['town', 'locality'],
  'state': ['province', 'region'],
  'zip': ['zipcode', 'zip_code', 'postal_code', 'postcode'],
  'country': ['country_code', 'nation'],
  'birthdate': ['dob', 'date_of_birth', 'birth_date', 'birthday'],
  'company': ['organization', 'org', 'company_name', 'employer'],
};

export function findSynonymMatch(
  header: string,
  targetSchema: string[]
): { target: string; score: number } | null {
  const normalized = header.toLowerCase().replace(/[_\-\.\s]/g, '');

  for (const target of targetSchema) {
    const synonyms = synonymDictionary[target] || [];
    const allVariants = [target.toLowerCase(), ...synonyms.map(s => s.toLowerCase())];

    for (const variant of allVariants) {
      const normalizedVariant = variant.replace(/[_\-\.\s]/g, '');
      if (normalized === normalizedVariant) {
        return { target, score: 1.0 };
      }
    }
  }

  return null;
}

Synonym matching provides high confidence (score: 1.0) for exact matches. This should run before fuzzy matching since it handles known abbreviations that algorithms might miss.

Step 5: Data-Type Inference from Column Values

When headers are ambiguous or missing, analyzing the actual data helps identify column types.

// typeInference.ts
const patterns = {
  email: /^[^\s@]+@[^\s@]+\.[^\s@]+$/,
  phone: /^[\+]?[(]?[0-9]{1,3}[)]?[-\s\.]?[(]?[0-9]{1,4}[)]?[-\s\.]?[0-9]{3,6}$/,
  date: /^\d{1,4}[-\/]\d{1,2}[-\/]\d{1,4}$/,
  url: /^https?:\/\/.+/,
  currency: /^[$\u20AC\u00A3]\s?\d+([,\.]\d{2,})?$/,
  zip: /^\d{5}(-\d{4})?$/,
};

function matchesPattern(value: string, pattern: RegExp): boolean {
  return pattern.test(value.trim());
}

export function inferColumnType(
  values: string[],
  threshold: number = 0.8
): string | null {
  const sample = values.slice(0, 100).filter(v => v && v.trim());
  if (sample.length === 0) return null;

  for (const [type, pattern] of Object.entries(patterns)) {
    const matches = sample.filter(v => matchesPattern(v, pattern));
    const matchRate = matches.length / sample.length;

    if (matchRate >= threshold) {
      return type;
    }
  }

  return null;
}

export function inferColumnMapping(
  headers: string[],
  data: Record<string, string>[],
  targetSchema: string[]
): Map<string, { target: string; confidence: number; method: string }> {
  const mappings = new Map();

  for (const header of headers) {
    const values = data.map(row => row[header] || '');
    const inferredType = inferColumnType(values);

    if (inferredType) {
      // Find matching target field by type
      const matchingTarget = targetSchema.find(t =>
        t.toLowerCase().includes(inferredType) ||
        inferredType.includes(t.toLowerCase())
      );

      if (matchingTarget) {
        mappings.set(header, {
          target: matchingTarget,
          confidence: 0.7,
          method: 'type-inference'
        });
      }
    }
  }

  return mappings;
}

Type inference analyzes a sample of column values (first 100 rows) and checks what percentage match known patterns. This approach helps when headers are cryptic ("col_1") or missing entirely.

Step 6: LLM-Assisted Mapping for Semantic Understanding

Traditional algorithms miss semantic relationships: "DOB" and "birthdate" are obviously the same to humans, but have low string similarity. LLMs understand these connections.

// aiMapping.ts
import OpenAI from 'openai';

interface SchemaField {
  name: string;
  description?: string;
  type?: string;
}

interface MappingResult {
  mapping: Record<string, string | null>;
  confidence: Record<string, number>;
}

export async function mapWithLLM(
  sourceHeaders: string[],
  targetSchema: SchemaField[],
  sampleData?: Record<string, string>[]
): Promise<MappingResult> {
  const openai = new OpenAI();

  const schemaDescription = targetSchema
    .map(f => `- ${f.name}${f.description ? `: ${f.description}` : ''}`)
    .join('\n');

  const sampleContext = sampleData
    ? `\n\nSample data (first row):\n${JSON.stringify(sampleData[0], null, 2)}`
    : '';

  const prompt = `You are a data mapping assistant. Map CSV headers to a target schema.

Source CSV headers: ${JSON.stringify(sourceHeaders)}

Target schema fields:
${schemaDescription}
${sampleContext}

Return a JSON object with:
1. "mapping": object mapping each source header to a target field name (or null if no match)
2. "confidence": object with confidence scores (0-1) for each mapping

Rules:
- Only map when confident the fields represent the same data
- Consider abbreviations, synonyms, and semantic meaning
- Return null for headers that don't match any target field
- Be conservative: low confidence is better than wrong mapping`;

  const response = await openai.chat.completions.create({
    model: 'gpt-4o-mini',
    messages: [{ role: 'user', content: prompt }],
    response_format: { type: 'json_object' },
    temperature: 0.1,
  });

  const result = JSON.parse(response.choices[0].message.content || '{}');

  return {
    mapping: result.mapping || {},
    confidence: result.confidence || {},
  };
}

The LLM approach handles edge cases that rule-based systems miss:

Abbreviations ("DOB" to "birthdate")
Domain-specific terms ("SKU" to "productId")
Context from sample data (a column of "@company.com" values suggests email)

For cost management, use gpt-4o-mini which handles this task well at lower cost. Cache results for repeated schema configurations.

Step 7: Combining Strategies with Confidence Scoring

Production systems should combine multiple strategies, using the highest-confidence match from each approach.

// columnMapper.ts
interface MappingCandidate {
  target: string;
  score: number;
  method: 'synonym' | 'fuzzy' | 'token' | 'type-inference' | 'ai';
}

interface ColumnMapping {
  source: string;
  target: string | null;
  confidence: number;
  method: string;
  alternatives: MappingCandidate[];
}

export async function mapColumns(
  sourceHeaders: string[],
  targetSchema: string[],
  data?: Record<string, string>[],
  options: {
    threshold?: number;
    useAI?: boolean;
  } = {}
): Promise<ColumnMapping[]> {
  const { threshold = 0.6, useAI = false } = options;
  const results: ColumnMapping[] = [];

  // Get AI mappings if enabled
  let aiMappings: Record<string, { target: string | null; confidence: number }> = {};
  if (useAI) {
    try {
      const aiResult = await mapWithLLM(
        sourceHeaders,
        targetSchema.map(name => ({ name })),
        data
      );
      for (const [source, target] of Object.entries(aiResult.mapping)) {
        aiMappings[source] = {
          target,
          confidence: aiResult.confidence[source] || 0.8,
        };
      }
    } catch (error) {
      console.warn('AI mapping failed, falling back to rule-based:', error);
    }
  }

  for (const source of sourceHeaders) {
    const candidates: MappingCandidate[] = [];

    // Strategy 1: Synonym matching (highest priority)
    const synonymMatch = findSynonymMatch(source, targetSchema);
    if (synonymMatch) {
      candidates.push({ ...synonymMatch, method: 'synonym' });
    }

    // Strategy 2: AI matching
    if (aiMappings[source]?.target) {
      candidates.push({
        target: aiMappings[source].target!,
        score: aiMappings[source].confidence,
        method: 'ai',
      });
    }

    // Strategy 3: Fuzzy matching
    for (const target of targetSchema) {
      const fuzzyScore = levenshteinSimilarity(source, target);
      if (fuzzyScore >= threshold) {
        candidates.push({ target, score: fuzzyScore, method: 'fuzzy' });
      }

      const tokenScore = tokenSortRatio(source, target);
      if (tokenScore >= threshold) {
        candidates.push({ target, score: tokenScore, method: 'token' });
      }
    }

    // Strategy 4: Type inference (if data provided)
    if (data) {
      const values = data.map(row => row[source] || '');
      const inferredType = inferColumnType(values);
      if (inferredType) {
        const matchingTarget = targetSchema.find(t =>
          t.toLowerCase().includes(inferredType)
        );
        if (matchingTarget) {
          candidates.push({
            target: matchingTarget,
            score: 0.7,
            method: 'type-inference',
          });
        }
      }
    }

    // Sort by score and pick best match
    candidates.sort((a, b) => b.score - a.score);
    const best = candidates[0];

    results.push({
      source,
      target: best?.score >= threshold ? best.target : null,
      confidence: best?.score || 0,
      method: best?.method || 'none',
      alternatives: candidates.slice(1, 4), // Keep top 3 alternatives
    });
  }

  return results;
}

This combined approach:

Checks synonym dictionary first (fastest, most reliable for known variations)
Queries the LLM for semantic understanding
Runs fuzzy and token-based matching
Uses type inference when data is available
Returns the highest-confidence match with alternatives for user review

Step 8: Building the React Confirmation UI

Users need to verify and correct auto-generated mappings. Here's a component for that:

// ColumnMappingUI.tsx
import React, { useState, useEffect } from 'react';

interface ColumnMapping {
  source: string;
  target: string | null;
  confidence: number;
  method: string;
  alternatives: { target: string; score: number }[];
}

interface Props {
  mappings: ColumnMapping[];
  targetSchema: string[];
  onConfirm: (finalMappings: Record<string, string>) => void;
  onCancel: () => void;
}

export function ColumnMappingUI({
  mappings,
  targetSchema,
  onConfirm,
  onCancel,
}: Props) {
  const [userMappings, setUserMappings] = useState<Record<string, string>>({});

  useEffect(() => {
    // Initialize with auto-detected mappings
    const initial: Record<string, string> = {};
    for (const m of mappings) {
      if (m.target) {
        initial[m.source] = m.target;
      }
    }
    setUserMappings(initial);
  }, [mappings]);

  const handleChange = (source: string, target: string) => {
    setUserMappings(prev => ({
      ...prev,
      [source]: target || undefined,
    }));
  };

  const usedTargets = new Set(Object.values(userMappings));

  const handleConfirm = () => {
    const finalMappings: Record<string, string> = {};
    for (const [source, target] of Object.entries(userMappings)) {
      if (target) {
        finalMappings[source] = target;
      }
    }
    onConfirm(finalMappings);
  };

  return (
    <div className="column-mapping">
      <h2>Map Your Columns</h2>
      <p>We've auto-matched your columns. Review and adjust as needed.</p>

      <table>
        <thead>
          <tr>
            <th>Your Column</th>
            <th>Maps To</th>
            <th>Confidence</th>
          </tr>
        </thead>
        <tbody>
          {mappings.map(mapping => (
            <tr key={mapping.source}>
              <td>
                <code>{mapping.source}</code>
              </td>
              <td>
                <select
                  value={userMappings[mapping.source] || ''}
                  onChange={e => handleChange(mapping.source, e.target.value)}
                >
                  <option value="">-- Skip this column --</option>
                  {targetSchema.map(field => (
                    <option
                      key={field}
                      value={field}
                      disabled={
                        usedTargets.has(field) &&
                        userMappings[mapping.source] !== field
                      }
                    >
                      {field}
                      {usedTargets.has(field) &&
                        userMappings[mapping.source] !== field &&
                        ' (already mapped)'}
                    </option>
                  ))}
                </select>
              </td>
              <td>
                <ConfidenceBadge
                  score={mapping.confidence}
                  method={mapping.method}
                />
              </td>
            </tr>
          ))}
        </tbody>
      </table>

      <div className="actions">
        <button onClick={onCancel}>Cancel</button>
        <button onClick={handleConfirm} className="primary">
          Confirm Mapping
        </button>
      </div>
    </div>
  );
}

function ConfidenceBadge({
  score,
  method,
}: {
  score: number;
  method: string;
}) {
  const level = score >= 0.9 ? 'high' : score >= 0.7 ? 'medium' : 'low';
  const percentage = Math.round(score * 100);

  return (
    <span className={`confidence-badge ${level}`} title={`Method: ${method}`}>
      {percentage}%
    </span>
  );
}

The UI shows confidence levels so users can focus on low-confidence mappings. It also prevents duplicate mappings (one source to multiple targets).

Complete Example

Here's how to wire everything together:

// CSVImportFlow.tsx
import React, { useState } from 'react';
import { mapColumns } from './columnMapper';
import { ColumnMappingUI } from './ColumnMappingUI';

const TARGET_SCHEMA = [
  'email',
  'firstName',
  'lastName',
  'phone',
  'company',
  'address',
  'city',
  'state',
  'zip',
];

export function CSVImportFlow() {
  const [step, setStep] = useState<'upload' | 'mapping' | 'complete'>('upload');
  const [csvData, setCsvData] = useState<Record<string, string>[]>([]);
  const [headers, setHeaders] = useState<string[]>([]);
  const [mappings, setMappings] = useState<any[]>([]);

  const handleFileUpload = async (file: File) => {
    const text = await file.text();
    const lines = text.split('\n').filter(Boolean);
    const parsedHeaders = lines[0].split(',').map(h => h.trim());
    const data = lines.slice(1).map(line => {
      const values = line.split(',');
      const row: Record<string, string> = {};
      parsedHeaders.forEach((h, i) => {
        row[h] = values[i]?.trim() || '';
      });
      return row;
    });

    setHeaders(parsedHeaders);
    setCsvData(data);

    // Auto-map columns
    const autoMappings = await mapColumns(
      parsedHeaders,
      TARGET_SCHEMA,
      data,
      { threshold: 0.6, useAI: true }
    );

    setMappings(autoMappings);
    setStep('mapping');
  };

  const handleMappingConfirm = (finalMappings: Record<string, string>) => {
    // Transform data according to mappings
    const transformedData = csvData.map(row => {
      const newRow: Record<string, string> = {};
      for (const [source, target] of Object.entries(finalMappings)) {
        newRow[target] = row[source];
      }
      return newRow;
    });

    console.log('Transformed data:', transformedData);
    setStep('complete');
  };

  if (step === 'upload') {
    return (
      <div>
        <h1>Import Contacts</h1>
        <input
          type="file"
          accept=".csv"
          onChange={e => {
            const file = e.target.files?.[0];
            if (file) handleFileUpload(file);
          }}
        />
      </div>
    );
  }

  if (step === 'mapping') {
    return (
      <ColumnMappingUI
        mappings={mappings}
        targetSchema={TARGET_SCHEMA}
        onConfirm={handleMappingConfirm}
        onCancel={() => setStep('upload')}
      />
    );
  }

  return <div>Import complete.</div>;
}

Common Pitfalls

Threshold Tuning

Setting the match threshold too high (above 0.9) causes missed matches for legitimate variations. Too low (below 0.5) generates false positives. Start with 0.6-0.7 and adjust based on your data.

Handling Ambiguous Matches

When multiple source columns match the same target ("First_Name" and "Given_Name" both matching "firstName"), the system should:

Pick the highest confidence match
Present alternatives to the user
Prevent duplicate mappings in the UI

Performance with Large Files

For files with thousands of rows:

Limit type inference to first 100 rows (sample-based)
Run LLM calls only once per file, not per column
Cache synonym lookups in a Map for O(1) access

Missing Required Fields

After mapping, validate that all required schema fields have mappings. Alert users to missing required columns before processing.

The Easier Way: ImportCSV

Building production-ready column mapping requires handling dozens of edge cases: encoding issues, delimiter detection, header row identification, and more. The code above is a starting point, but real-world implementations need validation, error handling, and user preference learning.

ImportCSV handles all of this with a drop-in React component:

import { CSVImporter } from '@importcsv/react';

<CSVImporter
  schema={[
    { key: 'email', label: 'Email', required: true },
    { key: 'firstName', label: 'First Name' },
    { key: 'lastName', label: 'Last Name' },
    { key: 'phone', label: 'Phone' },
  ]}
  onComplete={(data) => {
    // Data arrives with columns already mapped to your schema
    console.log(data);
  }}
/>

ImportCSV uses AI-assisted mapping and learns from user corrections over time, improving accuracy for your specific use case.