How to split a large CSV file based on the number of rows 🔪
Tags: csv, python, performance, tooling
Published: 2021-07-22 20:08:17

---
title: How to split a large CSV file based on the number of rows 🔪
published: true
date: 2021-06-24 23:15:00 UTC
tags: csv, python, performance, tooling
canonical_url: https://www.benjaminrancourt.ca/how-to-split-a-large-csv-file-based-on-the-number-of-rows
cover_image: https://www.benjaminrancourt.ca/images/how-to-split-a-large-csv-file-based-on-the-number-of-rows.jpg
---
Recently I had to import **CSV files** into an external application at work, but the application was not able to process all the rows because it was triggering **timeouts** after a few minutes. The files weren't exactly huge, they were only 8MB and maxed out at 200,000 lines, but that was **too much** for this application... 😢

I had no control over the external software, but I needed **smaller files**. I could have adapted the application that creates them, but it would have required fetching the data from several APIs again and it was taking a long time. ⌛

Instead, I decided to try **splitting the files** directly. After a quick search on a search engine, I came accros a [Gist in Python](https://gist.github.com/kelvintaywl/37dbfaea789707ec5f48) that does exactly what I wanted. My CSV file had semicolons (`;`) instead of commas (`,`), so I had to [adapt its script a bit](https://gist.github.com/ranb2002/e99cbdc243f0f9445c3fe6613b5c52bc/revisions) to accommodate this (`delimiter=';'`).

Here is my [adapted version](https://gist.github.com/ranb2002/e99cbdc243f0f9445c3fe6613b5c52bc), also saved in a forked Gist, in case I need it later:

```python
import csv
import sys
import os

# example usage: python split.py example.csv 200
# above command would split the `example.csv` into smaller CSV files of 200 rows each (with header included)
# if example.csv has 401 rows for instance, this creates 3 files in same directory:
# - `example_1.csv` (row 1 - 200)
# - `example_2.csv` (row 201 - 400)
# - `example_3.csv` (row 401)

CURRENT_DIR = os.path.dirname(os.path.realpath( __file__ ))
filename = sys.argv[1]

full_file_path = os.path.join(CURRENT_DIR, filename)
file_name = os.path.splitext(full_file_path)[0]

rows_per_csv = int(sys.argv[2]) if len(sys.argv) > 2 else 5000

with open(filename) as infile:
    reader = csv.DictReader(infile, delimiter=';')
    header = reader.fieldnames
    rows = [row for row in reader]
    pages = []

    row_count = len(rows)
    start_index = 0
    # here, we slice the total rows into pages, each page having [row_per_csv] rows
    while start_index < row_count:
        pages.append(rows[start_index: start_index+rows_per_csv])
        start_index += rows_per_csv

    for i, page in enumerate(pages):
        with open('{}_{}.csv'.format(file_name, i+1), 'w+') as outfile:
            writer = csv.DictWriter(outfile, fieldnames=header, delimiter=';')
            writer.writeheader()
            for row in page:
                writer.writerow(row)

        print('DONE splitting {} into {} files'.format(filename, len(pages)))
```

Thanks to [Kelvin Tay](https://github.com/kelvintaywl) for his script, it saved me a lot of time! 🥳