Skip to content

Latest commit

 

History

History
268 lines (198 loc) · 7.01 KB

CSV-FORMAT-GUIDE.md

File metadata and controls

268 lines (198 loc) · 7.01 KB

Bank CSV Format Guide

This system automatically detects and parses CSV files from multiple banks. Here's what's supported and how to add your own.

✅ Pre-Built Bank Parsers

The following banks are already supported with intelligent format detection:

Chase

  • Checking/Savings: Details, Posting Date, Description, Amount, Type, Balance, Check or Slip #
  • Credit Cards: Transaction Date, Post Date, Description, Category, Type, Amount, Memo
  • Amount Format: Negative for debits, positive for credits

American Express

  • Format: Date, Description, Amount, Card Member, Extended Details, Category
  • Amount Format: Positive for all (it's a credit card)

Bank of America

  • Format: Date, Description, Amount, Running Bal.
  • Amount Format: Negative for debits, positive for credits

Discover

  • Format: Trans. Date, Post Date, Description, Amount, Category
  • Amount Format: Positive for all

Capital One

  • Format: Transaction Date, Posted Date, Card No., Description, Debit, Credit
  • Amount Format: Split columns (Debit/Credit)

Wells Fargo

  • Format: Date, Amount, *, Check Number, Description
  • Amount Format: Negative for debits

Citi

  • Format: Status, Date, Description, Debit, Credit, Member Name
  • Amount Format: Split columns (Debit/Credit)

Generic (Fallback)

  • Intelligently detects common column patterns
  • Works with most standard formats
  • Used when no specific parser matches

📤 How to Share Your Bank CSV Format

If your bank isn't listed above, share a sanitized sample so I can build a custom parser.

What to Share

Minimum: 3 rows from your CSV (header + 2 transactions)

Example:

Date,Description,Amount,Balance
01/15/2024,WHOLE FOODS #123,-125.50,1500.00
01/16/2024,NETFLIX,-15.99,1484.01

How to Sanitize

You can replace:

  • Merchant names: WHOLE FOODS #123 → MERCHANT_A
  • Amounts: Use fake amounts
  • Balances: Use fake balances
  • Account numbers: Remove or replace

What I need to see:

  • ✅ Column headers (exact names)
  • ✅ Date format (MM/DD/YYYY, DD/MM/YYYY, etc.)
  • ✅ Amount format (single column or debit/credit split)
  • ✅ Negative sign placement (-, (), etc.)
  • ✅ Any special columns (category, type, etc.)

Example Sanitized CSVs

Chase Checking:

Details,Posting Date,Description,Amount,Type,Balance,Check or Slip #
DEBIT,01/15/2024,GROCERY STORE,-45.67,ACH_DEBIT,1234.56,
CREDIT,01/16/2024,PAYROLL DEPOSIT,2000.00,ACH_CREDIT,3234.56,

Amex:

Date,Description,Amount,Extended Details,Category
01/15/2024,RESTAURANT,-67.89,RESTAURANT NYC,Dining
01/16/2024,GAS STATION,-45.00,GAS STATION CA,Auto & Transport

Capital One:

Transaction Date,Posted Date,Card No.,Description,Category,Debit,Credit
2024-01-15,2024-01-16,1234,GROCERY STORE,Groceries,125.50,
2024-01-17,2024-01-18,1234,PAYMENT RECEIVED,Payments,,500.00

🔧 Format Detection Logic

The system detects your bank's format by:

  1. Reading CSV headers - column names
  2. Checking first row - data patterns
  3. Matching to known banks - using unique column combinations
  4. Falling back to generic - if no match found

Detection Examples

Chase detected by:

  • Has columns: "Posting Date" + "Description" + "Balance"
  • OR: "Transaction Date" + "Post Date" + "Amount"

Amex detected by:

  • Has columns: "Date" + "Description" + "Amount"
  • AND: "Card Member" OR "Extended Details"

Capital One detected by:

  • Has columns: "Transaction Date" + "Posted Date" + "Debit" + "Credit"

🛠️ Custom Parser Template

If you want to build your own parser, use this template:

import { BaseBankParser } from './base-parser';
import { RawTransaction } from '../../types/transaction.types';

export class MyBankParser extends BaseBankParser {
  name = 'My Bank';

  detect(headers: string[], firstRow: any): boolean {
    // Check for your bank's unique columns
    return this.hasColumns(headers, ['unique_col_1', 'unique_col_2']);
  }

  parse(row: any): RawTransaction {
    const date = this.findColumn(row, ['Date', 'Trans Date']) || '';
    const description = this.findColumn(row, ['Description']) || '';
    const amount = this.findColumn(row, ['Amount']) || '0';

    return { date, description, amount };
  }
}

📊 Supported CSV Variations

Date Formats

  • MM/DD/YYYY (US standard)
  • DD/MM/YYYY (International)
  • YYYY-MM-DD (ISO)
  • MM/DD/YY (Short year)

Amount Formats

Single Column:

Amount
-125.50    # Negative for debit
125.50     # Positive for credit

Split Columns:

Debit,Credit
125.50,     # Debit transaction
,125.50     # Credit transaction

Parentheses for Negative:

Amount
(125.50)    # Parsed as -125.50
125.50      # Parsed as 125.50

Special Cases

Headers with Symbols:

  • *, Check Number, Description (Wells Fargo)
  • Handled automatically

Multi-line Descriptions:

  • Some banks split description into multiple columns
  • Parser combines them: Description + Extended Details

Category/Type Columns:

  • Preserved if present
  • Used for initial categorization hints

🚀 How to Test Your CSV

  1. Save a sample (3-5 transactions):

    Date,Description,Amount
01/15/2024,Test Transaction,-50.00

  2. Try importing:

    pnpm cli import test.csv

  3. Check detection:

    Detected bank format: Chase
✓ Imported: 1

  4. If "Generic" detected:

    • Share your CSV format with me
    • I'll build a custom parser
    • You'll get better accuracy

🎯 What Happens After Detection

  1. Parser selected - specific or generic
  2. Rows parsed - using bank's format
  3. Data validated - dates, amounts, descriptions
  4. Duplicates checked - prevents re-importing
  5. Categories applied - auto-categorization
  6. Transactions stored - ready for analysis

📝 Tips for Best Results

  1. Export full CSV - don't edit in Excel (loses formatting)
  2. Include headers - column names are crucial
  3. Keep format consistent - don't mix formats
  4. One bank per file - don't combine CSVs
  5. Share unknown formats - I'll add support

🆘 Troubleshooting

"Import failed: Invalid CSV"

  • Check for missing headers
  • Ensure proper comma/quote escaping
  • Try different delimiter option

"Detected: Generic"

  • Your bank isn't recognized
  • Share CSV format for custom parser
  • Generic still works but less accurate

"Many validation errors"

  • Check date format in CSV
  • Verify amount column has numbers
  • Ensure required columns present

"All duplicates"

  • You already imported this file
  • Use --force to override (not recommended)
  • Check date range

📤 Ready to Share Your Format?

Send me:

  1. Bank name
  2. 3 rows of CSV (header + 2 transactions)
  3. Sanitized data is fine

I'll build a custom parser in ~5 minutes and you'll get perfect import accuracy.

Current Parsers: Chase, Amex, BofA, Discover, Capital One, Wells Fargo, Citi, Generic

Add yours: Share CSV format → Custom parser built → Perfect imports