Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/perl
- # programmaIlFuturo.it import hack from MIUR data to CMS MySQL DB - by Anak | anak@cpan.org - 22.08.2014 - GNU GPL v2
- use strict;
- use warnings;
- use feature 'say','switch';
- use DBI;
- use Spreadsheet::XLSX;
- our $version='0.1';
- my $xlsxDocument='anagrafe_istituti_principale_2014_2015_nuove-province.xlsx';
- my $excel=Spreadsheet::XLSX->new($xlsxDocument);
- my $dbh=dbConnect('user','password','dbname','host');
- my $dbTable='miur_data';
- foreach my $sheet (@{$excel->{Worksheet}}) {
- foreach my $row(1..$sheet->{MaxRow}) {
- my $regione=$sheet->{Cells}[$row][0]->{Val};
- my $provincia=$sheet->{Cells}[$row][1]->{Val};
- my $siglaProvincia=$sheet->{Cells}[$row][2]->{Val};
- my $codiceComune=$sheet->{Cells}[$row][3]->{Val};
- my $comune=$sheet->{Cells}[$row][4]->{Val};
- my $codiceIstituto=$sheet->{Cells}[$row][5]->{Val};
- my $denominazione=$sheet->{Cells}[$row][6]->{Val};
- my $indirizzo=$sheet->{Cells}[$row][7]->{Val};
- my $tipoIstituto=$sheet->{Cells}[$row][8]->{Val};
- my $tipologia=$sheet->{Cells}[$row][9]->{Val};
- my $codiceProvinciaNuova=$sheet->{Cells}[$row][10]->{Val};
- my $nomeProvinciaNuova=$sheet->{Cells}[$row][11]->{Val};
- my $siglaProvinciaNuova=$sheet->{Cells}[$row][12]->{Val};
- say '--> '.$regione.' > '.$provincia.' > '.$comune.' > '.$denominazione;
- my $sth = $dbh->prepare("
- INSERT INTO ".$dbTable." (
- regione,
- provincia,
- sigla,
- codice_comune,
- comune,
- codice_istituto,
- denominazione,
- indirizzo,
- tipo_istituto,
- tipologia,
- codice_provincia_nuova,
- nome_provincia_nuova,
- sigla_provincia_nuova
- )
- VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
- ") or die $_;
- $sth->execute(
- $regione,
- $provincia,
- $siglaProvincia,
- $codiceComune,
- $comune,
- $codiceIstituto,
- $denominazione,
- $indirizzo,
- $tipoIstituto,
- $tipologia,
- $codiceProvinciaNuova,
- $nomeProvinciaNuova,
- $siglaProvinciaNuova
- ) or die $_;
- $dbh->commit;
- }
- }
- say '--> Importazione completata con successo';
- exit;
- sub dbConnect {
- my ($dbUser,$dbPass,$dbName,$dbHost)=@_;
- my $dsn="DBI:mysql:database=$dbName;host=$dbHost";
- my $dbh=DBI->connect(
- $dsn,
- $dbUser,
- $dbPass,
- {RaiseError => 1}
- ) or die DBI->errstr;
- $dbh->{AutoCommit}=0;
- return $dbh
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement