1DBD::CSV(3) User Contributed Perl Documentation DBD::CSV(3)
2
6 DBD::CSV - DBI driver for CSV files
7
9 use DBI;
10 # See "Creating database handle" below
11 $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
12 f_ext => ".csv/r",
13 RaiseError => 1,
14 }) or die "Cannot connect: $DBI::errstr";
15 # Simple statements
17 $dbh->do ("CREATE TABLE foo (id INTEGER, name CHAR (10))");
18 # Selecting
20 my $sth = $dbh->prepare ("select * from foo");
21 $sth->execute;
22 $sth->bind_columns (\my ($id, $name));
23 while ($sth->fetch) {
24 print "id: $id, name: $name\n";
25 }
26 # Updates
28 my $sth = $dbh->prepare ("UPDATE foo SET name = ? WHERE id = ?");
29 $sth->execute ("DBI rocks!", 1);
30 $sth->finish;
31 $dbh->disconnect;
33
35 The DBD::CSV module is yet another driver for the DBI (Database
36 independent interface for Perl). This one is based on the SQL "engine"
37 SQL::Statement and the abstract DBI driver DBD::File and implements
38 access to so-called CSV files (Comma Separated Values). Such files are
39 often used for exporting MS Access and MS Excel data.
40 See DBI for details on DBI, SQL::Statement for details on
42 SQL::Statement and DBD::File for details on the base class DBD::File.
43 Prerequisites
45 The only system dependent feature that DBD::File uses, is the "flock
46 ()" function. Thus the module should run (in theory) on any system with
47 a working "flock ()", in particular on all Unix machines and on Windows
48 NT. Under Windows 95 and MacOS the use of "flock ()" is disabled, thus
49 the module should still be usable.
50 Unlike other DBI drivers, you don't need an external SQL engine or a
52 running server. All you need are the following Perl modules, available
53 from any CPAN mirror, for example
54 http://search.cpan.org/
56 DBI A recent version of the DBI (Database independent interface for
58 Perl). See below why.
59 DBD::File
61 This is the base class for DBD::CSV, and it is part of the DBI
62 distribution. As DBD::CSV requires a matching version of DBD::File
63 which is (partly) developed by the same team that maintains
64 DBD::CSV. See META.json or Makefile.PL for the minimum versions.
65 SQL::Statement
67 A simple SQL engine. This module defines all of the SQL syntax for
68 DBD::CSV, new SQL support is added with each release so you should
69 look for updates to SQL::Statement regularly.
70 It is possible to run "DBD::CSV" without this module if you define
72 the environment variable $DBI_SQL_NANO to 1. This will reduce the
73 SQL support a lot though. See DBI::SQL::Nano for more details. Note
74 that the test suite does only test in this mode in the development
75 environment.
76 Text::CSV_XS
78 This module is used to read and write rows in a CSV file.
79 Installation
81 Installing this module (and the prerequisites from above) is quite
82 simple. The simplest way is to install the bundle:
83 $ cpan Bundle::DBD::CSV
85 Alternatively, you can name them all
87 $ cpan Text::CSV_XS DBI DBD::CSV
89 or even trust "cpan" to resolve all dependencies for you:
91 $ cpan DBD::CSV
93 If you cannot, for whatever reason, use cpan, fetch all modules from
95 CPAN, and build with a sequence like:
96 gzip -d < DBD-CSV-0.40.tgz | tar xf -
98 (this is for Unix users, Windows users would prefer WinZip or something
100 similar) and then enter the following:
101 cd DBD-CSV-0.40
103 perl Makefile.PL
104 make test
105 If any tests fail, let us know. Otherwise go on with
107 make install UNINST=1
109 Note that you almost definitely need root or administrator permissions.
111 If you don't have them, read the ExtUtils::MakeMaker man page for
112 details on installing in your own directories. ExtUtils::MakeMaker.
113 Supported SQL Syntax
115 All SQL processing for DBD::CSV is done by SQL::Statement. See
116 SQL::Statement for more specific information about its feature set.
117 Features include joins, aliases, built-in and user-defined functions,
118 and more. See SQL::Statement::Syntax for a description of the SQL
119 syntax supported in DBD::CSV.
120 Table- and column-names are case insensitive unless quoted. Column
122 names will be sanitized unless "raw_header" is true.
123
125 For most things, DBD-CSV operates the same as any DBI driver. See DBI
126 for detailed usage.
127 Creating a database handle (connect)
129 Creating a database handle usually implies connecting to a database
130 server. Thus this command reads
131 use DBI;
133 my $dbh = DBI->connect ("dbi:CSV:", "", "", {
134 f_dir => "/home/user/folder",
135 });
136 The directory tells the driver where it should create or open tables
138 (a.k.a. files). It defaults to the current directory, so the following
139 are equivalent:
140 $dbh = DBI->connect ("dbi:CSV:");
142 $dbh = DBI->connect ("dbi:CSV:", undef, undef, { f_dir => "." });
143 $dbh = DBI->connect ("dbi:CSV:f_dir=.");
144 We were told, that VMS might - for whatever reason - require:
146 $dbh = DBI->connect ("dbi:CSV:f_dir=");
148 The preferred way of passing the arguments is by driver attributes:
150 # specify most possible flags via driver flags
152 $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
153 f_schema => undef,
154 f_dir => "data",
155 f_dir_search => [],
156 f_ext => ".csv/r",
157 f_lock => 2,
158 f_encoding => "utf8",
159 csv_eol => "\r\n",
161 csv_sep_char => ",",
162 csv_quote_char => '"',
163 csv_escape_char => '"',
164 csv_class => "Text::CSV_XS",
165 csv_null => 1,
166 csv_bom => 0,
167 csv_tables => {
168 syspwd => {
169 sep_char => ":",
170 quote_char => undef,
171 escape_char => undef,
172 file => "/etc/passwd",
173 col_names => [qw( login password
174 uid gid realname
175 directory shell )],
176 },
177 },
178 RaiseError => 1,
180 PrintError => 1,
181 FetchHashKeyName => "NAME_lc",
182 }) or die $DBI::errstr;
183 but you may set these attributes in the DSN as well, separated by
185 semicolons. Pay attention to the semi-colon for "csv_sep_char" (as
186 seen in many CSV exports from MS Excel) is being escaped in below
187 example, as is would otherwise be seen as attribute separator:
188 $dbh = DBI->connect (
190 "dbi:CSV:f_dir=$ENV{HOME}/csvdb;f_ext=.csv;f_lock=2;" .
191 "f_encoding=utf8;csv_eol=\n;csv_sep_char=\\;;" .
192 "csv_quote_char=\";csv_escape_char=\\;csv_class=Text::CSV_XS;" .
193 "csv_null=1") or die $DBI::errstr;
194 Using attributes in the DSN is easier to use when the DSN is derived
196 from an outside source (environment variable, database entry, or
197 configure file), whereas specifying entries in the attribute hash is
198 easier to read and to maintain.
199 The default value for "csv_binary" is 1 (True).
201 The default value for "csv_auto_diag" is <1>. Note that this might
203 cause trouble on perl versions older than 5.8.9, so up to and including
204 perl version 5.8.8 it might be required to use ";csv_auto_diag=0"
205 inside the "DSN" or "csv_auto_diag =" 0> inside the attributes.
206 Creating and dropping tables
208 You can create and drop tables with commands like the following:
209 $dbh->do ("CREATE TABLE $table (id INTEGER, name CHAR (64))");
211 $dbh->do ("DROP TABLE $table");
212 Note that currently only the column names will be stored and no other
214 data. Thus all other information including column type (INTEGER or
215 CHAR (x), for example), column attributes (NOT NULL, PRIMARY KEY, ...)
216 will silently be discarded. This may change in a later release.
217 A drop just removes the file without any warning.
219 See DBI for more details.
221 Table names cannot be arbitrary, due to restrictions of the SQL syntax.
223 I recommend that table names are valid SQL identifiers: The first
224 character is alphabetic, followed by an arbitrary number of
225 alphanumeric characters. If you want to use other files, the file names
226 must start with "/", "./" or "../" and they must not contain white
227 space.
228 Inserting, fetching and modifying data
230 The following examples insert some data in a table and fetch it back:
231 First, an example where the column data is concatenated in the SQL
232 string:
233 $dbh->do ("INSERT INTO $table VALUES (1, ".
235 $dbh->quote ("foobar") . ")");
236 Note the use of the quote method for escaping the word "foobar". Any
238 string must be escaped, even if it does not contain binary data.
239 Next, an example using parameters:
241 $dbh->do ("INSERT INTO $table VALUES (?, ?)", undef, 2,
243 "It's a string!");
244 Note that you don't need to quote column data passed as parameters.
246 This version is particularly well designed for loops. Whenever
247 performance is an issue, I recommend using this method.
248 You might wonder about the "undef". Don't wonder, just take it as it
250 is. :-) It's an attribute argument that I have never used and will be
251 passed to the prepare method as the second argument.
252 To retrieve data, you can use the following:
254 my $query = "SELECT * FROM $table WHERE id > 1 ORDER BY id";
256 my $sth = $dbh->prepare ($query);
257 $sth->execute ();
258 while (my $row = $sth->fetchrow_hashref) {
259 print "Found result row: id = ", $row->{id},
260 ", name = ", $row->{name};
261 }
262 $sth->finish ();
263 Again, column binding works: The same example again.
265 my $sth = $dbh->prepare (qq;
267 SELECT * FROM $table WHERE id > 1 ORDER BY id;
268 ;);
269 $sth->execute;
270 my ($id, $name);
271 $sth->bind_columns (undef, \$id, \$name);
272 while ($sth->fetch) {
273 print "Found result row: id = $id, name = $name\n";
274 }
275 $sth->finish;
276 Of course you can even use input parameters. Here's the same example
278 for the third time:
279 my $sth = $dbh->prepare ("SELECT * FROM $table WHERE id = ?");
281 $sth->bind_columns (undef, \$id, \$name);
282 for (my $i = 1; $i <= 2; $i++) {
283 $sth->execute ($id);
284 if ($sth->fetch) {
285 print "Found result row: id = $id, name = $name\n";
286 }
287 $sth->finish;
288 }
289 See DBI for details on these methods. See SQL::Statement for details on
291 the WHERE clause.
292 Data rows are modified with the UPDATE statement:
294 $dbh->do ("UPDATE $table SET id = 3 WHERE id = 1");
296 Likewise you use the DELETE statement for removing rows:
298 $dbh->do ("DELETE FROM $table WHERE id > 1");
300 Error handling
302 In the above examples we have never cared about return codes. Of
303 course, this is not recommended. Instead we should have written (for
304 example):
305 my $sth = $dbh->prepare ("SELECT * FROM $table WHERE id = ?") or
307 die "prepare: " . $dbh->errstr ();
308 $sth->bind_columns (undef, \$id, \$name) or
309 die "bind_columns: " . $dbh->errstr ();
310 for (my $i = 1; $i <= 2; $i++) {
311 $sth->execute ($id) or
312 die "execute: " . $dbh->errstr ();
313 $sth->fetch and
314 print "Found result row: id = $id, name = $name\n";
315 }
316 $sth->finish ($id) or die "finish: " . $dbh->errstr ();
317 Obviously this is tedious. Fortunately we have DBI's RaiseError
319 attribute:
320 $dbh->{RaiseError} = 1;
322 $@ = "";
323 eval {
324 my $sth = $dbh->prepare ("SELECT * FROM $table WHERE id = ?");
325 $sth->bind_columns (undef, \$id, \$name);
326 for (my $i = 1; $i <= 2; $i++) {
327 $sth->execute ($id);
328 $sth->fetch and
329 print "Found result row: id = $id, name = $name\n";
330 }
331 $sth->finish ($id);
332 };
333 $@ and die "SQL database error: $@";
334 This is not only shorter, it even works when using DBI methods within
336 subroutines.
337
339 Metadata
340 The following attributes are handled by DBI itself and not by
341 DBD::File, thus they all work as expected:
342 Active
344 ActiveKids
345 CachedKids
346 CompatMode (Not used)
347 InactiveDestroy
348 Kids
349 PrintError
350 RaiseError
351 Warn (Not used)
352 The following DBI attributes are handled by DBD::File:
354 AutoCommit
356 Always on
357 ChopBlanks
359 Works
360 NUM_OF_FIELDS
362 Valid after "$sth->execute"
363 NUM_OF_PARAMS
365 Valid after "$sth->prepare"
366 NAME
368 NAME_lc
369 NAME_uc
370 Valid after "$sth->execute"; undef for Non-Select statements.
371 NULLABLE
373 Not really working. Always returns an array ref of one's, as
374 DBD::CSV does not verify input data. Valid after "$sth->execute";
375 undef for non-Select statements.
376 These attributes and methods are not supported:
378 bind_param_inout
380 CursorName
381 LongReadLen
382 LongTruncOk
383
385 In addition to the DBI attributes, you can use the following dbh
386 attributes:
387 DBD::File attributes
389 f_dir
390 This attribute is used for setting the directory where CSV files
391 are opened. Usually you set it in the dbh and it defaults to the
392 current directory ("."). However, it may be overridden in statement
393 handles.
394 f_dir_search
396 This attribute optionally defines a list of extra directories to
397 search when opening existing tables. It should be an anonymous list
398 or an array reference listing all folders where tables could be
399 found.
400 my $dbh = DBI->connect ("dbi:CSV:", "", "", {
402 f_dir => "data",
403 f_dir_search => [ "ref/data", "ref/old" ],
404 f_ext => ".csv/r",
405 }) or die $DBI::errstr;
406 f_ext
408 This attribute is used for setting the file extension.
409 f_schema
411 This attribute allows you to set the database schema name. The
412 default is to use the owner of "f_dir". "undef" is allowed, but not
413 in the DSN part.
414 my $dbh = DBI->connect ("dbi:CSV:", "", "", {
416 f_schema => undef,
417 f_dir => "data",
418 f_ext => ".csv/r",
419 }) or die $DBI::errstr;
420 f_encoding
422 This attribute allows you to set the encoding of the data. With
423 CSV, it is not possible to set (and remember) the encoding on a
424 column basis, but DBD::File now allows the encoding to be set on
425 the underlying file. If this attribute is not set, or undef is
426 passed, the file will be seen as binary.
427 f_lock
429 With this attribute you can specify a locking mode to be used (if
430 locking is supported at all) for opening tables. By default, tables
431 are opened with a shared lock for reading, and with an exclusive
432 lock for writing. The supported modes are:
433 0 Force no locking at all.
435 1 Only shared locks will be used.
437 2 Only exclusive locks will be used.
439 But see "KNOWN BUGS" in DBD::File.
441 DBD::CSV specific attributes
443 csv_class
444 The attribute csv_class controls the CSV parsing engine. This
445 defaults to "Text::CSV_XS", but "Text::CSV" can be used in some
446 cases, too. Please be aware that "Text::CSV" does not care about
447 any edge case as "Text::CSV_XS" does and that "Text::CSV" is
448 probably about 100 times slower than "Text::CSV_XS".
449 Text::CSV_XS specific attributes
451 csv_eol
452 csv_sep_char
453 csv_quote_char
454 csv_escape_char
455 csv_csv
456 The attributes csv_eol, csv_sep_char, csv_quote_char and
457 csv_escape_char are corresponding to the respective attributes of
458 the csv_class (usually Text::CSV_CS) object. You may want to set
459 these attributes if you have unusual CSV files like /etc/passwd or
460 MS Excel generated CSV files with a semicolon as separator.
461 Defaults are "\015\012"", ",", """ and """, respectively.
462 The csv_eol attribute defines the end-of-line pattern, which is
464 better known as a record separator pattern since it separates
465 records. The default is windows-style end-of-lines "\015\012" for
466 output (writing) and unset for input (reading), so if on unix you
467 may want to set this to newline ("\n") like this:
468 $dbh->{csv_eol} = "\n";
470 It is also possible to use multi-character patterns as record
472 separators. For example this file uses newlines as field
473 separators (sep_char) and the pattern "\n__ENDREC__\n" as the
474 record separators (eol):
475 name
477 city
478 __ENDREC__
479 joe
480 seattle
481 __ENDREC__
482 sue
483 portland
484 __ENDREC__
485 To handle this file, you'd do this:
487 $dbh->{eol} = "\n__ENDREC__\n" ,
489 $dbh->{sep_char} = "\n"
490 The attributes are used to create an instance of the class
492 csv_class, by default Text::CSV_XS. Alternatively you may pass an
493 instance as csv_csv, the latter takes precedence. Note that the
494 binary attribute must be set to a true value in that case.
495 Additionally you may overwrite these attributes on a per-table base
497 in the csv_tables attribute.
498 csv_null
500 With this option set, all new statement handles will set
501 "always_quote" and "blank_is_undef" in the CSV parser and writer,
502 so it knows how to distinguish between the empty string and "undef"
503 or "NULL". You cannot reset it with a false value. You can pass it
504 to connect, or set it later:
505 $dbh = DBI->connect ("dbi:CSV:", "", "", { csv_null => 1 });
507 $dbh->{csv_null} = 1;
509 csv_bom
511 With this option set, the CSV parser will try to detect BOM (Byte
512 Order Mark) in the header line. This requires Text::CSV_XS version
513 1.22 or higher.
514 $dbh = DBI->connect ("dbi:CSV:", "", "", { csv_bom => 1 });
516 $dbh->{csv_bom} = 1;
518 csv_tables
520 This hash ref is used for storing table dependent metadata. For any
521 table it contains an element with the table name as key and another
522 hash ref with the following attributes:
523 o All valid attributes to the CSV parsing module. Any of the can
525 optionally be prefixed with "csv_".
526 o All attributes valid to DBD::File
528 If you pass it "f_file" or its alias "file", "f_ext" has no effect,
530 but "f_dir" and "f_encoding" still have.
531 csv_*
533 All other attributes that start with "csv_" and are not described
534 above will be passed to "Text::CSV_XS" (without the "csv_" prefix).
535 These extra options are only likely to be useful for reading
536 (select) handles. Examples:
537 $dbh->{csv_allow_whitespace} = 1;
539 $dbh->{csv_allow_loose_quotes} = 1;
540 $dbh->{csv_allow_loose_escapes} = 1;
541 See the "Text::CSV_XS" documentation for the full list and the
543 documentation.
544 Driver specific attributes
546 f_file
547 The name of the file used for the table; defaults to
548 "$dbh->{f_dir}/$table"
550 eol
552 sep_char
553 quote_char
554 escape_char
555 class
556 csv These correspond to the attributes csv_eol, csv_sep_char,
557 csv_quote_char, csv_escape_char, csv_class and csv_csv. The
558 difference is that they work on a per-table basis.
559 col_names
561 skip_first_row
562 By default DBD::CSV assumes that column names are stored in the
563 first row of the CSV file and sanitizes them (see "raw_header"
564 below). If this is not the case, you can supply an array ref of
565 table names with the col_names attribute. In that case the
566 attribute skip_first_row will be set to FALSE.
567 If you supply an empty array ref, the driver will read the first
569 row for you, count the number of columns and create column names
570 like "col0", "col1", ...
571 Note that column names that match reserved SQL words will cause
573 unwanted and sometimes confusing errors. If your CSV has headers
574 that match reserved words, you will require these two attributes.
575 If "test.csv" looks like
577 select,from
579 1,2
580 the select query would result in "select select, from from test;",
582 which obviously is illegal SQL.
583 raw_header
585 Due to the SQL standard, field names cannot contain special
586 characters like a dot (".") or a space (" ") unless the column
587 names are quoted. Following the approach of mdb_tools, all these
588 tokens are translated to an underscore ("_") when reading the first
589 line of the CSV file, so all field names are 'sanitized'. If you do
590 not want this to happen, set "raw_header" to a true value and the
591 entries in the first line of the CSV data will be used verbatim for
592 column headers and field names. DBD::CSV cannot guarantee that any
593 part in the toolchain will work if field names have those
594 characters, and the chances are high that the SQL statements will
595 fail.
596 Currently, the sanitizing of headers is as simple as
598 s/\W/_/g;
600 Note that headers (column names) might be folded in other parts of
602 the code stack, specifically SQL::Statement, whose docs mention:
603 Wildcards are expanded to lower cased identifiers. This might
605 confuse some people, but it was easier to implement.
606 That means that in
608 my $sth = $dbh->prepare ("select * from foo");
610 $sth->execute;
611 while (my $row = $sth->fetchrow_hashref) {
612 say for keys %$row;
613 }
614 all keys will show as all lower case, regardless of the original
616 header.
617 It's strongly recommended to check the attributes supported by
619 "Metadata" in DBD::File.
620 Example: Suppose you want to use /etc/passwd as a CSV file. :-) There
622 simplest way is:
623 use DBI;
625 my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
626 f_dir => "/etc",
627 csv_sep_char => ":",
628 csv_quote_char => undef,
629 csv_escape_char => undef,
630 });
631 $dbh->{csv_tables}{passwd} = {
632 col_names => [qw( login password uid gid realname
633 directory shell )];
634 };
635 $sth = $dbh->prepare ("SELECT * FROM passwd");
636 Another possibility where you leave all the defaults as they are and
638 override them on a per table basis:
639 require DBI;
641 my $dbh = DBI->connect ("dbi:CSV:");
642 $dbh->{csv_tables}{passwd} = {
643 eol => "\n",
644 sep_char => ":",
645 quote_char => undef,
646 escape_char => undef,
647 f_file => "/etc/passwd",
648 col_names => [qw( login password uid gid
649 realname directory shell )],
650 };
651 $sth = $dbh->prepare ("SELECT * FROM passwd");
652 Driver private methods
654 These methods are inherited from DBD::File:
655 data_sources
657 The "data_sources" method returns a list of sub-directories of the
658 current directory in the form "dbi:CSV:directory=$dirname".
659 If you want to read the sub-directories of another directory, use
661 my $drh = DBI->install_driver ("CSV");
663 my @list = $drh->data_sources (f_dir => "/usr/local/csv_data");
664 list_tables
666 This method returns a list of file-names inside $dbh->{directory}.
667 Example:
668 my $dbh = DBI->connect ("dbi:CSV:directory=/usr/local/csv_data");
670 my @list = $dbh->func ("list_tables");
671 Note that the list includes all files contained in the directory,
673 even those that have non-valid table names, from the view of SQL.
674 See "Creating and dropping tables" above.
675
677 ยท The module is using flock () internally. However, this function is
678 not available on some platforms. Use of flock () is disabled on
679 MacOS and Windows 95: There's no locking at all (perhaps not so
680 important on these operating systems, as they are for single users
681 anyways).
682
684 Tests
685 Aim for a full 100% code coverage
686 - eol Make tests for different record separators.
688 - csv_xs Test with a variety of combinations for
689 sep_char, quote_char, and escape_char testing
690 - quoting $dbh->do ("drop table $_") for DBI-tables ();
691 - errors Make sure that all documented exceptions are tested.
692 . write to write-protected file
693 . read from badly formatted csv
694 . pass bad arguments to csv parser while fetching
695 Add tests that specifically test DBD::File functionality where that
697 is useful.
698 RT Attack all open DBD::CSV bugs in RT
700 CPAN::Forum
702 Attack all items in http://www.cpanforum.com/dist/DBD-CSV
703 Documentation
705 Expand on error-handling, and document all possible errors. Use
706 Text::CSV_XS::error_diag () wherever possible.
707 Debugging
709 Implement and document dbd_verbose.
710 Data dictionary
712 Investigate the possibility to store the data dictionary in a file
713 like .sys$columns that can store the field attributes (type, key,
714 nullable).
715 Examples
717 Make more real-life examples from the docs in examples/
718
720 DBI, Text::CSV_XS, SQL::Statement, DBI::SQL::Nano
721 For help on the use of DBD::CSV, see the DBI users mailing list:
723 http://lists.cpan.org/showlist.cgi?name=dbi-users
725 For general information on DBI see
727 http://dbi.perl.org/ and http://faq.dbi-support.com/
729
731 This module is currently maintained by
732 H.Merijn Brand <h.m.brand@xs4all.nl>
734 in close cooperation with and help from
736 Jens Rehsack <sno@NetBSD.org>
738 The original author is Jochen Wiedmann. Previous maintainer was Jeff
740 Zucker
741
743 Copyright (C) 2009-2018 by H.Merijn Brand Copyright (C) 2004-2009 by
744 Jeff Zucker Copyright (C) 1998-2004 by Jochen Wiedmann
745 All rights reserved.
747 You may distribute this module under the terms of either the GNU
749 General Public License or the Artistic License, as specified in the
750 Perl README file.
751perl v5.28.0 2018-04-30 DBD::CSV(3)