1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
#!/usr/bin/perl -w # -*- perl -*-
use strict;
use DBI;
$|++;
my $database = 'amnesty';
my $csvdata = './postcodes.csv';
my $tablename = 'postcode_locations';
my $user = 'amnesty';
my $password = 'sus1e';
open( INPUT, $csvdata) || die("can\'t open file $csvdata: $!");
print "found postcode data ok\n";
my $dbh = DBI->connect("dbi:mysql:database=$database",$user,$password);
print "connected to database ok\n" if $dbh;
my @cols = split(",",<INPUT>);
chomp @cols;
my $columns = join(", ", map { "$_ varchar(255)" } grep { $_ ne "postcode" } @cols);
print "data columns:\n$columns\n";
my $maketable = "create table $tablename (postcode varchar(12) NOT NULL, $columns, primary key(postcode));";
print "creating table with\n$maketable\n";
$dbh->do($maketable);
print "created $tablename table.\nInserting location data.";
my $counter;
my $insert = "INSERT INTO $tablename( " . join(",",@cols) . " ) values ( " . join(",", map { "?" } @cols) . ")";
my $sth = $dbh->prepare($insert);
while (<INPUT>) {
chomp;
my @data = split(/,/);
$sth->execute( @data );
$counter++;
print ".";
}
$sth->finish;
$dbh->disconnect;
print "\n\ndone.\n$counter points imported into sample data set.\n\n";
|
