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

I'm trying to use a tab-delimited CSV file with DBD::CSV but some fields contain spaces and the driver uses them as column separator scrambling all the columns.

my $csvh = DBI->connect("DBI:CSV:f_dir=.;csv_sep_char=\t;csv_eol=\n;") or die($DBI::errstr); $csvh->{'csv_tables'}->{'csv'} = { 'file' => file.csv };

Replies are listed 'Best First'.
Re: CSV tab-delimited not space-delimited
by ww (Archbishop) on Jun 25, 2010 at 09:51 UTC

    From the DBD::CSV Synopsis:

    csv_* All other attributes that start with csv_ and are not described ab +ove will be passed to Text::CSV_XS (without the csv_ prefix). these e +xtra options are most likely to be only useful for reading (select) h +andles. Examples: $dbh->{csv_allow_whitespace} = 1; $dbh->{csv_allow_loose_quotes} = 1; $dbh->{csv_allow_loose_escapes} = 1; See the Text::CSV_XS documentation for the full list and the docum +entation.

    If $dbh->{csv_allow_whitespace}    = 1; doesn't address your problem, you have something very odd going on.

    Does the "scrambling" occur on input or retrieval?
    Do you get any errors or warnings?
    Do you use RaiseError and what does it tell you?
    Are you sure your data has spaces (0x20) and not tabs?

      If $dbh->{csv_allow_whitespace} = 1; doesn't address your problem, you have something very odd going on.
      Then somethings odd is going on, adding the options right doesn't solve the problem.

      The scrambling occours on retrival from a SELECT. No errors or warnings (with RaiseError). And now I double checked, but the characters are spaces and tabs.

      Just to be precise the versions are

        What is your version of Text::CSV_XS? A problem as you describe has been fixed in version 0.63:

        2009-03-20 0.63 - H.Merijn Brand <> * Fixed allow_whitespace with sep_char = TAB (RT#44402)

        Current DBD::CSV is 0.29, and Text::CSV_XS is 0.73

        Enjoy, Have FUN! H.Merijn
Re: CSV tab-delimited not space-delimited
by Tux (Canon) on Jun 25, 2010 at 10:16 UTC

    The solution the other two posted is correct, but to make it even more readable and maintanable:

    my $dbh = DBI->connect ("dbi:CSV:", undef, undef, { f_ext => ".csv/r", csv_sep_char => "\t", csv_allow_whitespace => 1, RaiseError => 1, PrintError => 1, }) or die $DBI::errstr; # due to <c>f_ext</c> there is no need to specify the filename! my $sth = $dbh->prepare ("select * from file");

    Note however that a TAB is also considered whitespace, so your milage may vary and it may not work at all.

    allow_whitespace When this option is set to true, whitespace (TAB's and SPAC +E's) surrounding the separation character is removed when parsin +g. If either TAB or SPACE is one of the three major characters "sep_char", "quote_char", or "escape_char" it will not be considered whitespace.

    Enjoy, Have FUN! H.Merijn
      I have resorted to split/join on "\t" to load, change and store line by line.

      thanks for the help mighty monks
Re: CSV tab-delimited not space-delimited
by Khen1950fx (Canon) on Jun 25, 2010 at 09:59 UTC
    I'd try csv_allow_whitespace = 1; like this:
    #!/usr/bin/perl use strict; use warnings; use DBD::CSV; my $csvh = DBI->connect("DBI:CSV:f_dir=.; csv_sep_char=\t;csv_eol=\n;") or die($DBI::errstr); $csvh->{csv_allow_whitespace} = 1; $csvh->{'csv_tables'}{'csv'} = { file => 'file.csv' };
      that's exactly what i did, and set the variable even after the table initialization, just to test even insane cases