file - Splitting a 7 million row CSV by a specific column

How would I go about splitting a very large (7 million rows) CSV file into several different sheets/files by a specific numerical column. It should split into about 10 different files.

Use this Python 3 program:

#!/usr/bin/env python3
import csv
import os.path
import sys
from tkinter.filedialog import askopenfilename, askdirectory
from tkinter.simpledialog import askinteger

def split_csv_file(f, dst_dir, keyfunc):
    csv_reader = csv.reader(f)
    csv_writers = {}
    for row in csv_reader:
        k = keyfunc(row)
        if k not in csv_writers:
            csv_writers[k] = csv.writer(open(os.path.join(dst_dir, k),
                                             mode='w', newline=''))
        csv_writers[k].writerow(row)

def get_args_from_cli():
    input_filename = sys.argv[1]
    column = int(sys.argv[2])
    dst_dir = sys.argv[3]
    return (input_filename, column, dst_dir)

def get_args_from_gui():
    input_filename = askopenfilename(
        filetypes=(('CSV', '.csv'),),
        title='Select CSV Input File')
    column = askinteger('Choose Table Column', 'Table column')
    dst_dir = askdirectory(title='Select Destination Directory')
    return (input_filename, column, dst_dir)

if __name__ == '__main__':
    if len(sys.argv) == 1:
        input_filename, column, dst_dir = get_args_from_gui()
    elif len(sys.argv) == 4:
        input_filename, column, dst_dir = get_args_from_cli()
    else:
        raise Exception("Invalid number of arguments")
    with open(input_filename, mode='r', newline='') as f:
        split_csv_file(f, dst_dir, lambda r: r[column-1]+'.csv')
        # if the column has funky values resulting in invalid filenames
        # replace the line from above with:
        # split_csv_file(f, dst_dir, lambda r: binascii.b2a_hex(r[column-1].encode('utf-8')).decode('utf-8')+'.csv')

Save it as split-csv.py and run it from Explorer or from the command line.

For example to split su.csv based off column 1 and write the output files under dstdir use:

python split-csv.py su.csv 1 dstdir

If you run it without arguments, a Tkinter based GUI will prompt you to choose the input file, the column (1 based index) and the destination directory.

This could be as simple as this one-liner with awk:

awk -F ',' '{ print > ("split-" $1 ".csv") }' 7mil.csv

• The input file here is 7mil.csv
• The deciding column number is indicated with the dollar sign. If it was the third column, it would be $3 instead of $1
• The column value is used to generate the resulting file name. So for example, every line with the value 42 will be in a file named split-42.csv
• The field separator is the comma
  • which works because/if the value is numeric, and doesn't have any quotes that need to be stripped
  • but also requires that there are no commas in any strings in the file (at least not before the numeric column)

So this just reads every line and prints it to the file that corresponds to the value. Note that it adds to the file, so if you run it twice, all the data will be duplicated; so make sure there are no files with that naming pattern to start: del split-*.cvs

The hard part for trying this is installing awk on Windows. There's gawk for Windows and a few tips for running it here.