Expense Sorted

How to Import Bank CSV Files into Google Sheets (Complete 2025 Guide)

Your bank statement downloads as a CSV file. Google Sheets can handle CSV files. This should be simple, right?

Wrong.

Bank CSV files are uniquely frustrating. They come with inconsistent date formats, merged cells, header rows that aren't headers, and encoding issues that turn your dollar signs into question marks. What should be a 30-second import becomes a 30-minute debugging session.

But here's the thing: getting this right once saves hours every month. And more importantly, it gives you the foundation for automating your entire financial workflow without surrendering your data to another fintech app.

If you'd rather skip the manual setup, our free Google Sheet template handles all major bank CSV formats automatically.

Why Bank CSV Imports Fail (And How to Fix Them)

Problem 1: Banks Use Non-Standard CSV Formats

What You Expect:

Date,Description,Amount
2025-06-18,"Coffee Shop",-4.50
2025-06-17,"Salary Deposit",2500.00

What Banks Actually Give You:

"Transaction Date","Posted Date","Description","Card Number","Amount"
"06/18/2025","06/18/2025","COFFEE SHOP 123 MAIN ST","****1234","-$4.50"
"","","Beginning Balance as of 06/17/2025:","","$1,247.89"

The Fix: Standardize your import process with a dedicated "Raw Import" sheet where you dump the messy data, then use formulas to clean it into a standardized format.

Problem 2: Date Formats Are Inconsistent

Different banks use different date formats, and some use different formats within the same file:

  • MM/DD/YYYY (US format)
  • DD/MM/YYYY (International format)
  • YYYY-MM-DD (ISO format)
  • MMM DD, YYYY (Written format)

The Solution: Create a date standardization formula that handles multiple formats:

=IF(ISNUMBER(DATEVALUE(A2)),DATEVALUE(A2),
  IF(ISNUMBER(DATEVALUE(SUBSTITUTE(A2,"/","-"))),
    DATEVALUE(SUBSTITUTE(A2,"/","-")),
    IF(LEN(A2)=8,DATE(RIGHT(A2,4),MID(A2,3,2),LEFT(A2,2)),
      "Check Format")))

Problem 3: Amount Columns Include Currency Symbols and Formatting

Banks love to include dollar signs, commas, and parentheses for negative numbers. Google Sheets sees "$1,234.56" as text, not a number.

The Solution: Strip formatting with a cleaning formula:

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",",""),"(",""),")",""))*
IF(SEARCH("(",A2,1),-1,1)

This removes currency symbols, commas, and converts parenthetical negatives to proper negative numbers.

The Universal Bank CSV Import Process

Want to skip all this and use a template that does it for you? Download our free, pre-built Google Sheet here.

Here's a step-by-step system that works with any bank:

Step 1: Set Up Your Import Infrastructure

Create a Google Sheets workbook with these tabs:

  • Raw Import: Where messy bank data goes
  • Cleaned Data: Standardized transactions
  • Categories: Transaction categorization
  • Dashboard: Summary and analysis

Step 2: Download and Import Bank CSV

For Most Banks:

  1. Go to File → Import in Google Sheets
  2. Upload your bank CSV file
  3. Choose "Replace current sheet" for Raw Import tab
  4. Select "Comma" as separator
  5. Important: Uncheck "Convert text to numbers, dates, and formulas"

Why Uncheck Conversion? You want to control the data cleaning process. Auto-conversion often misinterprets dates and amounts.

Step 3: Clean and Standardize Data

In your "Cleaned Data" sheet, create columns for:

  • Date (standardized format)
  • Description (cleaned)
  • Amount (numeric values)
  • Account (if importing multiple accounts)

Date Cleaning Formula (assuming date is in column A of Raw Import):

=DATE(RIGHT('Raw Import'!A2,4),
      MID('Raw Import'!A2,1,2),
      MID('Raw Import'!A2,4,2))

Description Cleaning Formula:

=PROPER(TRIM('Raw Import'!B2))

Amount Cleaning Formula:

