quietone has asked for the wisdom of the Perl Monks concerning the following question:

How would I check for null fields when writing to a database with a CSV file? 
$dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\\,});
$dbh->{'csv_tables'}->{'info'} = { 'file' => 'cust_sum_da.csv'};
$sth = $dbh->prepare("SELECT * FROM info");
$sth->execute();
my($one, $two, $three, $four, $five, $six, $seven, $eight, $nine, $ten
+, $eleven, $twelve, $thirteen, $fourteen, $fifteen, $sixteen);
$sth->bind_columns(undef, \$one, \$two, \$three, \$four, \$five, \$six
+, \$seven, \$eight, \$nine, \$ten, \$eleven, \$twelve, \$thirteen, \$
+fourteen, \$fifteen, \$sixteen);
while ($sth->fetch) {
$SqlStatement4 = ("INSERT INTO Customer_Information " .
  "(CustomerNumber, Name, Address1, Address2, City, State, ZipCode, Em
+ail, DefaultPassword) " .
"VALUES ('$one', '$two', '$three', '$four', '$six', '$seven', '$eight'
+, '$nine', '$sixteen') "); 
 $db->Sql($SqlStatement4);
}
$db->Close();
$sth->finish();
# print $SqlStatement;
exit;

[download]

Replies are listed 'Best First'.
Re: Null fields
by busunsl (Vicar) on Mar 27, 2001 at 19:46 UTC
    You get your values from a fetch.
    Fetch returns undef for NULL values.
[reply]
[d/l]
      And how are null fields held in a CVS file? Just curious.
[reply]

        Hmm, yes, 

        my $csv_data = "foo,,bar";
foreach ( split /,/, $csv_data ) {
 print "'$_' => ", defined($_), "\n";
}

        [download]

        Gives you 

        'foo' => 1
'' => 1
'bar' => 1

        So that middle value is not undef, but "". Of course, since DBI's results are supposed to be as independent as possible of the underlying data structure, there may -- in fact, should -- be routines within DBD::CSV to convert "" to undef, which is what you'd expect to get if you were using an actual RDBMS. That's assuming it even uses something so crude as that split to get at the data =) 

        /me is too lazy to look at the source code.
[reply]
[d/l]
[select]
        They are held as nothing between the commas, like this:
        12,"bla bla",,137

        The third is a NULL.

[reply]
Re: Null fields
by wardk (Deacon) on Mar 27, 2001 at 20:22 UTC

    Sounds like you are trying to prevent a null column from the CSV from being inserted into the RDBMS.

    If this is the case you can check for "null" after the fetch and before the insert via: 

    if ( $six eq '' ) {  # don't insert }
else { # ok to insert }

# or perhaps 
if ( length($six) == 0 ) { # don't insert }
else { # ok to insert }

    [download]

    Another option (possibly not available from the CVS) 

    INSERT INTO Customer_Information 
  (CustomerNumber, Name, Address1, Address2, City, 
   State, ZipCode, Email, DefaultPassword) 
VALUES (
   SELECT one, two, three, four, six, 
          seven, eight, nine , sixteen 
   FROM  info
   WHERE six is not null
   )

    [download]
[reply]
[d/l]
[select]
      Actually, what is happening is that when it read a null column, it skips the record. I need to write all the records, even if it has null columns in it, to the database.
[reply]
Re: Null fields
by arturo (Vicar) on Mar 27, 2001 at 19:58 UTC

    You can check to see whether a value is defined or not using the oddly-named defined function; DBI fetch will, as busunsl says, return the special undef value. 

    if (!defined($foo) ) {
 print "$foo is undefined.\n";
}

    [download]

    A suggestion: you have a whole bunch of variables named "one", "two" etc ... consider storing them in an array instead and an array slice to build that SQL statement up.

    HTH

    Philosophy can be made out of anything. Or less -- Jerry A. Fodor

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom