How to split a large CSV file with no code?

user_8063089 0
Viewed 180

I have a CSV file which has nearly 22M records. I want to split this into multiple CSV files so that I can use it further.

I tried to open it using Excel(tried Transform Data Option as well)/Notepad++/Notepad, but all give me an error.

When I explore the options, I found that we can split the file using some coding methodologies like Java, Python, etc.. I am not much familiar with coding and want to know if there is any option to split the file without using any coding process. Also, since the file has client sensitive data I don't want to download/use any external tools.

Any help would be much appreciated.

csv split
1 Answers

I know you're concerned about security of sensitive data, and that makes you want to avoid external tools (even a nominally trusted tool like Google Big Query... unless your data is medical in nature).

I know you don't want a custom solution w/Python, but I don't understand why that is—this is a big problem, and CSVs can be tricky to handle.

Maybe your CSV is a "simple one" where there are no embedded line breaks, and the quoting is minimal. But if it isn't, you're going to want to a tool that's meant for CSV.

And because the file is so big, I don't see how you can do it without code. Even if you could load it into a trusted tool, how would you process the 22M records?

I look forward to seeing what else the community has to offer you.

The best I can think of based on my experience is exactly what you said you don't want.

It's a small-ish Python script that uses its CSV library to correctly read in your large file and write out several smaller files. If you don't trust this, or me, maybe find someone you do trust who can read this and assure you it won't compromise your sensitive data.

#!/usr/bin/env python3

import csv

MAX_ROWS = 22_000

# The name of your input
INPUT_CSV = 'big.csv'

# The "base name" of all new sub-CSVs, a counter will be added after the '-':
#   e.g., new-1.csv, new-2.csv, etc...
NEW_BASE = 'new-'


# This function will be called over-and-over to make a new CSV file
def make_new_csv(i, header=None):
    # New name
    new_name = f'{NEW_BASE}{i}.csv'

    # Create a new file from that name
    new_f = open(new_name, 'w', newline='')

    # Creates a "writer", a dedicated object for writing "rows" of CSV data
    writer = csv.writer(new_f)

    if header:
        writer.writerow(header)

    return new_f, writer


# Open your input CSV
with open(INPUT_CSV, newline='') as in_f:
    # Like the "writer", dedicated to reading CSV data
    reader = csv.reader(in_f)

    your_header = next(reader)  # see note below about "header"

    # Give your new files unique, and sequential names: e.g., new-1.csv, new-2.csv, etc...
    new_i = 1

    # Make first new file and writer
    new_f, writer = make_new_csv(new_i, your_header)

    # Loop over all input rows, and count how many
    # records have been written for each "new file"
    new_rows = 0
    for row in reader:
        if new_rows == MAX_ROWS:
            new_f.close()                                     # This file is full, close it and...
            break
            new_i += 1
            new_f, writer = make_new_csv(new_i, your_header)  # get a new file and writer
            new_rows = 0                                      # Reset row counter
        writer.writerow(row)
        new_rows +=1

    # All done reading input rows, close last file
    new_f.close()

There's also a fantastic tool I use daily for processing large CSVs, also with sensitive client contact and personally identifying information, GoCSV.

Its split command is exactly what you need:

Split a CSV into multiple files.

Usage:

gocsv split --max-rows N [--filename-base FILENAME] FILE

I'd recommend downloading it for your platform, unzipping it, putting a sample file with non-sensitive information in that folder and trying it out:

gocsv split --max-rows 1000 --filename-base New sample.csv

would end up creating a number of smaller CSVs, New-1.csv, New-2.csv, etc..., each with a header and no more than 1000 rows.

user_2468010
Related