Ver Tema - Importar Excel a base de datos usando un archivo ".tabs"

Alfumao · 2011-06-11 09:12 @425

¡Muchísimas gracias, explorer!
Esta noche mismo me pongo a convertir el Excel a SQL y te cuento cómo me ha ido (con el formato tabs y con el csv (haciendo los cambios que me has recomendado).
¡Eres un fenómeno!

Alfumao · 2011-06-12 07:01 @334

Hola, explorer,

He unido tu script al programa principal pero el intérprete de Perl me da error en la sintaxis de SQL cuando lo ejecuto...

Aquí te pongo las modificaciones:

Syntax: [ Download ] [ Hide ]

Using perl Syntax Highlighting

#!c:/perl/bin/perl.exe
use warnings;
use strict;
#use Modern::Perl;      # somos modernos
#use utf8;              # este programa está escrito en utf8
use autodie;            # es mejor morir que regresar con deshonor (proverbio Klingon)
 
#Leo fichero para trabajar
 
@ARGV == 1 or die "Uso: $0 <fichero tabs>\n";
my $fichero = shift;
print "$fichero";
 
############################################################
# Conectamos a la base de datos de MySQL 
 
use DBI;
my $database = 'fungo';
my $server = 'localhost';
my $user = 'root';
my $passwd = 'password';
 
my $homologs = DBI->connect("dbi:mysql:$database:$server", $user, $passwd);
my $sqlinit = $homologs->prepare("show tables");
$sqlinit->execute();
while (my $row = $sqlinit->fetchrow_arrayref) {
        print join("\t", @$row), "\n";
}
#################################codigo de explorer################################################
 
my $table;
my $numero_campos;
my @campos;
 
open my $TABS, '<', $fichero;
 
while (my $linea = <$TABS>) {
 
    next if $linea =~ m/^\s*$/;         # líneas vacías, las saltamos
 
    chomp $linea;                       # quitamos carácter fin de línea
 
    if ($linea =~ m/TABLE\s+(\w+)/) {   # si es comienzo de definición de tabla
        $table = $1;                    # guardamos su nombre
 
        print "DELETE FROM $table\n";
                
        # Borramos las filas de la tabla
 
        my $droprows = $homologs->prepare("DELETE FROM $table");
        $droprows->execute();
                
                
        $linea = <$TABS>;               # leemos las cabeceras
         
        $numero_campos = @campos = split " ", $linea;
 
        print "INSERT INTO $table (", join(',' , @campos), ') values (', join(',' , ('?') x $numero_campos), ")\n";
                
        #Insertamos los campos
 
        my $query = "INSERT INTO $table (", join(',', @campos) .") values ("." join(',' , ('?') x $numero_campos)"."?)";
        my $sql = $homologs->prepare($query);
        $sql->execute();
        next;
    }
            
    my @valores = split " ", $linea;    # todas las demás líneas serán valores a introducir
 
    if ($numero_campos == @valores) {   # comprobación de que hemos leído correctamente el número de campos
        print "INSERT INTO $table (" , join(',' , @campos), ') values (', join(',' , map { qq('$_') } @valores), ")\n";
        
        #Insertamos los valores
 
        my $lquery = "INSERT INTO $table (" , join(',' , @campos) .") values ("."join(',' , @valores)".")";
        my $sqlinea = $homologs->prepare($lquery);
        $sqlinea->execute();   
   }
    else {
        die "Error en línea $.\n";      # si no es así, avisamos y terminamos
    }
}
 
close $TABS;
###################################fin codigo explorer##########################################
 
# Break connection with MySQL database
 
$homologs->disconnect;
 
exit;

Concretamente me da este error:

Syntax: [ Download ] [ Hide ]

Using text Syntax Highlighting

c:\Perlprograms>ESQL.pl HML.tabs

HML.tabs

genomes

homologs

prueba

DELETE FROM homologs

INSERT INTO homologs (OrgId,Organism) values (?,?)

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the m

anual that corresponds to your MySQL server version for the right syntax to use

near '' at line 1 at C:\Perlprograms\ESQL.pl line 61, <$_[...]> line 2.

INSERT INTO homologs (OrgId,Organism) values ('PPL','human')

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the m

anual that corresponds to your MySQL server version for the right syntax to use

near '' at line 1 at C:\Perlprograms\ESQL.pl line 73, <$_[...]> line 3.

INSERT INTO homologs (OrgId,Organism) values ('POS','worm')

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the m

anual that corresponds to your MySQL server version for the right syntax to use

near '' at line 1 at C:\Perlprograms\ESQL.pl line 73, <$_[...]> line 4.

INSERT INTO homologs (OrgId,Organism) values ('LBI','mouse')

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the m

anual that corresponds to your MySQL server version for the right syntax to use

near '' at line 1 at C:\Perlprograms\ESQL.pl line 73, <$_[...]> line 5.

explorer · 2011-06-12 16:12 @716

He encontrado lo siguiente:

* línea 62:
my $query = "INSERT INTO $table (", join(',', @campos) .") values ("." join(',' , ('?') x $numero_campos)"."?)";

El problema es que estás usando las comas para unir expresiones. Debes usar el operador concatenación '.'. Y además, hay un carácter '?' demás:

my $query = "INSERT INTO $table (" . join(',', @campos) . ') values (' . join(',' , ('?') x $numero_campos) . ')';

* línea 64: no hay que hacer ningún execute(): no estás insertando ningún valor.

* línea 75. Pasa lo mismo. La línea correcta es:

my $lquery = "INSERT INTO $table (" . join(',' , @campos) .') values (' . join(',' , @valores) . ')';

* realmente... las líneas 75 y 76 sobran... no es necesario hacer un prepare(), porque ya lo has hecho en la línea 64. Entonces... deberías hacer la variable $sql de la línea 63 global (mover el my $sql; fuera del bucle y dejar la línea 63 como $sql = $homologs->prepare($query); ) Quitas las líneas 75 y 76, y cambias la 77, la variable $sqlinea por $sql.

Alfumao · 2011-06-13 04:44 @238

Hola de nuevo, explorer,

He hecho las modificaciones que me comentaste y el programa aparenta no tener errores, pero al correrlo, me vacía la tabla de la base de datos de SQL, pero da error de sintaxis a la hora de introducir los valores, con lo cual deja la tabla vacía...

Te vuelvo a poner el código aquí por si puedes ver dónde he metido la pata esta vez.

Este cambio no lo he hecho porque me daba muchos errores al intentarlo

Citar:

deberías hacer la variable $sql de la línea 63 global (mover el my $sql; fuera del bucle y dejar la línea 63 como $sql = $homologs->prepare($query); )

y por eso he dejado las dos órdenes (líneas 62 y 75).

Aquí te dejo el código:

Syntax: [ Download ] [ Hide ]

Using perl Syntax Highlighting

    #!c:/perl/bin/perl.exe
    use warnings;
    use strict;
    #use Modern::Perl;      # somos modernos
    #use utf8;              # este programa está escrito en utf8
    use autodie;            # es mejor morir que regresar con deshonor (proverbio Klingon)
     
    #Leo fichero para trabajar
     
    @ARGV == 1 or die "Uso: $0 <fichero tabs>\n";
    my $fichero = shift;
    print "$fichero\n";
     
    ############################################################
    # Conectamos a la base de datos de MySQL
     
    use DBI;
    my $database = 'fungo';
    my $server = 'localhost';
    my $user = 'root';
    my $passwd = 'password';
     
    my $homologs = DBI->connect("dbi:mysql:$database:$server", $user, $passwd);
    my $sqlinit = $homologs->prepare("show tables");
    $sqlinit->execute();
    while (my $row = $sqlinit->fetchrow_arrayref) {
            print join("\t", @$row), "\n";
    }
    #################################codigo de explorer################################################
     
    my $table;
    my $numero_campos;
    my @campos;
    
    open my $TABS, '<', $fichero;
     
    while (my $linea = <$TABS>) {
     
        next if $linea =~ m/^\s*$/;         # líneas vacías, las saltamos
     
        chomp $linea;                       # quitamos carácter fin de línea
     
        if ($linea =~ m/TABLE\s+(\w+)/) {   # si es comienzo de definición de tabla
            $table = $1;                    # guardamos su nombre
     
            print "DELETE FROM $table\n";
                   
            # Borramos las filas de la tabla
     
            my $droprows = $homologs->prepare("DELETE FROM $table");
            $droprows->execute();
                   
                   
            $linea = <$TABS>;               # leemos las cabeceras
             
            $numero_campos = @campos = split " ", $linea;
     
            print "INSERT INTO $table ( ", join(' , ' , @campos), ' ) values ( ', join(' , ' , ('?') x $numero_campos), " )\n";
                   
            #Insertamos los campos
     
            my $query = "INSERT INTO $table ( " . join(' , ', @campos) . ' ) values ( ' . join(' , ' , ('?') x $numero_campos) . ' )'; 
            my $sql = $homologs->prepare($query);
             
            next;
        }
               
        my @valores = split " ", $linea;    # todas las demás líneas serán valores a introducir
     
        if ($numero_campos == @valores) {   # comprobación de que hemos leído correctamente el número de campos
            print "INSERT INTO $table ( " , join(' , ' , @campos), ' ) values ( ', join(' , ' , map { qq('$_') } @valores), " )\n";
           
            #Insertamos los valores
     
            my $query = "INSERT INTO $table ( " . join(' , ' , @campos) .' ) values ( ' . join(' , ' , @valores) . ' )';  
            my $sql = $homologs->prepare($query);  
            $sql->execute();  
       }
        else {
            die "Error en línea $.\n";      # si no es así, avisamos y terminamos
        }
    }
     
    close $TABS;
    ###################################fin codigo explorer##########################################
     
    # Break connection with MySQL database
     
    $homologs->disconnect;
     
    exit;

Y aquí el error de sintaxis que me da:

Syntax: [ Download ] [ Hide ]

Using text Syntax Highlighting

c:\Perlprograms>ex.pl hml.tabs

hml.tabs

genomes

homologs

prueba

DELETE FROM homologs

INSERT INTO homologs ( OrgId , Organism ) values ( ? , ? )

INSERT INTO homologs ( OrgId , Organism ) values ( 'PPL' , 'human' )

DBD::mysql::st execute failed: Unknown column 'PPL' in 'field list' at C:\Perlpr

ograms\EX.pl line 77, <$_[...]> line 3.

INSERT INTO homologs ( OrgId , Organism ) values ( 'POS' , 'worm' )

DBD::mysql::st execute failed: Unknown column 'POS' in 'field list' at C:\Perlpr

ograms\EX.pl line 77, <$_[...]> line 4.

INSERT INTO homologs ( OrgId , Organism ) values ( 'LBI' , 'mouse' )

DBD::mysql::st execute failed: Unknown column 'LBI' in 'field list' at C:\Perlpr

ograms\EX.pl line 77, <$_[...]> line 5.

Muchas gracias por tu atención y tu ayuda explorer.

explorer · 2011-06-13 09:23 @433

Disculpas. Parece que no te lo he explicado bien. Prueba con esto (no probado):

Syntax: [ Download ] [ Hide ]

Using perl Syntax Highlighting

#!c:/perl/bin/perl.exe
use warnings;
use strict;
#use Modern::Perl;      # somos modernos
#use utf8;              # este programa está escrito en utf8
use autodie;            # es mejor morir que regresar con deshonor (proverbio Klingon)
 
use DBI;
 
# Leo fichero para trabajar
@ARGV == 1 or die "Uso: $0 <fichero tabs>\n";
my $fichero = shift;
print "[$fichero]\n";
 
# Conectamos a la base de datos de MySQL
my $database = 'fungo';
my $server   = 'localhost';
my $user     = 'root';
my $passwd   = 'password';
 
my $homologs = DBI->connect("dbi:mysql:$database:$server", $user, $passwd);
 
 
# Lista de las tablas disponibles en la base de datos
my $sqlinit  = $homologs->prepare("show tables");
$sqlinit->execute();
while (my $row = $sqlinit->fetchrow_arrayref) {
    print join("\t", @$row), "\n";
}
 
# Lectura del fichero tabs
my $sql;
my $sth;
my $table;
my @campos;
my $numero_campos;
 
open my $TABS, '<', $fichero;
 
while (my $linea = <$TABS>) {
 
    next if $linea =~ m/^\s*$/;         # líneas vacías, las saltamos
 
    chomp $linea;                       # quitamos carácter fin de línea
 
    if ($linea =~ m/TABLE\s+(\w+)/) {   # si es comienzo de definición de tabla
        $table = $1;                    # guardamos su nombre
 
        $sql = "DELETE FROM $table";    # Borramos las filas de la tabla
        $homologs->do($sql);
        print "$sql\n";
               
        $linea = <$TABS>;               # leemos las cabeceras
        $numero_campos = @campos = split " ", $linea;
 
                                        # preparamos la instrucción de inserción
        $sql = "INSERT INTO $table ( "
             . join(',', @campos)
             . ' ) values ( '
             . join(',' , ('?') x $numero_campos)
             . ' )'
             ;
        $sth = $homologs->prepare($sql);
        print "$sql\n";
         
        next;                           # saltamos a siguiente línea
    }
           
    my @valores = split " ", $linea;    # todas las demás líneas serán valores a introducir
 
    if ($numero_campos == @valores) {   # comprobación de que hemos leído correctamente el número de campos
        $sth->execute(@valores);        # insertamos los valores
        print "Insertando [@valores]\n";
   }
    else {
        die "Error en línea $.\n";      # si no es así, avisamos y terminamos
    }
}
 
close $TABS;
 
# Break connection with MySQL database 
$homologs->disconnect;
 
__END__

Alfumao · 2011-06-13 09:34 @440

Muchísimas gracias, explorer, parece que ahora sí que funciona.

Espero ser capaz de crear la base de datos SQL (la de tamaño grande) usando este programa y los conocimientos que me has facilitado al contestar a mis dudas.

Un saludo con mi más sincero agradecimiento.

Publicidad

Socializa