Skill: csv-import

# Skill: csv-import

## Overview

Multi-format CSV transaction parser for personal finance apps. Detects Chase, Bank of America, Wells Fargo, and generic CSV formats. Outputs normalized transaction objects, deduplicates via SHA256 hash, and applies keyword category rules.

## Supported Bank Formats

| Bank | Date Column | Description Column | Amount Notes |
|------|-------------|-------------------|--------------|
| Chase | Transaction Date | Description | Negative = debit |
| Bank of America | Date | Payee | Positive = debit (sign flip required) |
| Wells Fargo | Date | Description | Positive = debit (sign flip required) |
| Generic | date / Date | description / memo / payee | Auto-detect sign convention |

## Format Detection Logic

```typescript
function detectBankFormat(headers: string[]): BankFormat {
  const normalized = headers.map(h => h.toLowerCase().replace(/[^a-z ]/g, '').trim());

  const hasAll = (...cols: string[]) => cols.every(c => normalized.some(h => h.includes(c)));

  if (hasAll('transaction date', 'post date', 'description', 'amount')) {
    return 'chase';
  }
  if (hasAll('date', 'payee', 'account number')) {
    return 'bofa';
  }
  if (hasAll('date', 'amount', 'transaction type')) {
    return 'wellsfargo';
  }
  return 'generic';
}
```

## CSV Parsing Pipeline

```typescript
import { parse } from 'csv-parse/sync';

interface ParseOptions {
  skipDuplicates?: boolean;
  existingHashes?: Set<string>;
  categoryRules?: CategoryRule[];
}

interface ParseResult {
  transactions: NormalizedTransaction[];
  skipped: number;
  errors: string[];
  format: BankFormat;
  dateRange: { from: string; to: string } | null;
}

function parseCSV(fileContent: string, options: ParseOptions = {}): ParseResult {
  const records = parse(fileContent, {
    columns: true,
    skip_empty_lines: true,
    trim: true,
    relax_column_count: true,
  });

  const headers = Object.keys(records[0] ?? {});
  const format = detectBankFormat(headers);

  const transactions: NormalizedTransaction[] = [];
  let skipped = 0;
  const errors: string[] = [];

  for (const record of records) {
    try {
      const normalized = normalizeRow(record, format);
      if (!normalized) continue;

      const hash = importHash(normalized.date, normalized.description, normalized.amount);

      if (options.skipDuplicates && options.existingHashes?.has(hash)) {
        skipped++;
        continue;
      }

      if (options.categoryRules) {
        normalized.categoryId = autoCategorize(normalized.description, options.categoryRules);
      }

      normalized.importHash = hash;
      transactions.push(normalized);
    } catch (err) {
      errors.push(`Row ${records.indexOf(record) + 2}: ${(err as Error).message}`);
    }
  }

  // Compute date range
  const dates = transactions.map(t => t.date).sort();
  const dateRange = dates.length > 0
    ? { from: dates[0], to: dates[dates.length - 1] }
    : null;

  return { transactions, skipped, errors, format, dateRange };
}
```

## Row Normalization

```typescript
function normalizeRow(row: Record<string, string>, format: BankFormat): NormalizedTransaction | null {
  switch (format) {
    case 'chase': {
      const amount = parseFloat(row['Amount'] ?? '0');
      if (isNaN(amount)) return null;
      return {
        date: parseDate(row['Transaction Date']),
        description: row['Description']?.trim() ?? '',
        amount,  // Chase: negative = debit, positive = credit
        originalCategory: row['Category'] ?? null,
        type: row['Type'] ?? null,
      };
    }

    case 'bofa': {
      const raw = parseFloat(row['Amount'] ?? '0');
      if (isNaN(raw)) return null;
      // BofA exports debits as positive, credits as negative -- invert
      return {
        date: parseDate(row['Date']),
        description: (row['Payee'] ?? row['Description'] ?? '').trim(),
        amount: -raw,
        originalCategory: null,
        type: null,
      };
    }

    case 'wellsfargo': {
      const raw = parseFloat(row['Amount'] ?? '0');
      if (isNaN(raw)) return null;
      return {
        date: parseDate(row['Date']),
        description: row['Description']?.trim() ?? '',
        amount: raw,  // Wells Fargo: negative = debit already
        originalCategory: null,
        type: row['Transaction Type'] ?? null,
      };
    }

    case 'generic':
    default: {
      // Try common column names
      const dateVal = row['date'] ?? row['Date'] ?? row['DATE'] ?? '';
      const descVal = row['description'] ?? row['Description'] ?? row['memo'] ?? row['Memo'] ?? row['payee'] ?? '';
      const amtStr = row['amount'] ?? row['Amount'] ?? row['AMOUNT'] ?? '0';
      const amount = parseFloat(amtStr.replace(/[$,\s]/g, ''));
      if (!dateVal || isNaN(amount)) return null;
      return {
        date: parseDate(dateVal),
        description: descVal.trim(),
        amount,
        originalCategory: null,
        type: null,
      };
    }
  }
}
```

## Date Parsing

