Tools to assist in database import

[J65nko]

In a database import of over 200,000 records, I noticed that a length of 30 chars was not long enough to hold all email addresses.

With assistance of a short awk script I could quickly determine the optimal length:

$ awk -f field_length.awk import_file.csv

111628 065031051@rrrrrr.com
322626 0594500515@wwwwww.co.za
[snip]
361682 ｈｕｉｌｉｎ１９
７８@wrwrwrwr.ｃ
;ｏｍ
xxxxxxxxx@hotmail.com
146

This unbelievable longest mail address probably was caused by either an UTF-16 issue or data corruption.

The awk script:

BEGIN {
SIZE = 0 ;
FS = "," ;
}

# field 1 is the primary key
# field 2 is the email address
{
if (length($2) > SIZE ) {
   print $1, $2 ;
   SIZE = length($2) ;
   }
}

END {
print $2 ;
print SIZE ;
}

BTW sed(1) confirmed this:

$ sed -ne '/361682/p' masterlist_comma.csv 
361682 ｈｕｉｌｉｎ１９
７８@wrwrwrwr.ｃ
;ｏｍ

After editing out the offending line the result was:

 $ awk -f field_length.awk import_file2.csv
[snip]
58

So a field width of 60 seemed safe

[J65nko]

In tomorrows episode a Perl script to count the number of comma's, and thus the fields, in an .csv file.

[ocicat]

Originally Posted by J65nko

...a Perl script to count the number of comma's...

Consider the following (assuming comma are not found in the data...):

#!/bin/env perl

use strict;
use warnings;

# array of test strings
my @a = (',', ',,', ',,,');

my $max = 0;
foreach my $line (@a) {
    my @matches = ($line =~ /(\,)/g);
    $max = @matches if @matches > $max;
}

print "max #occurrences = $max\n";

TIMTOWTDI.

[J65nko]

The issue I had on hand was, that the import file was over 200,000 records. So I needed to know exactly which record/line did not have 56 fields.

I took advantage of the split function which in scalar context returns the number of fields resulting from the split:

#!/usr/bin/perl

use warnings ;
use strict ;

my @temp ;
my $nr  ;

while (<>)  {
  chomp ;
  $nr = (@temp = split /,/) ; 
  print "$nr $temp[0]\n" ; 
}

A sample run:

$ head -10 masterlist_comma.csv | ./split.pl

56 id
56 100625
56 100626
56 100627
56 100628
56 100629
56 100630
56 100631
56 100632
56 100633

The first field 'id' is the primary index (unique) so I choose to print that too, and thus enabling me to locate any culprits.

I checked these culprits by first redirecting to file fields_count.txt and then use grep(1)

$ grep -v '^56 ' fields_count.txt
$

No culprits in the first 10 records There also were not any in the other 200,000.

PS why grep -v?

    -v      Selected lines are those not matching any of the specified
             patterns.