Exportar datos de Excel a archivo texto.

domingo, agosto 19, 2007

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 comments:

Augusto Jiménez Márquez 20 agosto, 2007 17:57  

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

hechicero,  20 agosto, 2007 22:30  

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

hechicero,  21 agosto, 2007 00:25  

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

hechicero,  21 agosto, 2007 01:50  

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.

Jorge L. Dunkelman 21 agosto, 2007 21:50  

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.

Jorge L. Dunkelman 21 agosto, 2007 22:07  

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))

Anónimo,  02 septiembre, 2007 07:16  

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.

Anónimo,  02 septiembre, 2007 12:21  

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

Jorge L. Dunkelman 02 septiembre, 2007 18:00  

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.

Jorge L. Dunkelman 02 septiembre, 2007 18:08  

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

Anónimo,  06 noviembre, 2007 03:33  

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

Jorge L. Dunkelman 06 noviembre, 2007 17:23  

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.

Anónimo,  15 abril, 2008 06:35  

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

Jorge L. Dunkelman 15 abril, 2008 17:48  

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.

Anónimo,  22 septiembre, 2009 11:35  

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

Jorge L. Dunkelman 25 septiembre, 2009 10:47  

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 &)

carlos 01 julio, 2010 08:36  

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

Jorge L. Dunkelman 01 julio, 2010 09:00  

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

Raul 30 enero, 2011 22:12  

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

Jorge L. Dunkelman 31 enero, 2011 07:08  

Raul,

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

Anónimo,  11 julio, 2012 19:46  

Muy bueno y muy útil!

Gabriela,  14 mayo, 2015 20:41  

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?

Jorge Dunkelman 15 mayo, 2015 17:08  

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

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP