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

Hello monks:

This is the first time I've used DBD::AnyData, and I've run into a problem, I hope there's a way around it. I've tried this with tab-delimited files and with CSVs.

I have rows of data like so:
name1,value1,status1,info1 info2 info3,more stuff
name2,value1,status1,info4 <newline><newline> info5,more stuff

[download]
My query stops as soon as it sees the "\n\n", and I want it to keep going to the end of the file.

I *think* I could normalize the data by replacing the "\n\n" values with <space>, or hack around with the value of $/, or something like that, but I wondered if there was something in the DBI bag that would help.

Any suggestions would be welcome.

Replies are listed 'Best First'.
Re: Help: column with newlines foils DBD::Anydata?
by jZed (Prior) on Jun 30, 2004 at 17:46 UTC
    The next version of DBD:AnyData will support embedded newlines. For now you can use DBD::CSV instead which uses the same SQL engine as DBD::AnyData and already supports embedded newlines and works fine with tab. Another option is to stick with DBD::AnyData but redefine the eol (end of line) to something other than "\n". For example, on windows, this script creates an queries a tab-separated file containing fields with embedded newlines: 
    #!perl -w
use strict;
use DBI;
my $dbh=DBI->connect('dbi:AnyData(RaiseError=1):');
$dbh->ad_catalog( 'test'
                , 'CSV'
                , 'newline.csv'
                , {sep_char=>"\t",eol=>"\015"}
                );
my %sql = ( drop   => 'DROP TABLE test'
          , create => 'CREATE TABLE test (id INT, phrase VARCHAR(30))'
          , insert => 'INSERT INTO test VALUES(?,?)'
          , query  => 'SELECT * FROM test'
          );
my $insert = $dbh->prepare( $sql{insert} );
my $query  = $dbh->prepare( $sql{query} );

eval {  $dbh->do( $sql{drop} ) };
$dbh->do( $sql{create} );
$insert->execute(1,"foo\n\nbar");
$insert->execute(2,'baz');
$query->execute;
while (my $row=$query->fetch) {
    print "[$_] " for @$row;
    print "~\n";
}

    [download]
[reply]
[d/l]
      Thanks jZed, DBD::CSV vs. a tab-separated file worked just fine.
[reply]

Back to Seekers of Perl Wisdom