=IF('Raw Import'!C2="",0,
   VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('Raw Import'!C2,"$",""),",",""),"(",""))*
   IF(ISERROR(SEARCH("(",Raw Import'!C2)),1,-1))

Step 4: Handle Common Bank-Specific Issues

Issue: Multiple Header Rows

Some banks include 3-4 rows of account info before the actual transaction data.

Solution: Use Apps Script to automatically detect where real data starts:

function findDataStart() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  for (var i = 0; i < data.length; i++) {
    // Look for first row with a valid date
    var cellValue = data[i][0];
    if (isValidDate(cellValue)) {
      return i + 1; // Return row number (1-indexed)
    }
  }
  return 1; // Default to first row if no date found
}

function isValidDate(dateString) {
  var date = new Date(dateString);
  return date instanceof Date && !isNaN(date);
}

Issue: Merged Transaction Descriptions

Some banks spread transaction details across multiple columns.

Solution: Concatenate and clean:

=TRIM(B2&" "&C2&" "&D2)

Issue: Pending vs. Posted Transactions

Banks often include both pending and posted versions of the same transaction.

Solution: Use conditional formatting to highlight duplicates, then create a filter formula:

=IF(COUNTIFS($B$2:$B2,B2,$C$2:$C2,C2)>1,"DUPLICATE","UNIQUE")

Bank-Specific Import Guides

Wells Fargo

File Format: CSV with MM/DD/YYYY dates
Amount Column: Includes $ and commas
Quirk: Negative amounts use minus signs, not parentheses

Import Settings:

  • Separator: Comma
  • Date Format: MM/DD/YYYY
  • Skip first row (headers)

Chase Bank

File Format: CSV with MM/DD/YYYY dates
Amount Column: Clean numeric values (no $)
Quirk: Separate columns for debits and credits

Cleaning Formula for Amount:

=IF(D2<>0,D2,-C2)

Bank of America

File Format: CSV with MM/DD/YYYY dates
Amount Column: Includes $ and uses parentheses for negatives
Quirk: Description often includes extra spaces

Cleaning Steps:

  1. Remove currency formatting
  2. Convert parentheses to negative signs
  3. Trim extra spaces from descriptions

ANZ (New Zealand)

File Format: CSV with DD/MM/YYYY dates
Amount Column: Clean numeric with negatives
Quirk: Different date format from US banks

Date Conversion Formula:

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

Commonwealth Bank (Australia)

File Format: CSV with DD/MM/YYYY dates
Amount Column: Uses separate debit/credit columns
Quirk: Balance column included

Amount Consolidation:

=IF(C2<>0,C2,IF(D2<>0,-D2,0))

Automating the Import Process

The easiest way to automate this entire process is to use our free Google Sheet template, which has this automation pre-built. For those who want to build it themselves, here are the methods:

Method 1: Apps Script Automation

Create a script that automatically processes new CSV uploads:

function processNewCSV() {
  var rawSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Raw Import");
  var cleanSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cleaned Data");
  
  var rawData = rawSheet.getDataRange().getValues();
  var cleanedData = [];
  
  // Skip header row
  for (var i = 1; i < rawData.length; i++) {
    var row = rawData[i];
    
    // Skip empty rows or balance rows
    if (row[0] === "" || row[2] === "") continue;
    
    var cleanedRow = [
      standardizeDate(row[0]),
      cleanDescription(row[1]),
      cleanAmount(row[2])
    ];
    
    cleanedData.push(cleanedRow);
  }
  
  // Append to cleaned data sheet
  if (cleanedData.length > 0) {
    var lastRow = cleanSheet.getLastRow();
    cleanSheet.getRange(lastRow + 1, 1, cleanedData.length, 3).setValues(cleanedData);
  }
}

function standardizeDate(dateString) {
  // Handle multiple date formats
  var date = new Date(dateString);
  return Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy-MM-dd");
}

function cleanDescription(description) {
  return description.toString().trim().replace(/\s+/g, " ");
}

function cleanAmount(amount) {
  var cleaned = amount.toString().replace(/[$,()]/g, "");
  return parseFloat(cleaned) || 0;
}

Method 2: Google Apps Script Trigger for Email Imports

Many banks can email CSV statements. Set up automatic processing:

function processEmailCSVs() {
  var threads = GmailApp.search('from:statements@yourbank.com has:attachment filename:csv');
  
  threads.forEach(function(thread) {
    var messages = thread.getMessages();
    var latestMessage = messages[messages.length - 1];
    
    var attachments = latestMessage.getAttachments();
    attachments.forEach(function(attachment) {
      if (attachment.getName().indexOf('.csv') > -1) {
        // Process CSV attachment
        var csvData = Utilities.parseCsv(attachment.getDataAsString());
        processCSVData(csvData);
      }
    });
  });
}

Troubleshooting Common Import Problems

Problem: "The file is too large to import"

Cause: CSV file exceeds Google Sheets' 5 million cell limit
Solution:

  1. Split CSV into smaller files by date range
  2. Import older data into separate sheets
  3. Use Google Apps Script to combine data programmatically

Problem: Dates importing as text

Cause: Non-standard date format or mixed formats
Solution:

  1. Import with text conversion disabled
  2. Use custom date parsing formulas
  3. Create a "Date Issues" filter to catch problems

Problem: Amounts not calculating correctly

Cause: Currency symbols, thousand separators, or formatting
Solution:

  1. Use VALUE() function with SUBSTITUTE() to clean
  2. Handle parentheses for negative amounts
  3. Check for hidden characters (use CLEAN() function)

Problem: Duplicate transactions appearing

Cause: Bank includes both pending and posted versions
Solution:

  1. Sort by date and amount
  2. Use COUNTIFS() to identify duplicates
  3. Create filter formulas to show unique transactions only

Problem: Missing or garbled characters

Cause: Encoding issues (UTF-8 vs. ASCII)
Solution:

  1. Open CSV in text editor and save with UTF-8 encoding
  2. Use Google Drive upload instead of direct file import
  3. Try different import encoding options

Building Your Monthly Import Routine

The 5-Minute Monthly Process

Once your system is set up:

  1. Download CSV files from all accounts (2 minutes)
  2. Upload to Raw Import sheet (30 seconds)
  3. Run cleaning script or copy formulas (1 minute)
  4. Review for obvious errors (1 minute)
  5. Archive raw CSV files (30 seconds)

Quality Control Checklist

Before processing each month's data:

  • Date range covers expected period
  • Transaction count seems reasonable
  • No obvious duplicates
  • Amount totals match bank statements
  • All transactions have dates and amounts

Error Prevention Tips

  1. Backup Before Import: Always keep a copy of your working sheet
  2. Test With Small Files First: Import one week before doing full months
  3. Document Your Process: Keep notes on bank-specific quirks
  4. Version Control: Use Google Sheets' version history feature
  5. Regular Audits: Monthly spot-checks against actual bank statements

Advanced Import Techniques

Multi-Bank Aggregation Script

function aggregateMultipleBanks() {
  var banks = [
    {name: "Checking", sheet: "Chase_Import", dateCol: 1, descCol: 2, amountCol: 3},
    {name: "Savings", sheet: "WF_Import", dateCol: 1, descCol: 4, amountCol: 5},
    {name: "Credit", sheet: "BOA_Import", dateCol: 1, descCol: 2, amountCol: 6}
  ];
  
  var masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("All_Transactions");
  var allData = [];
  
  banks.forEach(function(bank) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(bank.sheet);
    var data = sheet.getDataRange().getValues();
    
    for (var i = 1; i < data.length; i++) {
      allData.push([
        data[i][bank.dateCol - 1],
        data[i][bank.descCol - 1], 
        data[i][bank.amountCol - 1],
        bank.name
      ]);
    }
  });
  
  // Sort by date
  allData.sort(function(a, b) {
    return new Date(a[0]) - new Date(b[0]);
  });
  
  // Write to master sheet
  masterSheet.clear();
  masterSheet.getRange(1, 1, 1, 4).setValues([["Date", "Description", "Amount", "Account"]]);
  if (allData.length > 0) {
    masterSheet.getRange(2, 1, allData.length, 4).setValues(allData);
  }
}

Automatic Categorization During Import

function categorizeTransactions() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  var categories = {
    "GROCERY": "Food",
    "RESTAURANT": "Dining",
    "GAS": "Transportation", 
    "AMAZON": "Shopping",
    "NETFLIX": "Entertainment"
  };
  
  for (var i = 1; i < data.length; i++) {
    var description = data[i][1].toString().toUpperCase();
    var category = "Uncategorized";
    
    for (var keyword in categories) {
      if (description.indexOf(keyword) > -1) {
        category = categories[keyword];
        break;
      }
    }
    
    sheet.getRange(i + 1, 5).setValue(category);
  }
}