```typescript
function parseDate(raw: string): string {
  // Handles MM/DD/YYYY, M/D/YYYY, YYYY-MM-DD, DD-MMM-YY
  const cleaned = raw.trim();

  // Already ISO
  if (/^\d{4}-\d{2}-\d{2}$/.test(cleaned)) return cleaned;

  // MM/DD/YYYY or M/D/YYYY
  const mdy = cleaned.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/);
  if (mdy) {
    const [, m, d, y] = mdy;
    return `${y}-${m.padStart(2, '0')}-${d.padStart(2, '0')}`;
  }

  // DD-MMM-YY (e.g. 20-Mar-26)
  const dmy = cleaned.match(/^(\d{2})-([A-Za-z]{3})-(\d{2})$/);
  if (dmy) {
    const months: Record<string, string> = {
      Jan:'01',Feb:'02',Mar:'03',Apr:'04',May:'05',Jun:'06',
      Jul:'07',Aug:'08',Sep:'09',Oct:'10',Nov:'11',Dec:'12'
    };
    const [, d, mon, y] = dmy;
    return `20${y}-${months[mon] ?? '01'}-${d}`;
  }

  throw new Error(`Unrecognized date format: ${raw}`);
}
```

## Deduplication Hash

```typescript
import { createHash } from 'crypto';

function importHash(date: string, description: string, amount: number): string {
  // Normalize: trim whitespace, lowercase description, 2 decimal places
  const normalized = `${date}|${description.trim().toLowerCase()}|${amount.toFixed(2)}`;
  return createHash('sha256').update(normalized).digest('hex');
}
```

## Auto-Categorization

```typescript
interface CategoryRule {
  id: number;
  categoryId: number;
  keyword: string;
  priority: number;
}

function autoCategorize(description: string, rules: CategoryRule[]): number | null {
  // Rules are pre-sorted: ORDER BY priority DESC, category_id ASC
  const descLower = description.toLowerCase();
  for (const rule of rules) {
    if (descLower.includes(rule.keyword.toLowerCase())) {
      return rule.categoryId;
    }
  }
  return null;  // caller stores as NULL = Uncategorized
}
```

## Default Category Rules (seeded on first run)

```typescript
const DEFAULT_RULES = [
  // Food and Dining
  { category: 'Food and Dining', keywords: ['starbucks','whole foods','chipotle','mcdonald','subway','wendy','grubhub','doordash','instacart','trader joe','safeway','kroger','publix','chick-fil','panera','pizza hut','domino','taco bell','burger king','wendy','peet','dutch bros'] },
  // Housing
  { category: 'Housing', keywords: ['rent','mortgage','hoa','property management','apartment'] },
  // Shopping
  { category: 'Shopping', keywords: ['amazon','target','walmart','costco','ebay','etsy','best buy','home depot','ikea','gap','h&m','zara','nordstrom','macy'] },
  // Transportation
  { category: 'Transportation', keywords: ['uber','lyft','shell','chevron','bp ','exxon','mobil','metro transit','king county metro','amtrak','southwest','delta','united air','american air'] },
  // Entertainment
  { category: 'Entertainment', keywords: ['netflix','spotify','hulu','disney','apple tv','hbo','youtube premium','twitch','steam','ticketmaster','eventbrite','cinema','amc theatre','regal'] },
  // Utilities
  { category: 'Utilities', keywords: ['puget sound energy','comcast','verizon','at&t','t-mobile','spectrum','xfinity','pg&e','con ed','water dept','electric','internet','wireless'] },
  // Healthcare
  { category: 'Healthcare', keywords: ['pharmacy','cvs','walgreens','rite aid','urgent care','hospital','clinic','kaiser','blue cross','anthem','united health','dental','optometrist'] },
  // Income
  { category: 'Income', keywords: ['payroll','direct dep','salary','employer','ach credit','transfer from'] },
];
```

## Express Route Integration

```typescript
// POST /api/transactions/import
router.post('/import', upload.single('csv'), async (req, res) => {
  const fileContent = req.file!.buffer.toString('utf-8');
  const accountId = parseInt(req.body.accountId);
  const skipDuplicates = req.body.skipDuplicates === 'true';
  const autoCat = req.body.autoCategorize === 'true';

  // Load existing hashes for dedup
  const existingHashes = new Set<string>(
    db.prepare('SELECT import_hash FROM transactions WHERE import_hash IS NOT NULL')
      .all().map((r: any) => r.import_hash)
  );

  // Load rules if auto-categorize enabled
  const rules = autoCat
    ? db.prepare('SELECT id, category_id, keyword, priority FROM category_rules ORDER BY priority DESC, category_id ASC').all()
    : [];

  const result = parseCSV(fileContent, { skipDuplicates, existingHashes, categoryRules: rules });

  // Bulk insert
  const insert = db.prepare(`
    INSERT OR IGNORE INTO transactions
      (account_id, date, description, amount, category_id, import_hash)
    VALUES (?, ?, ?, ?, ?, ?)
  `);

  const insertMany = db.transaction((rows: NormalizedTransaction[]) => {
    for (const row of rows) {
      insert.run(accountId, row.date, row.description, row.amount, row.categoryId ?? null, row.importHash);
    }
  });

  insertMany(result.transactions);

  res.json({
    imported: result.transactions.length,
    skipped: result.skipped,
    errors: result.errors,
    format: result.format,
    dateRange: result.dateRange,
  });
});
```

## Dependencies

```json
{
  "csv-parse": "^5.5.0"
}
```

## Edge Cases

- BOM characters at start of file: strip with `fileContent.replace(/^\uFEFF/, '')`
- Quoted fields with embedded commas: handled by csv-parse
- Empty rows in middle of file: `skip_empty_lines: true`
- Amount values with currency symbols like `$1,234.56`: strip `[$,\s]` before parseFloat
- Date column may have extra whitespace: always `.trim()` before parsing
- Files exported by some banks have trailing commas on header row: `relax_column_count: true`