domingo, agosto 19, 2007

Exportar datos de Excel a archivo texto.

Una tarea corriente es importar archivos de texto a Excel, para lo cual existen varias posibilidades.
Otra tarea similar es generar un archivo texto para exportar datos de una hoja Excel a una aplicación externa. Algunas de estas aplicaciones aceptan archivos texto con formato .csv (comma separated values); otras aplicaciones exigen archivos texto delimitado con tabulaciones.
En ambos casos usamos el menú Guardar como y elegimos alguna de las opciones de texto




Si elegimos la opción .csv Excel genera un archivo de texto cuyos campos están delimitados por comas. Supongamos esta hoja Excel



Si la exportamos usando la opción .csv obtenemos este resultado



Como pueden ver, un archivo texto cuyos campos están separados por ";".

En los casos de exportar a una aplicación que no acepta este formato, usamos la opción texto delimitado con tabulaciones. El resultado es



El problema surge cuando la aplicación a la cual queremos exportar los datos exige que los campos tengan una longitud determinada. En esta nota mostraremos como crear un archivo texto con campos de longitud determinada por el usuario.

A la hoja Excel que contiene los datos a exportar la llamaremos "BD" (base de datos). Agregamos una fila por encima de la fila 1, de manera que los encabezamientos queden en la fila 2. Ahora ponemos en las celdas correspondientes de la fila 1 el "ancho" deseado para cada campo



El próximo paso consiste en agregar una hoja, que llamaremos "ascii". En celda A1 de esta hoja ponemos esta fórmula

=BD!A3&REPETIR(" ";BD!A$1-LARGO(BD!A3))

Esta fórmula crea un valor compuesto por el contenido de la celda A3 (siendo 3 la primer fila de la hoja BD con datos a exportar) y una cantidad de espacios en blanco hasta completar el largo deseado para el campo. En nuestro caso "Jorge" tiene un largo de cinco, pero el campo "Nombre" tiene que tener 15. Los 10 espacios necesarios son calculados por la expresión BD!A$1-LARGO(BD!A3)
Copiamos esta fórmula a lo largo del rango necesario



Ahora tenemos que guardar esta hoja como archivo texto delimitado con tabulaciones, tal como mostramos más arriba. Es recomendable crear una copia de la hoja usando la opción Mover o copiar (clic con el botón derecho en la pestaña de la hoja) a un libro nuevo y asegurándonos de haber señalado la opción "crear una copia"



Al guardar este nuevo libro, que contiene sólo la hoja con los datos, recibimos este mensaje



Apretamos "Si" y guardamos el libro. El resultado es



Como ven, el ancho de los campos está de acuerdo a nuestras definiciones.

Un problema potencial se presenta cuando el largo del valor de la celda en BD es mayor que el ancho requerido para el campo.
Por ejemplo, si el ancho para el campo "Ciudad" es sólo de 15 caracteres, cuando ponemos Santiago de Chile (17 caracteres) en la hoja BD, obtensmo un resultado de error en la hoja "ascii"



Esto se debe a que la función REPETIR no puede aceptar un número negativo como segundo argumento.
En esto casos tenemos dos opciones:
1 – cambiar el parámetro del ancho del campo (lel valor en la celda C1 en nuestro ejemplo);
2 – cambiar la fórmula para que "recorte" el texto al ancho requerido, desechando los caracteres supernumerarios. Para eso usamos esta fórmula

=SI(LARGO(BD!C5)>BD!C$1;IZQUIERDA(BD!C5;BD!C$1);BD!C5&REPETIR(" ";BD!C$1-LARGO(BD!C5)))

que nos dará como resultado



es decir, recortando Santiago de Chile (17 caracteres) a Santiago de Chi (15 caracteres).

El archivo con el ejemplo esta a vuestra disposición excel asciiaquí.


Technorati Tags:

