1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
#!/usr/bin/perl # sollte in keinem Script fehlen use strict; use warnings; use DBI; use Data::Dumper; # Verzeichnis mit dem Script finden. # "." muss nicht immer das Verzeichnis mit dem Script sein use FindBin '$Bin'; # Sollte man machen. # es hilft die Dateinamen # von den Tabellennamen zu trennen. my %zuordnung=( prospects => { file => 'prospects.csv', col_names => ['Name', 'Address', 'Floors', 'Donated last year', 'Contact'], }, ); # Verbindung aufbauen my $dbh = DBI->connect("DBI:CSV:", undef, undef, { # Zeilenseparator csv_eol => "\x0A", # Spaltenseparator: csv_sep_char => ",", # Verzeichnis mit den Dateien f_dir => $Bin, # Zuordnung Dateiname -> Tabelle csv_tables => \%zuordnung, # bei Fehlern immer sterben: RaiseError => 1, } ) or die($DBI::errstr); # Testausgabe Nr.1: ganze CSV my $sth=$dbh->prepare('SELECT * FROM prospects'); $sth->execute(); while(my @row = $sth->fetchrow_array){ print join('; ',@row)."\n"; } $sth->finish; #Textausgabe Nr.2: sortierte CSV $sth = $dbh->prepare("SELECT * FROM prospects WHERE Name LIKE 'G%'"); $sth->execute(); print Dumper (my $row = $sth->fetchrow_hashref); while (my $row = $sth->fetchrow_hashref){ print qq(Name = $row->{name} Contact = $row->{contact})."\n"; } $sth->finish(); $dbh->disconnect();
2012-09-16T12:13:41 ?Woher zum Geier hat der das "name" in Kleinschrift her?
In Zeile 19 ist es grossgeschrieben!
In der CSV-Datei ist es grossgeschrieben!
Also bleibt die Preisfrage: "Warum der output von DBI-DBD-CSV-Datei jetzt in klein erfolgt? Das wär doch mal ne interessante Frage!
col_names => [qw( Name Address Floors Donated Contact )],
Quote"Name","Address","Floors","Donated","Contact"
"Charlotte French Cakes","1179 Glenhuntly Rd","1","Y","John"
"Glenhuntly Pharmacy","1181 Glenhuntly Rd","1","Y","Paul"
"Dick Wicks Magnetic Pain Relief","1183-1185 Glenhuntly Rd","1","Y","George"
"Gilmour's Shoes","1187 Glenhuntly Rd","1","Y","Ringo"
1 2 3 4 5 6 7 8 9 10 11 12 13
Name; Address; Floors; Donated; Contact Charlotte French Cakes; 1179 Glenhuntly Rd; 1; Y; John Glenhuntly Pharmacy; 1181 Glenhuntly Rd; 1; Y; Paul Dick Wicks Magnetic Pain Relief; 1183-1185 Glenhuntly Rd; 1; Y; George Gilmour's Shoes; 1187 Glenhuntly Rd; 1; Y; Ringo $VAR1 = { 'floors' => '1', 'name' => 'Glenhuntly Pharmacy', 'contact' => 'Paul', 'address' => '1181 Glenhuntly Rd', 'donated' => 'Y' }; Name = Gilmour's Shoes Contact = Ringo
2012-09-16T12:13:41 ?[...]
Also bleibt die Preisfrage: "Warum der output von DBI-DBD-CSV-Datei jetzt in klein erfolgt?
[...]
Quote[...]
Table- and column-names are case insensitive unless quoted. Column names will be sanitized unless "raw_header" is true;
[...]