viernes, 25 de junio de 2010

Tratamiento ficheros de texto con Excel: TextToColumns.

Un usuario habitual del blog preguntaba alguna forma de tratar información importada de un fichero tipo texto (extensión *.txt), la cual debía convertir de manera muy mecanica varias veces todos los meses, perdiendo bastante tiempo en la rutina. Y me pedía alguna manera de automatizar todo el proceso. Tiempo atrás subí un post con un ejemplo aplicado del uso de la herramienta Texto en columnas; y esa será en parte la tarea a explicar.
Si bien, ya que el preceso es reiterativo, generaremos algo de código VBA que nos facilite en el futuro su aplicación.


Supondremos para nuestra comodidad que disponemos en nuestra hoja de cálculo de la información del Fichero de texto; muy importante que cada una de las filas del fichero guarden la misma estructura respecto a las columnas, ya que nuestro trabajo consistirá en aplicar la herramienta de Texto en columnas con la opción de Ancho fijo para definir las columnas necesarias y sus formatos individualizados


Sabiendo cuáles deben ser las columnas resultantes, cuáles serán sus formatos y cuáles no se importaran, el código de nuestra macro a generar será:

'Ejecutamos la herramienta Texto en columnas y generamos el código VBA
Sub Trata_texto()
Sheets("Hoja1").Range("A1:A6").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(1, 4), Array(11, 1), Array(18, 1), _
Array(25, 1), Array(32, 9), Array(36, 2), Array(50, 2), Array(75, 1)),_
DecimalSeparator:=".", ThousandsSeparator:=",", TrailingMinusNumbers:=True
'Array(x,1) formato General
'Array(x,2) formato Texto
'Array(x,3) formato fecha MDA
'Array(x,4) formato fecha DMA
'Array(x,5) formato fecha AMD
'Array(x,6) formato fecha MAD
'Array(x,7) formato fecha DAM
'Array(x,8) formato fecha ADM
'Array(x,9) No importa a Excel -omite- la columna definida
'Valores predeterminados para reconocer valores numéricos:
' DecimalSeparator:="."
'entiende que el separador de los decimales es el .
' ThousandsSeparator:=","
'entiende que el separador de los miles es la ,

End Sub



haz click en la imagen



Vemos como opera la instrucción Array dentro del parámetro FieldInfo para el método TextToColumns:

Rango de celdas.TextToColumns(Destination, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers)

El código a continuación del parámetro FieldInfo:= se define como una matriz que contiene información de análisis para las columnas individuales de datos, interpretándose en función del valor de DataType (en nuestro ejemplo DataType:=xlFixedWidth).
Como los datos de origen tienen columnas definidas por nosotros como ancho fijo - FixedWidth-, el primer elemento de cada matriz - Array (x,y) - de dos elementos especificará la posición del primer carácter de la columna (como un entero; un carácter 0 (cero) es el primer carácter) y un segundo elemento de la matriz de dos elementos especifica la opción de distribución de la columna como un número de 1 a 9, de la manera indicada anteriormente, esto es:
  1. Array(x,1) formato General

  2. Array(x,2) formato Texto

  3. Array(x,3) formato fecha MDA

  4. Array(x,4) formato fecha DMA

  5. Array(x,5) formato fecha AMD

  6. Array(x,6) formato fecha MAD

  7. Array(x,7) formato fecha DAM

  8. Array(x,8) formato fecha ADM

  9. Array(x,9) No importa a Excel -omite- la columna definida


Todo este código se ha generado empleando el Asistente para macros.
Con ello hemos conseguido obtener una macro que otorgue, cada vez que lo requiramos, el mismo tratamiento (convertir un texto en columnas) a nuestros ficheros de texto; dando a cada columna resultante el formato correcto y convirtiendo todos los valores numéricos.

12 comentarios:

  1. Estimado tengo un archivo txt, lo envio a excel hago el texto en columnas, pero cuando lo guardo como txt, me deja desordenado todos los datos y no los puedo ingresa a la base de datos con ese formato gracias.

    ResponderEliminar
  2. Hola Kayo..
    ¿por que si lo tratas con la herramienta Texto en columnas' de Excel, luego lo vuelves a guardar como texto?...
    La idea de esta herramienta es poder trabajar en Excel, o algún Sw tabulado como Access, con lo que antes era un fichero .txt.
    Prueba a guardarlo como .xlsx o como .csv o copiarlo y pegar el resultado directamente desde Excel a tu base de datos.
    Slds

    ResponderEliminar
  3. Yo lo estoy probando con un csv separado por ",", pero las fechas, a pesar de indicar el formato DMA con Array(x,4), me las rellena mal. Un ejemplo, tengo "01-09-2011 08:54:51" y al pasarle la macro, me deja en la columna "09/01/2011 8:54:51".
    Si hago la operación manualmente esto no me pasa, ¿qué puedo hacer?
    Muchas gracias por anticipado.

    ResponderEliminar
  4. Hola,
    bueno, necesitaría saber si la fecha correcta es uno de septiembre o nueve de enero.
    Pero la idea es que la macro lo convierte de MDA al especificado, en este caso Array(x,4) o DMA.. por lo que entiendo la macro lo ha convertido correctamente...
    ¿Quizá te esté entendiendo mal???

    ResponderEliminar
  5. Hola otra vez, perdona, yo no me he explicado bien, la fecha correcta es 1 de Sep y si hago la operación Texto en Columnas manaulamente, me mantiene la fecha, pero al hacerlo mediante una macro, me cambia el formato dejando 09/01. Uso Array(x,4), pero el resultado es el mismo. De hecho, ponga el formato que ponga en la segunda posición del Array, me devuelve la misma fecha.
    Gracias otra vez.

    ResponderEliminar
  6. No sabría darte una explicación sin ver el fichero con el que trabajas, ya que he replicado tu planteameniento y a mí si me lo convierte correctamente...
    Revisa el proceso manual, y asegúrate que seleccionas la columna de Fecha, en el asistente, y con esta seleccionada le cambias al formato elegido.
    Si sigue fallando, envíame el ejemplo a
    excelforo@gmail.com

    Slds

    ResponderEliminar
  7. Manualmente, dando General como tipo de datos para todas las columnas, funciona. Si le doy el tipo DMA a la columna en particular, funciona. Funciona lo haga como lo haga. Pero en la macro me falla lo haga como lo haga. No hace caso al tipo de dato que le pongo.
    Me he pegado con el problema, he buscado, preguntado... creo que voy a desistir y generarlo manualmente cada vez.
    Gracias igualmente por las respuestas.

    ResponderEliminar
  8. Hola quiero saber si hay alguna formula que combierta los números en letras Ejemp.tengo un cuadro de notas y necesito que al colocar las Notas en una columna en número estas me las coloque en otro columna en letras. Ejemp.
    Notas en letra
    15 Quince
    10 Diez
    12 Doce
    05 Cinco
    09 Nueve

    ResponderEliminar
  9. Hola Carlos,
    realmente no existe ninguna función estándar de Excel que haga lo que quieres, se requiere, por tanto, el desarrollo de una función personalizada (UDF)...
    LLevo tiempo pensando en subir un post explicando este desarrollo, así que aprovechando, en próximos días lo subiré.
    Slds

    ResponderEliminar
  10. gracias por responder, esperare el post.

    ResponderEliminar
  11. hola puedo enviarte un fichero que debo arreglar en excel? la verdad es bastante extenso

    ResponderEliminar
  12. Hola Yoyinni,
    si claro, le echaré un vistazo.
    Envíalo a
    excelforo@gmail.com

    Slds

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.