23 comentarios:

  1. Felicitaciones por sus tips. Mi pregunta es ¿Habrá alguna macro que me permita abrir un documento en word (p.ej:Un contrato).? Una vez abierto copiar en él datos importados de una hoja de excel (tales como nombre, valor contrato, etc). Es decir abrir un modelo de contrato de arrendamiento y modificar luego desde excel ciertos datos. Saludos y gracias

    ResponderBorrar
  2. Muchisimas gracias. Ya veo que con excel (y tu ayuda) muchas cosas son posibles. A ver si algun dia diseñas una formula que nos limpie la casa o nos haga la comida... seria genial, jajaja

    Gracias de nuevo, era justo lo que estaba buscando. Saludos

    ResponderBorrar
  3. Acabo de caer en una cosa... cuando los campos no son texto si no que son numeros, ¿se podria hacer lo mismo cambiando el formato de la celda? Por ejemplo a las columnas que son numericas ponerle el formato 000000000000000 (15 ceros) para que complete con ceros.
    Y otra cosa mas, puede que algunos de esos campos halla que rellenar con ceros a la derecha o a la izquierda. En ese caso ¿como se podria hacer?
    Gracias por tu tiempo

    ResponderBorrar
  4. Me surge otro problemilla al guardar en formato texto. Si el campo debe tener 15 posiciones cuando se guarda aparece siempre una mas. No se si es por la forma de guardarlo o por mi version de excel, pero el segundo campo de tu ejemplo que deberia empezar en la posicion 16 lo hace en la 17, y asi con todos y acumulando el "exceso" por lo que el ultimo campo esta fuera de lugar completamente.
    Espero no impotunarte mucho y gracias por el tiempo dedicado a este tema.

    ResponderBorrar
  5. Lo que tienes que hacer es usar la función TEXTO para asegurarte que aparezcan los ceros al principio del valor. Por ejemplo, si en la celda C3 de la hoja BD tiene el valor 15, pero quieres que pase como 000000000000015 (quince caracteres), tendías que modificar la fórmula del ejemplo a:
    =SI(LARGO(BD!C3)>BD!C$1;TEXTO(IZQUIERDA(BD!C3;BD!C$1);"000000000000000");TEXTO(BD!C3&REPETIR(" ";BD!C$1-LARGO(BD!C3));"000000000000000"))
    EL segundo argumento de la función TEXTO determina el formato del valor.

    ResponderBorrar
  6. No había visto tu último comentario. Tienes razón. Para solucionarlo puedes hacer una de dos:
    1 - en el encabezamiento de la hoja BD, reduces en 1 el ancho del campo (en nuestro ejemplo, en la celda A1 de BD en lugar de 15 ponemos 14)
    2 - modificamos la fórmula restando 1 en los lugares indicados:
    =SI(LARGO(BD!A3)>BD!A$1-1;IZQUIERDA(BD!A3;BD!A$1);BD!A3&REPETIR(" ";BD!A$1-LARGO(BD!A3)-1))

    ResponderBorrar
  7. Primero dejame felicitarte por la excelentisima pagina....creo que ayudar gratuitamente (solo en el sentido economico) por estos dias es todo un "miracle"...realmente son muy buenos tus post y he aprendido muchisimas cosas para mi trabajo.
    Particularmente en este momento tengo la siguiente consulta:
    Tengo una planilla en la cual voy registrando datos en columnas y filas (una base de datos), pero necesito automatizar algunos campos. Necesito que cuando ingrese por ejemplo un valor a una celda de una fila, en otra columna de la misma fila, me quede registrado el dia en que se ingreso ese dato. Actualmente debo hacer esto manualmente y pierdo tiempo innecesariamente creo. Lamentablemente para mi, las formulas hoY() y ahora(), actualizan su valor...yo deseo que queden fijas.
    Sin mas que agregar me despido atte.
    Javier
    Santiago de Chile.

    ResponderBorrar
  8. tengo una base de datos con datos en la columna A, tales como nombre, direccion, actividad, etc, unos tienen 4 campos y otros alguno mas, para poder ordenar esta base de datos quiero utilizar la función trasponer,para situar los datos de cada registro en una fila, y así poder ordenarlos, me pregunto si puedo utilizar alguna macro que me ayude a realizar este proceso de trasposición.
    Muchas gracias Te felicito por el blog, me parece muy interesante.
    lsanzd@wanadoo.es

    ResponderBorrar
  9. Hola Javier
    la forma de hacerlo es usando un tipo especial de macros que se llaman eventos.
    No es posible dar una explicación en el marco de un comentario. Posiblemente publique una nota sobre el tema, ya que recibo muchas consultas relacionadas con eventos.

    ResponderBorrar
  10. Hola lsanzd@wanadoo.es

    debo confesar que no entiendo tu consulta. Por lo general las bases de datos tienen campos (columnas) y registros (filas). Tu mencionas una base de datos en la columna A, por lo que entiendo que los campos están en las filas?
    De todas maneras, puedes usar la función TRANSPONER en Vba de la siguiente manera Worksheetfunction.Transpose.
    Tal vez sea suficiente con usar Pegado Especial--Transponer.

    Gracias por las felicitaciones

    ResponderBorrar
  11. Hola tengo un caso necesito exportar un archivo de excel a csv pero tengo una columna de codigo postal que algunos valores empiezan con cero a la izquierda, al exportar a csv me quita el formato y me elimina el cero hay alguna forma de lograr esto?
    Gracias

    ResponderBorrar
  12. Hola
    Excel exporta los datos con el cero incluido. Esto lo puedes comprobar abriendo el archivo en un editor textual (como Notepad). El problema surge cuando lo vuelves a abrir en Excel. A veces Excel trata de ahorrarnos trabajo y termina complicándonos la vida.
    Para abrir el archivo en Excel usa el menú Datos-Obterner datos externos-Importar datos. De esta manera Excel abre el asistente y te permite definir el campo a importar como texto.
    Fíjate en esta nota.

    ResponderBorrar
  13. Muchas gracias por la ayuda, ya que logre generar estos campos con largos definidos para poder convertirlos a txt, pero tengo el problema que cuando genero el txt, los campos que son textos deben quedar entre comillas ' '. Como puedo lograr esto, muchas gracias por la ayuda,
    Cristian
    Valparaíso, Chile
    marquezpino@vtr.net

    ResponderBorrar
  14. Hola Cristian

    depende de cómo aparecen los datos en el archivo de origen. Una solución es, obviamente, agregar las comillas en las celdas de origen.
    Si necesitas que las comillas de cierre queden al final del campo, es decir, después de los espacios en blanco, tendrías que usar la función CONCATENAR y descontar dos espacios de la cuenta de espacios en blanco a agregar.

    ResponderBorrar
  15. Hola, quisiera saber como realizar una exportacion de dos campos en un libro excel (nombre y email) y como resultado obtener un formato como este (Juan,juan@juan.com)??? Muchas Gracias por anticipado
    Gracias, Daniel

    ResponderBorrar
  16. Daniel, tienes que concatenar los campos. Esto se puede hacer en una columna auxiliar de la hoja usando la función CONCATENAR o el operador &, o creando la concatenación en el código de la macro (con el operador &)

    ResponderBorrar
  17. Hola Jorge tengo una inquietud y es la siguiente quiero tener una lista de usuarios y al dar clic en un usuario se abra como otra hoja de calculo donde este todo el historial de ese usuario. como se aria eso.
    att: carlos

    ResponderBorrar
  18. Carlos,
    tendrías que programar un evento. E el blog hay una serie de notas sobre el tema.

    ResponderBorrar
  19. Hola, gracias por los tips.... pero te comento:
    estoy haciendo exactamente el ejemplo con la hoja llamada BD y la hoja ASCII, pero al insertar la funcion que recomiendas marca error.

    =BD!A3&REPETIR(" ";BD!A$1-LARGO(BD!A3))

    El error me lo marca segun excel 2007 en las " " , como que esperando el texto y numero de veces que se repite.

    Espero me puedas ayudar, lo que requiero es que de una base de datos (excel) pueda crear una hoja con datos limitados, para despues crear el archivo txt, como tu lo muestras en el ejemplo.

    Saludos

    ResponderBorrar
  20. Raul,

    tendría que ver tu archivo (fijate en el enlace Ayuda).

    ResponderBorrar
  21. Muy bueno y muy útil!

    ResponderBorrar
  22. Hola Buenas Tardes! La verdad que muy útil, pero me surgió un problema al convertir el archivo en excell con las longitudes determinadas, como dijeron en unas de las consultas me agrega un espacio mas en la longitud, modifique la fórmula restando y también probé modificando el ancho de la longitud fijada el fila 1 de la hoja BD, pero no se me soluciona el inconveniente. Como puedo hacer?

    ResponderBorrar
  23. Gabriela, tendría que ver el archivo que quieres exportar. Para mandarlo fijate en el enlace Ayuda (en la parte superior del blog).

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.