CSV tab-delimited not space-delimited

[mellon85]

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 };

[ww]

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?

[mellon85]

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


perl	5.10.1
DBD::CSV	0.2600

[Tux]

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   <[e-mail]>

    * Fixed allow_whitespace with sep_char = TAB (RT#44402)

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

[Tux]

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.

[mellon85]

I have resorted to split/join on "\t" to load, change and store line by line.

thanks for the help mighty monks

[Khen1950fx]

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' };

[mellon85]

that's exactly what i did, and set the variable even after the table initialization, just to test even insane cases