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

I'm looking for the best way to transform CSV data into a series of SQL statements. A typical line is something like this:
"Félagsmálaráđuneytiđ","7101","1989","A","1","1","1939","1949","Bréfadagbók","A - Bréfadagbćkur","","" Anyone?

Replies are listed 'Best First'.
GIGO: CSV to SQL?
by demerphq (Chancellor) on Sep 15, 2001 at 14:59 UTC
    Hi spm,

    Nobody is going to be able to help you until you tell us what a sample SQL statement looks like.

    We dont know if you want select statements (unlikely) or update statements (maybe) or insert statements (my bet). Nor do we know the tables you want these statements to interact with, and so forth.

    Perlmonks is just like a computer program, garbage in garbage out. So give us some idea of what you need and well be able to help...

    :-)

    Yves
    --
    You are not ready to use symrefs unless you already know why they are bad. -- tadmc (CLPM)

[reply]
(bbfu) (DB's import) Re: CSV to SQL?
by bbfu (Curate) on Sep 15, 2001 at 18:10 UTC

    Most databases/database clients support some sort of import facility. And most of those support CSV's. It might be worth looking into whether your database supports that.

    bbfu
    Seasons don't fear The Reaper.
    Nor do the wind, the sun, and the rain.
    We can be like they are.

[reply]
Re: CSV to SQL?
by runrig (Abbot) on Sep 15, 2001 at 20:30 UTC
    I'd look into what bbfu says above first about an import facility for your database, and if there isn't any import facility, or if there's some filtering/munging that the import facility just doesn't provide that you want to do in perl (then again you can filter/munge and output another CSV file to import, which if its a large dataset, can be a faster way to do it), then you could look into the AnyData module (or another CSV module), and the insert_hash example from the latest DBI perldocs.
[reply]
Re: CSV to SQL?
by spm (Acolyte) on Sep 15, 2001 at 15:04 UTC
    Ohh sorry... I knew I'd forgot something :) I want something like this: 

    INSERT INTO skjalaskra ('id', 'skjalamyndari', 'afhendingarar', 
'skjalaflokkur', 'kassanumer', 'ork', 'fraar', 'tilar', 'innihald', 
'yfirflokkur', 'athugasemdir') VALUES ('2', 'Félagsmálaráđuneytiđ', 
'7101', '1989', 'A', '2', '1', '1950', '1967', 
'Bréfadagbók - sendendur', 'A - Bréfadagbćkur', '', '');

    [download]

    ... (I think that's not the same line from the CSV, but it's the same context..)

[reply]
[d/l]
      Ok. Here you go.

      BTW: CPAN is your friend...

      Oh yeah, I removed the ID column, for one you dont say where the id comes from and two if it was my table that column would be an identity column.

      use strict;
use warnings;
use Text::CSV_XS;
my $csv=Text::CSV_XS->new({binary=>1});
while (<DATA>) {
   chomp;
   last unless $csv->parse($_);
   my @vals=$csv->fields;
   print "INSERT INTO skjalaskra ('skjalamyndari', ".
      "'afhendingarar', 'skjalaflokkur', 'kassanumer'," .
      "'ork', 'fraar', 'tilar', 'innihald', 'yfirflokkur',".
      " 'athugasemdir')\n".
      " VALUES ('".join("', '",@vals)."')\n";
}

      [download]
      HTH.

      Yves
      --
      You are not ready to use symrefs unless you already know why they are bad. -- tadmc (CLPM)

[reply]
[d/l]

Back to Seekers of Perl Wisdom