I'm working with some CSV files generated by YouTube (so I cannot change the source structure). In the CSV file, some records span multiple lines. A hypothetical example with many other columns omitted for brevity is as follows:

video_id, upload_time, title, policy
oHg5SJYRHA0, 2007/05/15, "RickRoll'D", "Monetize in all countries except: CU, IR, KP, SD, SY
Track in countries: CU, IR, KP
Block in countries: SD, SY"
dQw4w9WgXcQ, 2009/10/24, "Rick Astley - Never Gonna Give You Up", "Monetize in all countries except: CU, IR, KP, SD, SY
Track in countries: CU, IR, KP, SD, SY"

A typical file contains hundreds of thousands of records if not millions of records (one file is 29.57GB in size), which is too big to process in one go, so I would like to split them up into smaller chunks for processing on separate machines. I've previously used split with -l on other report files and that works great when there is no newline in cells. In this case, if the split happens on a bad line (e.g.: line 4 of the example), then I have broken records in two files. Short of parsing the CSV file and then rebuilding it into multiple files, is there an effective way to split CSVs like this?


4 Answers 4


You're going to want to parse the CSV file to re-emit it in smaller chunks the way you want it. During this operation, maybe you even want to re-emit it in a different, more rigorous, well-defined format (like, oh, I don't know, json).

Your input file is in quite an unusual format. Python's csv module, for one, can't parse it, because it's got a multi-character delimiter: , (comma space) instead of the more common ,. Otherwise you'd be able to trivially parse and re-emit the file with 5 lines of Python.

You'll have to find another parser that works, or write a small one. First, try to find out what the specifics of the format you've got on your hands are, like what the quoting rules are (e.g. what happens when a field quoted with " contains ".)

  • I made the mistake in formatting while making the example file. There is no spaces following the comma. YouTube, with all of their crazy formattings, is at least pretty good with the quoted fields containing quotes (Per RFC, double quotes in double quoted fields should be escaped with an extra double quote; e.g.: "this is a double-quoted field that for whatever reason need an extra ""."). I'll accept this as the answer, and mark Glenn's comment as helpful! Thanks everyone!
    – Andy Huang
    Commented Mar 27, 2017 at 18:43

You will likely have to parse it. Here is a sample grep command piped into three sed commands which will combine the multi-line quoted strings onto one line (you can add a pipe to split -l at the end):

  grep -Eoz "((([^\",[:space:]]+|\"[!#-~[:space:]]+\"),? ?){4}[[:space:]]){1}" csvtest |  
  sed -e ':a' -e 'N' -e '$!ba' -e 's/\n\n/XXX new record XXX/g' |
  sed -e ':a' -e 'N' -e '$!ba' -e 's/\n/ /g' |
  sed -e "s/XXX new record XXX/\n/g"  

Breaking it down:

  • The -E grep option allows for extended regular expressions.
  • The -o grep option outputs only matching items
  • The -z grep option treats newline characters as \0
  • [^\",[:space:]]+ in the pattern matches unquoted items
  • \"[!#-~[:space:]]+\" in the pattern matches quoted items
  • you may need to update the quoted items pattern for any special cases where quoted strings contain quotes " or nonstandard character ranges. Simply add other character ranges after the ~
  • The first sed statement replaces two newlines with XXX new record XXX. The output of the grep generates two newlines between matches.
  • The second sed statement replaces each remaining single newline with a space.
  • The final sed replaces the previously added XXX new record XXX back to a single newline

You can add a split -l pipe onto the end of it all.


For CSV parsing, you're best advised to use an actual CSV parser. With recent versions of Perl's Text::CSV module, you can specify a multi-char field separator

#!/usr/bin/env perl
use strict;
use warnings;
use Text::CSV;
use Data::Dump; # just for this demonstration

# the "binary" option allows newlines in field values
my $csv = Text::CSV->new({binary=>1, sep=>", "})
  or die Text::CSV->error_diag;

open my $fh, "<", "test.csv";

while (my $row = $csv->getline($fh)) {
    print "next row:\n";
    dd $row; # or do something more interesting

close $fh;
  • Thank you for this, this is super helpful. I'll probably end up using this to split the file instead of vanilla split.
    – Andy Huang
    Commented Mar 27, 2017 at 18:44

I solved this problem by using csvkit's streaming commands to transform CSV rows into JSON objects (which escapes the newlines), split-ing the transformed JSON stream, then transforming the split JSON files back into CSV.

Gist with a runnable script is here: https://gist.github.com/vergenzt/d717bbad096dcf4be2151c66af47bf3a

General structure:

BASE="$(basename "$FILE" | cut -d. -f1)"

cat "$FILE" \
  | csvjson --stream --no-inference --snifflimit 0 \
  | gsplit -d --additional-suffix=.json -l $ROWS_PER_FILE -u - "${BASE}_"

for chunk_json in ${BASE}_*.json; do
  chunk_csv="$(basename "$chunk_json" .json).csv"
  in2csv -f ndjson --no-inference "$chunk_json" > "$chunk_csv"
  rm "$chunk_json"

According to csvkit docs:

  • csvjson uses streaming if "--stream --no-inference --snifflimit 0 is set and --skip-lines isn't set", and
  • in2csv uses streaming if "--format ndjson --no-inference is set ..."

For scale, on my machine (8GB RAM, 1.8GHz processor) it took about two and a half minutes to split a ~300MB CSV file containing ~4.5m lines but only ~180k rows (unquoted newlines).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .