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

Alfumao · 2011-06-06 05:43 @280

Buenos días,

Necesito pasar la información de una tabla Excel a una base de datos en MySQL.

Para ello he modificado un script publicado por James Tisdall que permite realizar dicho proceso convirtiendo la tabla Excel a un archivo ".tabs" como el adjunto.

En teoría se conseguiría con el script que os pongo, pero resulta que no hay manera y me estoy volviendo loco.

Os agradecería sugerencias y/o modificaciones bien en el script o en el formato de archivo al que convertir el Excel.

Necesito conseguir este paso lo antes posible para no perder muchos meses de trabajo.

Aquí va el script que publicó J. Tisdall y yo modifiqué:

Syntax: [ Download ] [ Hide ]

Using perl Syntax Highlighting

#!c:/perl/bin/perl.exe
use warnings;
use strict;
 
# Make connection with MySQL database
 
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";
}
 
my $flag = 0;
my $table;
my @tables;
my $sql;
 
while(<>) {
    # skip blank lines
    if(/^\s*$/) {
        next;
 
    # begin new table
    }elsif(/^TABLE\t(\w+)/) {
        $flag = 1;
        $table = $1;
        push(@tables, $table);
        # Delete all rows in database table
        my $droprows = $homologs->prepare("delete from $table");
        $droprows->execute();
 
    # get fieldnames, prepare SQL statement
    } elsif($flag == 1) {
        $flag = 2;
        my @fieldnames = split;
        my $query = "insert into  $table (" . join(",", @fieldnames) . ") values (" . "?, " x (@fieldnames-1) . "?)";
        $sql = $homologs->prepare($query);
 
    # get row, execute SQL statement
    } elsif($flag == 2) {
        my @fields = split;
        $sql->execute( @fields);
    }
}
 
# Check if tables were updated
 
foreach my $table (@tables) {
        print "\nTable: $table\n\n";
        my $query = "select * from $table";
        my $sql = $homologs->prepare($query);
        $sql->execute();
 
        while (my $row = $sql->fetchrow_arrayref) {
            print join("\t", @$row), "\n";
        }
}
 
# Break connection with MySQL database
 
$homologs->disconnect;
 
exit;
 
 
 

Y este seria un ejemplo de archivo ".tabs" obtenido del Excel:

Syntax: [ Download ] [ Hide ]

Using text Syntax Highlighting

TABLE OXTRY

Organism Ndufs1 Ndufs2 Ndufs3 Ndufs4 Ndufs6 Ndufs7 Ndufs8

NCR NCU01765 NCU02534   NCU04074 NCU05221 NCU00484 NCU03953 NCU05009

FOX FOXG_07987 FOXG_00680 FOXG_06006 FOXG_10434 FOXG_01910 FOXG_05196 FOXG_04085 

FGR FGSG_05198 FGSG_00376 FGSG_09234 FGSG_02474 FGSG_06909 FGSG_10276 FGSG_07867 

FVE FVEG_04906 FVEG_00831 FVEG_03873 FVEG_09088 FVEG_14032 FVEG_03763 FVEG_07199 

AFU Afu5g04370 Afu2g13710 Afu6g08810 None Afu5g02080 Afu4g05860 Afu1g06610

HCA HCAG_06929 HCAG_04623 HCAG_05764 HCAG_00972  HCAG_00338 HCAG_03991 HCAG_05509 

DHA DEHA0G06721g DEHA0F11077g DEHA0A12265g DEHA0F22154g DEHA0B11770g DEHA0F02838g DEHA0D03652g

El programa Perl, si se llama tabla.load, según las instrucciones del autor, debería ejecutarse de la siguiente forma:

%perl tabla.load homologs.tabs

Yo lo ejecuto colocándolo en la carpeta

"c:\perl\bin"

¿Puede ser que lo esté ejecutando mal además del problema del formato?

Ojalá podáis ayudarme, alguien

explorer · 2011-06-06 06:04 @294

Bueno, este código no hace la transformación de Excel a formato tabs, sino que hace la parte de pasar del formato .tabs a la base de datos.

Por lo que veo, el programa parece que está bien. Lo único curioso, es que en la línea 32 dice que espera que la línea, dentro del tabs, que comience por TABLE, debe estar separada por el nombre de la tabla por un carácter tabulador. Y en el ejemplo que has puesto, me parece que no lo hay.

