Search and replace

Question

Hi,

I have a really large SQL dump around 400MB.

It's in the following format, "INSERT INTO user VALUES('USERID', 'USERNAME', 'PASSWORD', '0', '0', 'EMAIL', 'GENDER', 'BIRTHDAY', '182', '13', '640', 'Married', 'Straight', '', 'Yes', 'Yes', '1146411153', '1216452123', '1149440844', '0', picture', '1', '0', '0', 'zip', '0', '', '0', '', '', '0')"

Is there anyway I can just get the email and password out from that, I want to import the users into another table.

Anyone know how I can do this and just get email-password stripped out from that content?

Thank you in advance

Answer 1

since your question is tagged "perl", one partial solution:

perl -ne '@m = split /,/; print $m[5], $m[2], "\n";' <your400MB.sqldump

Answer 2

A shorter Perl-oneliner-solution

perl -F, -anE 'say $F[5], $F[2];' file

Answer 3

As Kinopiko said, running split to parse CSV files is not a good idea (e.g. commas inside fields etc...). You should instead use a CSV parser, like this:

use strict;
use Text::CSV_XS; 
my $csv_obj = Text::CSV_XS->new({allow_whitespace=>1, quote_char => "'"})
           || die "Error\n"; 
while (<>) { 
    $csv_obj->parse($_); 
    my @fields = $csv_obj->fields();
    print "$fields[2],$fields[5]\n"
}

NOTE: Not tested since i'm now in an environment has no access to modern CSV_XS :(