April 2009 Archives

Here is a quick script I wrote to load SQLite tables from CSV files.  I exported the files from Microsoft Access.  The first row has to have correct column names.

Text::xSV is the best CSV file handler I could find on CPAN.  It correctly handles files with embedded newlines as well as very large files.  SQL::Abstract makes it very easy to use straight DBI to speed up the load without going through an ORM. 

I prepare each row separately because I may have cases where there are trailing unused fields, and some Microsoft programs (Outlook comes to mind) do not attempt to output empty fields for them.  If the speed difference is important, you may want to try to guarantee the exact number of fields in each row so you can prepare once.

#!/usr/bin/perl

# load_csv.pl - Load a db table from a csv file.
#               First row must be field names.
# 04/28/2009  W. Ruppert

use strict;
use warnings;
use Text::xSV;
use DBI;
use SQL::Abstract;

$| = 1;

sub usage {
    my ($msg) = @_;
    warn "$msg\n" if $msg;
    die "Usage: load_csv.pl db table csvfile\n";
}

# get parms
my $db        = shift || usage "No database name";
my $table     = shift || usage "No table name";
my $data_file = shift || usage "No csv data file";

usage "No such file: $db"        unless -e $db;
usage "No such file: $data_file" unless -e $data_file;

# setup csv file
my $csv = new Text::xSV;
$csv->open_file("$data_file");
$csv->read_header();

my $sql = SQL::Abstract->new;

# connect to db
my $dbh = DBI->connect("dbi:SQLite:$db", "","",
        { RaiseError => 1, PrintError => 0 }
) or die "can't connect\n";

$dbh->do('begin');

my $max_commit  = 1000;
my $inserted    = 0;

# process csv rows
while (my %fieldvals = $csv->fetchrow_hash) {

    # SQL::Abstract sets up the DBI variables
    my($stmt, @bind) = $sql->insert($table, \%fieldvals);

    # insert the row
    my $sth = $dbh->prepare($stmt);
    $inserted += $sth->execute(@bind);

    # progress bar
    print "*"            unless $inserted % 10;
    print " $inserted\n" unless $inserted % 500;

    # commit every once in a while
    unless ($inserted % $max_commit) {
        $dbh->do('commit');
        $dbh->do('begin');
    }
}

$dbh->do('commit');
$dbh->disconnect;

print "\nInserted $inserted records\n";
exit 1;

Firefox FOUC fixed

| No Comments
Had a nasty FOUC (flash of unstyled content) on one of our websites.  It was real bad in Firefox, didn't check it in IE. 

The page loads a fairly small amount of CSS (28k) in the head.  No scripts though.  Our main site does not have this problem, and it has a small script in the head (to prevent external framing), so I added that but no difference.

The main site also has a scipt at the top of the body to pre-load the "Cool DHTML Tooltip" from www.dynamicdrive.com.  Adding that script fixed the FOUC:

<body>
<div id="dhtmltooltip"></div>
<script type="text/javascript" src="js/tooltip.js"></script>

Now there is a blank screen for a while, then the formatted page loads.  I guess the script stops the rendering from starting too fast.  I don't actually use the tooltip script on this site, but it is only 4k.

Determine Image Size from URL

| No Comments
Need to get the size of an image on the web without storing it.  Done by pulling the image with LWP::Simple and giving the content buffer to Image::Size.



#!/usr/bin/perl
# Get dimensions of web image
# 04/17/2009  WR

use strict;
use warnings;
use LWP::Simple;
use Image::Size;

my @urls = (
	"http://www.google.com/intl/en_ALL/images/logo.gif",
	"http://l.yimg.com/a/i/ww/beta/y3.gif",
	"http://www.example.com/nothing.gif",
	"http://graphics8.nytimes.com/images/misc/nytlogo379x64.gif",
);

URL:
for my $url (@urls) {
	my $image = get $url;
	unless (defined $image) {
		warn "Couldn't get $url!\n";
		next URL;
	}
	my ($width, $height) = imgsize(\$image);
	printf "%4d %4d   %s\n", $width, $height, $url;
}

exit 1;

About this Archive

This page is an archive of entries from April 2009 listed from newest to oldest.

May 2009 is the next archive.

Find recent content on the main index or look in the archives to find all content.

Pages

OpenID accepted here Learn more about OpenID
Powered by Movable Type 4.38