Yo cambiaría el '\t' por un '\s+'.

Alfumao · 2011-06-06 07:35 @358

Hola explorer,

He modificado la línea 32 como me dijiste, y he descargado de la página de J.Tisdall el archivo .tabs que tiene de ejemplo y que escribo bajo estas líneas:

Syntax: [ Download ] [ Hide ]

Using text Syntax Highlighting

TABLE   ORGANISM

OrgId   Organism

1       human

2       worm

3       mouse

TABLE   GENES

GeneId  Gene    Date

118     aging   1984-07-13

9223    wrinkle 1987-08-15

273     hairy   1990-09-30

TABLE   VARIANTS

VarId   OrgId   GeneId

1       1       118

2       2       118

3       1       9223

4       3       9223

5       3       273

Aún usando este archivo no consigo que el programa me cree una base de datos en el MySQL de XAMPP.

¿Podría estar el fallo en la forma de ejecutarlo según las instrucciones que expuse en la primera entrada del post?

Citar:

El programa Perl, si se llama tabla.load, según las instrucciones del autor, debería ejecutarse de la siguiente forma:

%perl tabla.load homologs.tabs

Yo lo ejecuto colocándolo en la carpeta

"c:\perl\bin"

Lo único que consigo es que me liste en pantalla las tablas de la base de datos a la que accedo, pero no me modifica ni me crea nada...

Te pregunto esto porque ya no sé qué más vueltas darle al formato para intentar que funcione...

¿Crees que habría una opción alternativa que me permita conseguir el resultado que busco de una forma menos complicada? (Digo esto porque quizá haya una vía más sencilla y yo estoy tan empecinado con hacer funcionar esta, que sólo pierdo tiempo y posiblemente acabe perdiendo hasta la oportunidad de usar los datos que recopilé en el Excel...)

Muchas gracias por tu atención y tu rapidez contestando, explorer

explorer · 2011-06-06 08:13 @384

El programa no crea ninguna base de datos.

Lo que hace es leer el fichero de entrada, línea a línea, y cuando encuentra una línea TABLE, manda una orden de borrar la tabla correspondiente dentro de la base de datos. Y las siguientes líneas hace un INSERT por cada una de ellas.

Eso quiere decir...

* que la base de datos debe estar creada antes
* que la base de datos debe tener definidas y creadas, las tablas

Este último paso es también importante: este programa tampoco crea las tablas. Tienes que crearlas antes de arrancar el programa.

En caso de error, debes sacar el valor de $DBI::errstr para saber qué ha pasado. Pon la opción RaiseError => 1 en el new del DBI, para que saque un error en pantalla, parando el programa. Así tendremos más pistas.

Por ejemplo, en las líneas 38 y 50 no se comprueba si ha ocurrido un error.

Alfumao · 2011-06-06 08:35 @399

Hola de nuevo,

Por lo que yo entendía, si tenía una base de datos creada, con una tabla creada, este programa podía coger dicha tabla y reemplazar sus datos con los datos del archivo .tabs (borrando los datos prexistentes, antes de correr el programa).

Ahora viendo lo que me explicas,
¿La tabla que debe estar creada, ha de ser exactamente igual en cuanto a características que la que crea el programa? Es decir, tener el mismo número de filas y columnas con variables de iguales características.

Si esto es así, entonces, ¿hay una forma de pasar un Excel a MySQL vía Perl, que no me obligue a tener que introducir manualmente (o mediante sentencias insert individualizadas para cada línea) todas las variables?

La tabla que he de manejar tiene miles de celdas y sería inviable hacerlo de forma manual en el plazo de tiempo del que dispongo...

explorer · 2011-06-06 09:07 @422

Te basta con que la tabla tenga el mismo número de columnas (campos) que las que vas a insertar. Y deben llamarse igual que la primera fila de datos (la cabecera de campos, la que sigue a TABLE). En cuanto al tipo de dato de cada columna, te vale con que sean strings o varchar, ya que el programa no distingue lo que está leyendo (quiero decir que Perl está enviando strings hacia la base de datos).

En cuanto a si esto se puede automatizar: sí.

Te vale con modificar las líneas 36 a 39 para agregar sentencias SQL del tipo CREATE TABLE en caso de que la tabla no exista, para crearla dentro de MySQL.

Luego, una vez creada la tabla, se supone que todas las líneas de datos serán iguales (en cuanto a número de campos), y el INSERT de la línea 44 hará su trabajo.

Atención al caso de que tengas una línea al que le falte un campo (la celda correspondiente esté vacía). En la línea 49 hay un split(), que está dividiendo la línea por los espacios en blanco que separa a esos campos. Si a la línea que estás leyendo le falta un campo, entonces el INSERT fallará porque le estás pasando menos campos que los que espera la sentencia creada en la línea 44. Si sospechas que puede ocurrir ese caso, tendrás que contemplarlo en la línea 49 (ver si el número de @fields es igual al de @fieldnames sacados en la línea 43). Esto se podría solventar, si el fichero tabs mantiene los campos separados por un tabulador, por ejemplo.

Alfumao · 2011-06-06 09:37 @442

Gracias explorer,

La tabla no tiene celdas en blanco, ya que las que pudieran estar vacías tienen escrito "None", así que eso no sería un problema en principio.

Alfumao · 2011-06-07 05:23 @266

Hola de nuevo explorer,

He hecho la modificación que he hecho en las lineas 36-39 en base a lo que me comentaste.
¿Era esto a lo que te referías?

Syntax: [ Download ] [ Hide ]

Using perl Syntax Highlighting

#!c:/perl/bin/perl.exe
use warnings;
use strict;
 
# Make connection with MySQL database
 
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";
}
 
my $flag = 0;
my $table;
my @tables;
my $sql;
 
while(<>) {
    # skip blank lines
    if(/^\s*$/) {
        next;
 
    # begin new table
    }elsif(/^TABLE\t(\w+)/) {
        $flag = 1;
        $table = $1;
        push(@tables, $table);
# Delete all rows in database table
#        my $droprows = $homologs->prepare ("delete from $table");
#        $droprows->execute();
 if (!$1){ 
 my $dotable = $homologs->prepare("create $table");
    $dotable->execute();
        $flag = 1;
 }
        
    # get fieldnames, prepare SQL statement
    } elsif($flag == 1) {
       $flag = 2;
       my @fieldnames = split;
       my $query = "insert into  $table (" . join(",", @fieldnames) . ") values (" . "?, " x (@fieldnames-1) . "?)";
        $sql = $homologs->prepare($query);
 
    # get row, execute SQL statement
    } elsif($flag == 2) {
        my @fields = split;
        $sql->execute( @fields);
    }
}
 
# Check if tables were updated
 
foreach my $table (@tables) {
        print "\nTable: $table\n\n";
        my $query = "select * from $table";
        my $sql = $homologs->prepare($query);
        $sql->execute();
 
        while (my $row = $sql->fetchrow_arrayref) {
            print join("\t", @$row), "\n";
        }
}
 
# Break connection with MySQL database
 
$homologs->disconnect;
 
exit;

explorer · 2011-06-07 05:30 @271

El proceso CREATE TABLE hay que hacerlo cuando no exista la tabla en la base de datos. No tiene sentido preguntar si $1 tiene un valor verdadero o no.

En la línea 15 se leen las tablas de la base de datos, así que ahí podrías quedarte con sus nombres, y luego, en la línea 36, si el nombre de la tabla que el fichero tabs solicita usar, no existe entre las que has leído (ergo, no existe en la base de datos), entonces es cuando debes realizar el proceso de CREATE TABLE con el nombre de la tabla a crear y los nombres de las columnas (campos) que esa tabla debe tener.

Intenta hacer un programa, aparte, que haga solo eso: crear una tabla con una definición de los campos. Cuando funcione, agrega el código al programa principal.

Alfumao · 2011-06-09 04:11 @216

Una cosilla, explorer,

Verás, es que me estoy haciendo un lío muy gordo. Yo pensaba que el problema era del formato del archivo de entrada (.tabs). Imaginaba que el programa no lo podía leer y por eso no hacía lo que se esperaba de él. La tabla que quiero meter en la base de datos sería siempre con el mismo nombre, pero solo cambiarían el número de filas y quizá el de columnas.

