Skip to content

Instantly share code, notes, and snippets.

@vapniks
Created April 11, 2018 03:59
Show Gist options
  • Save vapniks/0f93651afd58d955ac48cbd1b31fe498 to your computer and use it in GitHub Desktop.
Save vapniks/0f93651afd58d955ac48cbd1b31fe498 to your computer and use it in GitHub Desktop.
perl script for filling a postgresql database with data contained in .csv files
#!/usr/bin/perl
use DBI;
#example running
#csv_load_db.pl Changed.csv pat_regan_combined create_table.sql
$host="localhost";
$port=5433;
$db="geopolitical";
$user="foobar";
$pass="guess";
$incoming = $ARGV[0];
$table = $ARGV[1];
$create_sql = $ARGV[2];
unless(open OUTPUT, $incoming) {&logged("ERROR! Not open $incoming file - $!"); exit;}
@MAIN_DATA = <OUTPUT>;
unless(close OUTPUT) {&logged("ERROR! Not close $incoming file - $!"); exit;}
chomp(@MAIN_DATA);
@headers = split /\s*,\s*/,$MAIN_DATA[0];
shift @MAIN_DATA;
$dbh = DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;", $user, $pass, {AutoCommit => 0}) or die $DBI::errstr;
&create if $create_sql ne "";
$dbh->commit if &insert;
$dbh->disconnect;
exit;
sub insert
{
foreach my $str (@headers)
{
chomp($str);
$str =~ s/\n|\r//g;
$str = qq~"$str"~;
}
my $fields = lc(join (",",@headers));
foreach my $str (@MAIN_DATA)
{
my $sql = "INSERT INTO $table ($fields) values (";
@tmp = &mysplit("\s*,\s*",$str);
my @values = ();
foreach my $str (@tmp)
{
$str =~ s/\n|\r|\"|\'//g;
if($str eq " " || $str eq "" || $str eq "." || $str eq "!!!") {$str = "NULL"}
elsif($str =~ /^\w{3}([-|\/])(\d{2})$/)
{
my $year=$2;
my $char=$1;
my $mask="Mon".$char."yyyy";
if($2>10){$year+=1900;}
else{$year+=2000;}
$str =~ s/$char$2/$char$year/;
$str = qq~to_date('$str','$mask')~;
}
elsif($str =~ /\d+(.)\d+\1\d{4}/){$str = qq~to_date('$str','dd$1mm$1yyyy')~}
#elsif($str =~ /\d+\/\d+/){$str = qq~to_date('$str','mm/yy')~; print $str."\n";}
elsif($str =~ /^\d+$/){$str = $str;}
else {$str = $dbh->quote($str)}
push @values,$str;
}
$sql.=join(",",@values).")";
$sth=$dbh->prepare($sql);
$sth->execute;
if($DBI::errstr)
{
&logged("\nERROR in sql - $sql\n");
$dbh->rollback;
return 0;
}
}
#$dbh->commit;
print "COMPLETE\n";
return 1;
}
sub create
{
unless(open OUTPUT, $create_sql) {&logged("ERROR! Not open $create_sql file - $!"); exit;}
my @SQL = <OUTPUT>;
unless(close OUTPUT) {&logged("ERROR! Not close $create_sql file - $!"); exit;}
chomp(@SQL);
my $sql=lc(join(" ",@SQL));
$dbh->do($sql);
if ($DBI::errstr) {$dbh->rollback;}
else {print "TABLE CREATE!!\n";$dbh->commit;}
return 1;
}
sub logged($)
{
my $message = shift;
print $message."\n";
return 1;
}
sub mysplit($$)
{
my $sign = shift;
my $str = shift;
my @tmp = split /$sign/,$str;
my @res = ();
my $flag=0;
my $str_add="";
foreach my $el (@tmp)
{
$flag++ if $el =~ /"/;
push @res, $el if !$flag;
if ($flag>0 && $flag<2){$str_add.=$el.", "}
elsif($flag==2)
{
$str_add.=$el;
push @res,$str_add;
$flag=0;
$str_add="";
}
}
return @res;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment