Re: CSV-SQL convertion

[Sam Roberts]

Quoting Scott Taylor <[EMAIL PROTECTED]>, who wrote:
> Hello,
> 
> Does anyone have a routine to convert from a csv file to an sql database, 
> that works.  I'm having a hard time with it.

Below is what I've done, don't know how it would work with other
database types, but works well for me so far.

It's execerpted code, so might not run standalone.

Good luck,
Sam

-- 
Sam Roberts <[EMAIL PROTECTED]>

$debug = 0;
$db = "DB";
$cmd = "import";

$dbh = DBI->connect("DBI:CSV:f_dir=$dbn", "user", "passwd",
                {
                        PrintError => 0,        # Don't print errors,
                        RaiseError => 1         # die instead.
                }
        );

[...]

# Import a CSV file.

$cmd =~ /^import$/ && do {

        local $table = shift;
        local $file = shift;

        if( defined($file) && $file ne "-" ) {
                close STDIN;
                open( STDIN, $file ) or die "open( $file ) failed: $!\n";
        }

        local $sth;

        while( <STDIN> ) {

                chomp;

                # Strip whitespace around the "," seperators.

                $_ =~ s/\s*,\s*/,/g;

                local @_ = split(/,/, $_);
                local $sth;
                local $sql;

                # Create $sth based on the number of values in the first line.
                if( ! defined($sth) ) {
                        # We need VALUES to look like VALUES( ?,?,?...) with one ? for 
every argument we
                        # will be inserting. Thus, @_ "?"s, split into an array, and 
joined with a column.

                        $sql = "INSERT INTO $table VALUES ( " . join(" , ", split(//, 
"?" x @_) ) . " )";

                        $sth = $dbh->prepare($sql);
                }

                local $" = ",";

                $debug && print "$sql @_\n";

                $sth->execute( @_ );
        }

        $dbh->disconnect();
};