Lo que tu me comentas está enfocado para crear una tabla si no la hay, eso lo entiendo, pero en este caso, que sí la hay (con el mismo nombre) ¿No se supone que el programa original habría de borrar línea a línea, una tabla que ya estuviera presente en la base de datos y reemplazarla, creando una nueva con su mismo nombre pero con las nuevas características derivadas de los datos del archivo ".tabs"?

Se supone que el borrado lo haría así, ¿no?

Syntax: [ Download ] [ Hide ]

Using perl Syntax Highlighting

# Delete all rows in database table
       my $droprows = $homologs->prepare ("delete from $table");
       $droprows->execute();

Y ya después el propio programa va insertando los campos y los valores, de esta forma, ¿no?

Syntax: [ Download ] [ Hide ]

Using perl Syntax Highlighting

  elsif($flag == 1) {
       $flag = 2;
       my @fieldnames = split;
       my $query = "insert into  $table (" . join(",", @fieldnames) . ") values (" . "?, " x (@fieldnames-1) . "?)";
        $sql = $homologs->prepare($query);
 
    # get row, execute SQL statement
    } elsif($flag == 2) {
        my @fields = split;
        $sql->execute( @fields);
    }
}

No sé sí estoy empezando a perder el objetivo original de mi pregunta y al final acabo formando un lío de narices...

En fin, muchísimas gracias por tu atención, explorer.

explorer · 2011-06-09 08:19 @388

En efecto. El programa trabaja bajo la idea de que tienes una base de datos con unas tablas cuyos nombres coinciden con los que aparecen en el fichero .tabs. Y las tablas tienen definidas una serie de campos que son los mismos y con los mismos nombres que los que tiene el fichero .tabs.

Si todo eso existe, el programa limpiará la tabla en cuanto llegue a la primera línea TABLE. Y luego empezará a hacer INSERT con el resto de las líneas.

Alfumao · 2011-06-09 08:29 @395

La cosa es, si a la tabla del fichero tabs, le agrego una línea nueva y luego intento cargarla con el programa en la base de datos, ¿funcionará aunque esa línea no estuviera en la tabla original?
¿podría agregar una columna más al fichero tabs de las que presenta la tabla original (sita en la base de datos) y me seguiría funcionando el procedimiento?
El problema que tengo que resolver es el de obtener un programa que me permita reemplazar la tabla preexistente por sus actualizaciones (ya que la tabla se irá actualizando con el tiempo al írsele agregando nuevas filas o columnas).
Se que soy un negado con la informática, pero la ayuda que obtengo de este foro es la única con la que puedo contar.

Gracias de nuevo por tu atención explorer.

explorer · 2011-06-09 16:59 @749

Alfumao escribió:

La cosa es, si a la tabla del fichero tabs, le agrego una línea nueva y luego intento cargarla con el programa en la base de datos, ¿funcionará aunque esa línea no estuviera en la tabla original?

Sí, si la línea tiene el mismo número de campos y están separados por el mismo tipo de delimitadores (espacios en blanco, tabuladores).

Alfumao escribió:

¿podría agregar una columna más al fichero tabs de las que presenta la tabla original (sita en la base de datos) y me seguiría funcionando el procedimiento?

Sí, si cambias la definición de la tabla en la base de datos, agregando ese nuevo campo.

Alfumao escribió:

El problema que tengo que resolver es el de obtener un programa que me permita reemplazar la tabla preexistente por sus actualizaciones (ya que la tabla se irá actualizando con el tiempo al írsele agregando nuevas filas o columnas).

No hay problemas en agregar filas. Pero sí las columnas, como te acabo de decir.

Alfumao escribió:

Se que soy un negado con la informática, pero la ayuda que obtengo de este foro es la única con la que puedo contar.

¡Ánimo!

Alfumao · 2011-06-11 03:08 @172

Hola de nuevo,

He creado manualmente una tabla pequeña en MySQL para comprobar que puedo hacer funcionar el programa:

Syntax: [ Download ] [ Hide ]

Using text Syntax Highlighting

TABLE   HOMOLOGS

OrgId   Organism

PPL     human

POS     worm

