in reply to Creating Table and Inserting Data from CSV

I'd suggest checking out Text::xSV. It is really cool. If the col names are in the file it can easily figure that out. If not, you just have to tell it the order of the col names. As for creating a table in MySQL from the CSV... there are a couple of way you can do it. What I'd do is use Text::xSV to get the column names, loop through that and write the SQL. Something like: 
use Text::xSV;
my $csv = new Text::xSV;
$csv->open_file("foo.csv");
$csv->read_header();

my $SQL = "CREATE TABLE $table_name(";
  foreach my $field ($csv->get_fields) {
 $SQL .= $field . " varchar(255),";


  }
  $SQL .= ");

# then connect to the database and run the query

[download]
Also in MySQL 4, it now supports a CSV engine. If you have that enabled, you might be able to just write your file into the mysql data dir and have it work. I haven't tried it, so I won't recommend it. One more thing, I'd suggest using the SQL load data infile or the commandline mysqlimport to actually import the data.

Replies are listed 'Best First'.
Re^2: Creating Table and Inserting Data from CSV
by awohld (Hermit) on Oct 04, 2005 at 19:48 UTC
    I know this may seem trivial for my app, but using the "get_fields" method returns the fields in random order, is there another similar module that will return them in order? I read Text::xSV on CPAN and I didn't see that it could do that, or from at least what I understood.
[reply]
      will regular split not work for this file? If it will then you can try something like this - 

      #!/usr/bin/perl -w

use strict;

use DBI;

my $line = "var1, var2, var3";

my $dbh = DBI->connect('DBI:mysql:test') or die "Couldn't connect to d
+atabase: " . DBI->errstr;
my $create = "create table dummy3 (" . join (',', map { $_ . ' varchar
+(255) ' } split /,/, $line) . ");";

my $sth = $dbh->prepare($create) or die "Couldn't prepare statement: "
+ . $dbh->errstr;
$sth->execute or die "Couldn't execute statement: " . $sth->errstr;;

      [download]
      mysql> desc dummy3
    -> ;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| var1  | varchar(255) | YES  |     | NULL    |       |
| var2  | varchar(255) | YES  |     | NULL    |       |
| var3  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

      [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom