dbi:csv how to

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

I have many csv file to orgnize. I try to use dbi:csv but not a clue.

 use DBI;
    $dbh = DBI->connect("DBI:CSV:f_dir=/base/")
        or die "Cannot connect: " . $DBI::errstr;
    $sth = $dbh->prepare("CREATE TABLE a (id INTEGER, name CHAR(10))")
        or die "Cannot prepare: " . $dbh->errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();
    $sth->finish();
    $dbh->disconnect();
[download]

When I use this code, where is the data base locates? Is that a file or something else? Can I use a existing csv file as a data base? I totally dont know how to ge the codes from cpan work for me.

Code tags added by GrandFather

Comment on dbi:csv how to Download Code

Replies are listed 'Best First'.
Re: dbi:csv how to by ptum (Priest) on Oct 27, 2006 at 22:48 UTC
It may be worth your time to read the documentation associated with DBD::CSV a little more thoroughly. I've never used DBD::CSV as a driver, but the use of the DBI module is pretty much the same everywhere -- you create a database handle, you prepare a statement on that handle, you execute the handle and you retrieve the result set. In your example, it looks as though you are creating a new table with the not very interesting name of 'a' and storing it in the /base directory on your file system. To use an existing CSV file, I think you want the third example in the documentation: `$dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\\;}); $dbh->{'csv_tables'}->{'info'} = { 'file' => 'info.csv'}; $sth = $dbh->prepare("SELECT * FROM info");` [download] Also, don't forget about Super Search; there seem to be lots of nodes on this topic, including this randomly selected one: Problem parsing semicolon delimited file with DBD::CSV.	[reply] [d/l]
Re: dbi:csv how to by madbombX (Hermit) on Oct 28, 2006 at 00:57 UTC
I recently had a similar task of having to organize CSV files. I originally started out using DBD::CSV because I wanted the SQL like access to the information. However, I ended up using Text::CSV_XS. I did this for a number of reasons. The first reason was that I was also able to use Text::CSV::Simple which allowed me an easy interface (since many of the CSV files I was working with had less than 15 columns in them). I also used it because it has a simple straightforward interface to handle quoted delimiters (ex. `val1,val2,"val,3",val4`) which is something I needed. You weren't specific as to what you were using it for, but if you are just merging CSV files, some of the modules are very useful (at least they were for what I needed them for).	[reply] [d/l]
Re: dbi:csv how to by Cabrion (Friar) on Oct 27, 2006 at 23:13 UTC
Each table would be a file in the /base directory. You would be better off with SQLite or Postgres or MySQL. CSV lacks a whole lot of DB goodness.	[reply]