Y he intentado, con el programa, que cargue una versión de la tabla con el mismo nombre pero con una línea más, la siguiente:

Syntax: [ Download ] [ Hide ]

Using text Syntax Highlighting

LBI     mouse

Pero parece que el script se queda atascado en la primera llamada a SQL y se para una vez que me muestra las tablas presentes en la base de datos... Creo que el problema puede estar en el formato tabs, que me está trayendo de cabeza para manipularlo.

¿Sería una buena solución elegir otro formato que se pueda obtener del Excel?

Creo que quizá con un formato más "fácil de entender" es decir, más claro a la hora de leerlo, como csv podrían obtener archivos estandar mas intuitivos de leer (por mí) y modificar más fácil y rápido, en caso de tener que cambiar un valor "a mano"...

¿Qué opinas, explorer?

¿Habría que hacerle muchos cambios al programa que mandé al principio?

Muchas gracias por tus respuestas

explorer · 2011-06-11 07:29 @353

He hecho un ejemplo, para que veas que es tanto o más sencillo que con el formato CVS.

Supongamos este archivo tabs (valores separados por tabuladores):

Syntax: [ Download ] [ Hide ]

Using text Syntax Highlighting

TABLE   ORGANISM

OrgId   Organism

1       human

2       worm

3       mouse

TABLE   GENES

GeneId  Gene    Date

118     aging   1984-07-13

9223    wrinkle 1987-08-15

273     hairy   1990-09-30

TABLE   VARIANTS

VarId   OrgId   GeneId

1       1       118

2       2       118

3       1       9223

4       3       9223

5       3       273

Entonces, con este programa:

Syntax: [ Download ] [ Hide ]

Using perl Syntax Highlighting

#!/usr/bin/perl
#use Modern::Perl;      # somos modernos
#use utf8;              # este programa está escrito en utf8
use autodie;            # es mejor morir que regresar con deshonor (proverbio Klingon)
 
@ARGV == 1 or die "Uso: $0 <fichero tabs>\n";
 
my $fichero = shift;
 
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";
 
        $linea = <$TABS>;               # leemos las cabeceras
         
        $numero_campos = @campos = split " ", $linea;
 
        print "INSERT INTO $table (" , join(',', @campos), ') values (', join(',', ('?') x $numero_campos), ")\n";
        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";
    }
    else {
        die "Error en línea $.\n";      # si no es así, avisamos y terminamos
    }
}
 
close $TABS;

sale

Syntax: [ Download ] [ Hide ]

Using text Syntax Highlighting

DELETE FROM ORGANISM

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

INSERT INTO ORGANISM (OrgId,Organism) values ('1','human')

INSERT INTO ORGANISM (OrgId,Organism) values ('2','worm')

INSERT INTO ORGANISM (OrgId,Organism) values ('3','mouse')

DELETE FROM GENES

INSERT INTO GENES (GeneId,Gene,Date) values (?,?,?)

INSERT INTO GENES (GeneId,Gene,Date) values ('118','aging','1984-07-13')

INSERT INTO GENES (GeneId,Gene,Date) values ('9223','wrinkle','1987-08-15')

INSERT INTO GENES (GeneId,Gene,Date) values ('273','hairy','1990-09-30')

DELETE FROM VARIANTS

INSERT INTO VARIANTS (VarId,OrgId,GeneId) values (?,?,?)

INSERT INTO VARIANTS (VarId,OrgId,GeneId) values ('1','1','118')

INSERT INTO VARIANTS (VarId,OrgId,GeneId) values ('2','2','118')

INSERT INTO VARIANTS (VarId,OrgId,GeneId) values ('3','1','9223')

INSERT INTO VARIANTS (VarId,OrgId,GeneId) values ('4','3','9223')

INSERT INTO VARIANTS (VarId,OrgId,GeneId) values ('5','3','273')

La diferencia con un fichero CVS es que el delimitador, en lugar de ser un tabulador o espacios, es una ','. Solo tendrías que cambiar, en las instrucciones split(), el " ", por un "," y ya tienes un programa que lee ficheros CVS.

El problema está en otro sitio. Puedes usar el truco que te muestro (pintar las instrucciones SQL) para ver qué comandos le estás enviando al MySQL.

Publicidad

Socializa