3

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?

1

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 Mar 27 '17 at 18:43
1

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.

1

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 Mar 27 '17 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.