From CSV to SQL statements…easy.

Context

These days I was working with some CSV data from Betfair, and I wanted to put it all in a MySQL database. So I decided to write a perl script to do the job. Also, I had to take a look into MySQL procedures and loop statements to rapidly make some changes to a table.

From CSV to SQL

I used Text:CSV to parse the CSV and get, one line at a time, the columns in the CSV lines.

my $in = $ARGV[0];
open CSV, "<", $in or die $!;
my $csv = Text::CSV->new();
...
while( <CSV>  ){
 if ($csv->parse($_)) {
 my @columns = $csv->fields();
...

I also had to make use of the substitution command to take timestamps in dd-mm-yyyy format to yyyy-mm-dd which is the MySQL format. I find this particularly amazing:

$date =~ s/^(\d\d)-(\d\d)-(\d\d\d\d)/$3-$2-$1/;

Very handy, don’t you think?

With the columns given by the CSV module, I just had to access each one and use it to write the SQL statement to a file.

The complete script, to my particular object is given below:

#!/usr/bin/env perl
use warnings;
use diagnostics;
use Text::CSV;
use strict;
use 5.8.8;
if($#ARGV != 2){
 print "./import_horse.pl <file_in.csv> <bd_table> <out_file.sql>\n";
 exit;
}
my $in = $ARGV[0];
my $out = $ARGV[2];
open OUT, ">", $out or die $!;
open CSV, "<", $in or die $!;
my $csv = Text::CSV->new();
my $statement = "";
while( <CSV>  ){
 if ($csv->parse($_)) {
 my @columns = $csv->fields();
 if($columns[8] eq ""){
 $columns[8] = "";
 }
 $columns[2] =~ s/^(\d\d)-(\d\d)-(\d\d\d\d)/$3-$2-$1/;
 $columns[6] =~ s/^(\d\d)-(\d\d)-(\d\d\d\d)/$3-$2-$1/;
 $columns[8] =~ s/^(\d\d)-(\d\d)-(\d\d\d\d)/$3-$2-$1/;
 $columns[14] =~ s/^(\d\d)-(\d\d)-(\d\d\d\d)/$3-$2-$1/;
 $columns[15] =~ s/^(\d\d)-(\d\d)-(\d\d\d\d)/$3-$2-$1/;
 $statement = "insert into $ARGV[1](sports_id, event_id, settled_date, country, full_description, course, scheduled_off, event, actual_off, selection_id, selection, odds, number_bets, volume_matched, latest_taken, first_taken, win_flag, in_play)
 values($columns[0], $columns[1], \"$columns[2]\", \"$columns[3]\", \"$columns[4]\", \"$columns[5]\", \"$columns[6]\", \"$columns[7]\", \"$columns[8]\", $columns[9], \"$columns[10]\", $columns[11], $columns[12], $columns[13], \"$columns[14]\", \"$columns[15]\", $columns[16], \"$columns[17]\"
 );";
 print OUT $statement . "\n";
 } else {
 my $err = $csv->error_input;
 print "Failed to parse line: $err";
 }
}
close(CSV);
close(OUT);

MySQL Procedures and Loops

After I had the data in the tables, I discovered that I needed to change the tables, including some more columns and populating those columns. So, I thought that the best way to achieve that was creating a loop in MySQL that did the job for each row. With a procedure I was able to do this…However, I wanted to share with you the solution for the problems I had:

- I was trying to create a procedure without changing the delimiter first:

CREATE PROCEDURE myloop()
BEGIN
...
'things ending with **;**'
...
...
END;

This won’t work…:( MySQL is waiting for ‘;‘ to end a statement. So, just has you’re putting some logic inside the procedure, ending with ‘;‘ and hit ENTER for another line, you get an error…

The correct way to do this, is changing the delimiter first to something else, write the procedure which can now make use of ‘;‘ and after the procedure, change the delimiter back to ‘;‘.

delimiter $$
CREATE PROCEDURE myloop()
BEGIN
...
'things ending with **;**'
...
...
END$$
delimiter ;

MySQL Automatic Backups, btw…

I wanted to have my databases automatically backed-up. I was just thinking of writing a script to do this, and then I found AutoMySQLBackup which was just what I was looking for. Just change a few variables inside, put it inside /etc/cron.daily/, make executable and, voilà, you just got:

multiple database backup
daily backups
rotation of your backups
compression of backups

…it is really a good tool…

References

Spread the word...

Miscellaneous