Split CSV by Date or Year

You have one massive file: transactions_all_time.csv. You need: transactions_2023.csv, transactions_2024.csv.

Splitting by date is a specific type of "Split by Column" that often requires a bit of pre-processing because dates are messy.

The Challenge: Date Formats

If your date column is 2024-01-01, splitting is easy. If your date column is 01/01/2024 14:30:00, splitting by "Year" is hard because "2024" isn't a distinct value—it's buried in the string.

Method 1: Excel Filter & Save

1. Open file.
2. Ensure Date column is recognized as Date.
3. Add a helper column "Year": =YEAR(A2).
4. Filter by Year "2023".
5. Copy visible cells -> New Workbook -> Save.
6. Repeat for 2024.

Pros: Visual. Cons: Slow, manual.

Method 2: Python (Pandas)

The automated way.

import pandas as pd

# Read CSV, parse dates
df = pd.read_csv('data.csv', parse_dates=['Date'])

# Create a Year column
df['Year'] = df['Date'].dt.year

# Group by Year and save separate files
for year, group in df.groupby('Year'):
    group.to_csv(f'data_{year}.csv', index=False)

This script instantly creates data_2023.csv, data_2024.csv, etc.

Method 3: Command Line (awk)

For the hardcore users. Assuming Date is Column 1 and format is YYYY-MM-DD.

awk -F, '{print > "data_" substr($1,1,4) ".csv"}' data.csv

Explanation: It takes the substring of the first column (chars 1-4, the year) and uses it as the filename.

Method 4: Split Tools

If you don't code, use a tool that supports "Split by Column".

1. If your date is YYYY-MM-DD, just split by that column. You'll get one file per day.
2. If you want by Year, you first need to create a "Year" column (using Excel or a Transform tool), then split by that.

-> Split CSV Tool

Summary

To split by date:

1. Ensure you have a clean column representing the group you want (e.g., "2024" for Year, or "2024-01" for Month).
2. Use Pandas for the fastest, most robust splitting.
3. Use Excel for small, one-off tasks.

---

Need to organize data? HappyCSV can split your files by any column value in seconds.