Next Steps: Building on Your Import Foundation

Once you've mastered CSV imports, you can layer on additional automation:

  1. Automatic Categorization: Use pattern matching to assign categories
  2. Budget Tracking: Compare spending against predetermined limits
  3. Trend Analysis: Identify seasonal patterns and unusual spending
  4. Alert System: Get notified of large transactions or budget overages
  5. Financial Forecasting: Project future cash flow based on historical patterns

Taking Action

Don't let CSV import frustration keep you from automating your finances. Start with one bank account, get the process working smoothly, then expand to additional accounts.

This Week:

  1. Download one month of bank transactions
  2. Set up the four-sheet structure (Raw, Cleaned, Categories, Dashboard)
  3. Import and clean one CSV file manually
  4. Document any bank-specific quirks you encounter

Next Week:

  1. Create cleaning formulas for your bank's format
  2. Test the process with a second month of data
  3. Build basic categorization rules
  4. Set up your monthly import routine

Remember: Perfect is the enemy of done. A working 80% solution you use monthly beats a perfect system you never finish building.

Your time is too valuable to spend on manual data entry. Get this foundation right, and you'll save hours every month while maintaining complete control over your financial data.


Ready to automate your bank imports? Download our bank-specific Google Sheets templates and get your first automated import working in under 30 minutes.

Stop Manual Expense Tracking

Let AI categorize your transactions automatically. See exactly where your money goes.

Try Free AI Categorization

100% free • No credit card required • 2 minute setup

Financial Dashboard

Upload bank statements, get AI insights

Try Free →

F*** You Money

Calculate financial independence number

Calculate →

Google Sheets Add-on

AI categorization in your spreadsheet

Get Add-on →