por Siguiendo con el post anterior de exportar .txt delimitado por caracteres, hoy toca hacer referencia a los archivos .txt de ancho fijo.
Cuando hablamos de ancho fijo queremos decir que cada campo ingresado en nuestro .txt tiene un ancho específico, es decir: la longitud del propio elemento más el carácter específico con el que queramos rellenar hasta llegar al ancho deseado.
Como norma general, cuando hablamos de texto, debemos rellenar espacios en blanco por la derecha y cuando hablamos de números, ceros por la izquierda. Pero en cualquier caso, esto lo encontraréis en las especificaciones que suelen acompañar a los .txt
Rellenar ceros por la izquierda ya lo habíamos visto en otra entrada, y podríamos utilizar esta fórmula perfectamente para realizar nuestra macro (también por la derecha cambiando el orden de la fórmula), os recuerdo que la fórmula era esta:
.Cells(i, 2) = WorksheetFunction.Rept("0", 15 - Len(.Cells(i, 1))) & .Cells(i, 1)
si quisiésemos que fuese por la derecha solo hay que cambiar el orden:
.Cells(i, 2) = .Cells(i, 1) & WorksheetFunction.Rept("0", 15 - Len(.Cells(i, 1)))
Pero para el caso de hoy vamos a utilizar dos funciones específicas para cada caso, para añadir caracteres a la izquierda y a la derecha. Siguiendo con el ejemplo del post anterior, nos piden un archivo txt de ancho fijo con las siguientes especificaciones para cada uno de los campos:
El Campo1 (Marca) debe tener una longitud de 20 caracteres a la derecha, y se rellena con espacios en blanco.
El Campo2 (Carburante) debe tener una longitud de 23 caracteres a la derecha, y se rellena con espacios en blanco.
El Campo3 (Comunidad autónoma de residencia) debe tener una longitud de 28 caracteres a la derecha, y se rellena con espacios en blanco.
El Campo4 (Total) debe tener una longitud de 4 caracteres a la izquierda, y se rellena con ceros.
Pues bien, vemos antes de nada la macro y luego las dos funciones que vamos utilizar:
Sub EXPORTAR_TXT_ANCHOFIJO()
Dim i As Double
'Creamos automáticamente un .txt en blanco que llamamos EJEMPLO
'el archivo se creará en la misma unidad que tenemos el Excel.
Archivo_txt = ThisWorkbook.Path & "\" & "EJEMPLO.txt"
'si queremos cambiar su ubicación basta con poner Archivo_txt = "E:\EJEMPLO.txt"
Open Archivo_txt For Output As #1
With Sheets(1)
fin = Application.CountA(Range("A:A"))
For i = 2 To fin
'Asignamos a cada Campo la función que necesitamos aplicar
Campo1 = C_Der(.Cells(i, 1), 20)
Campo2 = C_Der(.Cells(i, 2), 23)
Campo3 = C_Der(.Cells(i, 3), 28)
Campo4 = C_Izq(.Cells(i, 4), 4)
Print #1, Campo1 & Campo2 & Campo3 & Campo4
Next i
Close
End With
End Sub
Como podéis observar solo tendremos que aplicar a cada campo la función que necesitemos y especificar la longitud de la cadena que le queremos pasar.
Las funciones que vamos a utilizar son dos: Rellenar caracteres por la izquierda:
Function C_Izq(ByVal sCadena As String, ByVal nLargo As Integer, Optional sCaracter As Variant) As String
'Creamos cadena para rellenar por la izquierda con el caracter indicado
Dim sValor As String
If IsMissing(sCaracter) Then sCaracter = "0"
sCadena = Trim(sCadena)
If Len(sCadena) > nLargo Then sCadena = Right(sCadena, nLargo)
sValor = String(nLargo - Len(sCadena), sCaracter) & sCadena
C_Izq = sValor
End Function
Y rellenar caracteres por la derecha:
Function C_Der(ByVal sCadena As String, ByVal nLargo As Integer, Optional sCaracter As Variant) As String
'Creamos cadena para rellenar por la derecha con el caracter indicado
Dim sValor As String
If IsMissing(sCaracter) Then sCaracter = Space(1)
sCadena = Trim(sCadena)
If Len(sCadena) > nLargo Then sCadena = Left(sCadena, nLargo)
sValor = sCadena & String(nLargo - Len(sCadena), sCaracter)
C_Der = sValor
End Function
Una vez que hayamos aplicado la macro, se creará en la misma carpeta o ubicación de nuestro archivo Excel un .txt denominado «EJEMPLO» con la siguiente información:
Efectivamente, ya tenemos nuestros datos correctamente escritos en nuestro archivo de texto 🙂
Y con este post finalizamos cuatro capítulos dedicados a los .txt, espero que os sean de utilidad.
Descarga el archivo de ejemplo pulsando en: EXPORTAR ARCHIVOS TXT DE ANCHO FIJO DESDE EXCEL
Hola, como puedo hacer para que una tabla con el formato de 3 columnas «nombre» «edad=20» y «peso=70», se convierta en base de datos «nombre» «edad=20» y en la siguiente linea «nombre» «peso=70»
Es decir, que repita el nombre x cantidad de veces (una vez por linea) de acuerdo a las columnas que tiene, en este caso 2, pero si tengo mas características o datos en mas columnas, se tiene que reperir el primer nombre las veces que tenga datos en las columnas . Muchas Gracias. Muy buen aporte
Hola Luigi. En estos momentos estoy de vacaciones, a la vuelta te responderé. Sin embargo una solución sencilla sería hacer esa tarea con una tabla dinámica.
Saludos
Hola Luigi,
Efectivamente, como te comentaba la mejor forma de realizar el trabajo que indicas es utilizando una tabla dinámica. Prácticamente realiza todo el trabajo.
Quedando un resultado como este:
Ejemplo
Te dejo el archivo para que lo puedas ver:
Ejemplo repetir nombres
Saludos.
Hola, tengo una duda. Al exportar una columna de datos de tipo fecha-hora-min (dd/mm/aaaa:hh:mm), en la hoja de texto me coloca por defecto el formato de dd/mm/aaaa hh:mm:ss (am/pm) quisiera, solo guardar la configuración tal cual está en mi hoja excel, ¿cómo podría hacer?
Hola Josua:
Para eso, debes darle el formato que necesites en el código, por ejemplo así:
Format(Campo3, «m/d/yyyy h:mm») , sin los segundo y sin am/pm.
Saludos.
Excellent article. Keep writing such kind of information on your
page. Im really impressed by your blog.
Hi there, You’ve done a great job. I’ll certainly digg it and in my opinion suggest to
my friends. I am confident they’ll be benefited from
this web site.
Buenas tardes Segu, Muchas Gracias por compartir esta macro me a sido de mucha utilidad, Es mas estoy acondicionando para mi proyecto pero tengo dos dudas quisiera saber si me puedes asesorar,
1 como hago para leer el encabezado osea la primera fila pero de forma independiente al resto de las columnas, Yo se que se podría leer empezando el For i = 1 desde uno pero el problema es que el encabezado debe tener espacios distintos o condiciones aperte del resto del columnas, De hay en adelante no tendría problema ya todos las columnas deben tener las mismas condiciones.
2. La otra inquietud es como hago para condicionar algunas columnas ejemplo si en la primera columna encuentra un Cero (0) que me lo cambie por (0.000) la condición no seria para una celda si no para toda columna en general.
Quedo atento a sus comentarios muchas Graciasss.
Hola Carlos:, gracias a ti por comentar : )
Sobre la primera pregunta, creo con un if lo puedes solucionar fácilmente, del tipo:
For i = 1 To fin
If i = 1 Then
else
end if
print
Es decir, comienzas la macro en la fila 1 y condicionas el valor de la variable a que cuando sea 1 aplicas X formatos, y si es distinto de 1 el resto.
Sobre la segunda pregunta, debes utilizar otro condicional: If campo1 = 0 Then campo1 = «0.000»
Saludos.
Buenas tardes SEGU;
Agradecido de todo corazón por responder mis Inquietudes, La segunda pregunta ya quedo resuelta muchas Gracias De Nuevo. Pero en la primera pregunta no pude asignarle el formato ya que si le asigno un valor o formato al for iniciando en 1 me va modificar todo la columna, y a lo que quiero llegar es que en la primera FILA ejemplo A1, B1, C1, D1 o RANGO(A1 hasta U1) para poder asignarles sus números de espacios o formato para cada celda ya que son distintos a los asignados de la fila dos (2) en adelante ya que todos lo campos son iguales en cada columna.
En conclusión ya tengo listo el archivo de la fila dos en adelante no se realmente si me toca insertar otro ciclo independiente para poder leer la información de la primera fila y sus celdas esto para poder indicar los espacios puntuales que debe llevar el encabezado del archivo. Te adjunto las imágenes y los archivos TXT de como llevo el proyecto y a que debo llegar, En lo que me puedas ayudar te agradezco en el alma, Feliz Noche.
Enlace Drive https://drive.google.com/drive/folders/1cCE4eFw5zewe_01ul52Kvkhk6od6faYJ?usp=sharing
Hola Carlos:
¿Has probado a introducir el condicional, tal y como te comentaba en la respuesta anterior?, lo tendrías que hacer así:
With Sheets(1)fin = Application.CountA(Range("A:A"))
For i = 1 To fin
If i = 1 Then
Campo1 = C_Izq(.Cells(i, 4), 4) & "hola "
Campo2 = C_Izq(.Cells(i, 4), 4) & "adios "
Campo3 = C_Der(.Cells(i, 1), 20) & ":)"
Campo4 = Now
Else
'Asignamos a cada Campo la función que necesitamos aplicar
Campo1 = C_Der(.Cells(i, 1), 20)
Campo2 = C_Der(.Cells(i, 2), 23)
Campo3 = C_Der(.Cells(i, 3), 28)
Campo4 = C_Izq(.Cells(i, 4), 4)
End If
Print #1, Campo1 & Campo2 & Campo3 & Campo4
Next i
En el if indica en los primeros campos la estructura que quieres para la primera fila, después del else el resto.
Así debería funcionarte.
Saludos.
Buenas noches Segui; Muchas gracias por la respuesta me fue de mucha ayuda, Me puedes por favor ayudar con la siguiente duda tengo una celda donde viene la siguiente información; febrero de 2018 y necesito pasarlo cuando que cuando se convierta quede con el siguiente formato de fecha YYYY-MM osea queda ASi; 2018-02 pero el inconveniente es por que una parte de la información la trae en STRING el mes y el año si viene en numero, Actualmente lo tengo con la siguiente condición, If Campo15 > 1 Then Campo15 = Format(Date, «yyyy-mm») Esta perfecto por que me trae la fecha actual y mes y año pero el problema es que tiene leer es el mes y el año que viene en la celda no me sirviria de esa forma, Ya que en la celda podría venir cualquier mes del año y esto ocurre con el año puede ser cualquier año, La pregunta señor SEGUI como capturo el año y el mes de la celda si viene de la siguiente forma noviembre de 2017, Se podría traer la información y que quede 2017-11, Agradezco su colaboración con esta consulta, Muchas Gracias felices pascuas.
Buenas tardes Segui Espero que este bien, Otra opción que esta pensando referente al comentario anterior es como ya sabemos que el problema fundamental es que la funciones no reconocen la celda como fecha es por el (de) que viene siempre en la mitad de la celda EJEMPLO; febrero de 2017 o también puede ser, diciembre de 2015, enero de 2016 osea el problema es el de, Te pregunto señor SEGUI si hay algún condicionante que puede eliminar esa palabra (de) ya que si quedara ejemplo febrero 2018 o marzo 2015 omitiendo la palabra (de) funcionaria la función para llegar al YYYY-MM (2018-02) con la función CDate probé y me funciono sin el (de) se podría eliminar de la celda para que la función fecha funcione bien, Agradecería su asesoría o guía con esta consulta, Gracias por su atención.
Puedes hacerlo así:
Fecha = Format(Replace(dato, " de ", " "), "yyyy-mm")Donde dato es la fecha en texto que tu tienes, febrero de 2017. Lo que hacemos es reemplazar el de (ojo con los espacios) por un espacio vacío, eliminando finalmente el «de». Luego aplicamos el formato que indicas.
Lo he probado y funciona perfectamente.
Saludos.
Buenas noches Segu, Espero que este bien, Quiero agradecerle por responder mis comentarios fueron de Gran ayuda y pedirle también disculpas por tanta molestia, Por ultimo Quiero preguntarle qui si conoce alguna forma de que no me guarde el archivo en la ubicacion donde tengo la macro, Me gustaría que me genere el cuadro de dialogo de «guardar como» para que el usuario indique el nombre del archivo y la ubicación donde lo quiera guardar, Tengo entendido que se realiza con la función (application.filedialog(msofiledialogsaveas).show) Agradezco su colaboración y asesoría, Muchas Gracias por todo.
Hola Carlos:
En esta pequeña entrada creo que puedes obtener la información que necesitas:
https://excelsignum.com/2013/07/16/macro-para-guardar-como/
Saludos.
Buenas tardes Segu,
Muchas gracias por responder Segu Agradecido de corazón por su amabilidad y disposición, Lo realice con la Función (Application.GetSaveAsFilename)
Buenas tardes Segu,
Me puedes indicar como para que los valores queden con 2 dígitos en los decimales, aunque sea 00. por ejemplo 55.35 si me sale con los dos decimales pero si dejo en el Excel 55.00, no me crea el txt con los dos decimales. Aunque en la hoja si tengo puesto que queden 2, en la macro si es 0, no los pone…
Muchas gracias.
Hola Fred:
Para que lo puedes probar con el ejemplo, si queremos que el campo 4 tenga dos decimales siempre:
Campo4 = Format(.Cells(i, 4), «0.00»)
Modifica la macro y ejecútala, te funcionará perfectamente.
Saludos.
Muchas gracias!, todo correcto.
Hola Segu
donde debe agregarse el campo format ya que he probado diferentes opciones y no me ha funcionado, he modificado la macro de la siguiente manera:
El campo 8/9/18/19/20/21/22/23/24/26/28/29 seria los que llevan formato de numero con dos decimales
Sub EXPORTAR_TXT_ANCHOFIJO()
Dim i As Double
Archivo_txt = ThisWorkbook.Path & «\» & «103402-201901_Dr.devolucion.txt»
Open Archivo_txt For Output As #1
With Sheets(1)
fin = Application.CountA(Range(«A:A»))
For i = 2 To fin
Campo1 = C_Izq(.Cells(i, 1), 11)
Campo2 = C_Izq(.Cells(i, 2), 6)
Campo3 = C_Izq(.Cells(i, 3), 2)
Campo4 = C_Izq(.Cells(i, 4), 6)
Campo5 = C_Izq(.Cells(i, 5), 6)
Campo6 = C_Izq(.Cells(i, 6), 11)
Campo7 = C_Izq(.Cells(i, 7), 3)
Campo8 = C_Izq(.Cells(i, 8), 12)
Campo9 = C_Izq(.Cells(i, 9), 12)
Campo10 = C_Izq(.Cells(i, 10), 4)
Campo11 = C_Izq(.Cells(i, 11), 11)
Campo12 = C_Izq(.Cells(i, 12), 22)
Campo13 = C_Izq(.Cells(i, 13), 20)
Campo14 = C_Izq(.Cells(i, 14), 20)
Campo15 = C_Izq(.Cells(i, 15), 10)
Campo16 = C_Izq(.Cells(i, 16), 10)
Campo17 = C_Izq(.Cells(i, 17), 10)
Campo18 = C_Izq(.Cells(i, 18), 12)
Campo19 = C_Izq(.Cells(i, 19), 12)
Campo20 = C_Izq(.Cells(i, 20), 12)
Campo21 = C_Izq(.Cells(i, 21), 12)
Campo22 = C_Izq(.Cells(i, 22), 12)
Campo23 = C_Izq(.Cells(i, 23), 12)
Campo24 = C_Izq(.Cells(i, 24), 12)
Campo25 = C_Izq(.Cells(i, 25), 8)
Campo26 = C_Izq(.Cells(i, 26), 12)
Campo27 = C_Izq(.Cells(i, 27), 12)
Campo28 = C_Izq(.Cells(i, 28), 12)
Campo29 = C_Izq(.Cells(i, 29), 12)
Campo30 = C_Izq(.Cells(i, 30), 150)
Print #1, Campo1 & «|»; Campo2 & «|»; Campo3 & «|»; Campo4 & «|»; Campo5 & «|»; Campo6 & «|»; Campo7 & «|»; Campo8 & «|»; Campo9 & «|»; Campo10 & «|»; Campo11 & «|»; Campo12 & «|»; Campo13 & «|»; Campo14 & «|»; Campo15 & «|»; Campo16 & «|»; Campo17 & «|»; Campo18 & «|»; Campo19 & «|»; Campo20 & «|»; Campo21 & «|»; Campo22 & «|»; Campo23 & «|»; Campo24 & «|»; Campo25 & «|»; Campo26 & «|»; Campo27 & «|»; Campo28 & «|»; Campo29 & «|»; Campo30
Next i
Close
End With
End Sub
Saludos
Hola:
Sería así, por ejemplo para el campo 8
Campo8 = C_Izq(Format(.Cells(i, 8), «0.00»), 12)
Saludos.
Ahora entendi!! Muchas Gracias por la ayuda!!!
Ahora sale perfecto!
Te estoy muy agradecida por compartir el código. Funciono de maravillas para solucionar el problema que tenia con la exportación.
Muchas gracias Nilda!.
Me alegro que te haya resultado útil.
Saludos.
Hola
tengo el siguiente codigo
Option Explicit
Sub CreaTXT()
Dim NombreArchivo, RutaArchivo As String ‘defino variables
Dim obj As FileSystemObject
Dim tx As Scripting.TextStream ‘llama a la libreria previamente activada
Dim Ht As Worksheet
Dim i, j, nFilas, nColumnas As Integer
NombreArchivo = «seba»
RutaArchivo = ActiveWorkbook.Path & «\» & NombreArchivo & «.txt»
Set obj = New FileSystemObject
Set tx = obj.CreateTextFile(RutaArchivo)
Set Ht = Worksheets(«Hoja2»)
nFilas = Ht.Range(«A2», Ht.Range(«A2»).End(xlDown)).Cells.Count
nColumnas = Ht.Range(«A1», Ht.Range(«A1″).End(xlToRight)).Cells.Count
For i = 1 To nFilas
For j = 1 To nColumnas
tx.Write Ht.Cells(i + 1, j)
tx.Write » »
Next j
tx.WriteLine
Next i
MsgBox («Tabla Exportada»)
End Sub
y necesito exportar estos datos
RESULTADOS
Total en dolares de las fortunas informadas. USS 651,20
Hombres Millonarios 18
Mujeres millonarias 2
Extranjeros millonarios 10
Chilenos millonarios 10
Mujeres extranjeras millonarias 0
Mujeres chilenas millonarias 2
Hombres extranjeros millonarios 10
Hombres chilenos millonarios 8
Dolares de las fortunas chilenas USS 38,70
Millonarios procesados. 20
pero separados por largo fijo, aqui los separe con espacio, sabes como podria arreglarlo ???
Saludos
eres un genio.!!!! has salvado mi vida.!!! eres lo maximo
Hola Jelizmar. Me alegro que haya ayudado. Saludos.
hola, yo necesitaria exportar archivo excel a txt
dni cuenta saldo
12345678 123456789012345 1234567
para dni serian 10 numero, cuenta con 15 numero y el importe con 12 numeros a todos completando con cero y sin espacio
asi deberia quedar asi
001234567812345679012345000001234567
Hola Gustavo:
Solo tienes que aplicar la función izquierdo a cada variable e indicar el numero de ceros que quieres:
Sub EXPORTAR_TXT_ANCHOFIJO()Dim i As Double
'Creamos automáticamente un .txt en blanco que llamamos EJEMPLO
'el archivo se creará en la misma unidad que tenemos el Excel.
Archivo_txt = ThisWorkbook.Path & "\" & "EJEMPLO.txt"
'si queremos cambiar su ubicación basta con poner Archivo_txt = "E:\EJEMPLO.txt"
Open Archivo_txt For Output As #1
With Sheets(1)
fin = Application.CountA(Range("A:A"))
For i = 2 To fin
'Asignamos a cada Campo la función que necesitamos aplicar
Campo1 = C_Izq(.Cells(i, 1), 10)
Campo2 = C_Izq(.Cells(i, 2), 15)
Campo3 = C_Izq(.Cells(i, 3), 12)
Print #1, Campo1 & Campo2 & Campo3
Next i
Close
End With
End Sub
hola Segu, donde pongo los cero o la cantidad de ceros
Hola Gustavo:
Los ceros se indican en la siguiente expresión:
Campo1 = C_Izq(.Cells(i, 1), 10)
Aquí estás indicando que quieres poner 10 ceros por la izquierda, de modo que si la expresión tiene 8 caracteres, los completará con 2 ceros.
Saludos.
Buenos días Segu, Necesitaría poder exportar en TXT como te comento Gustavo
Pero que cada vez que termina un campo lo separe por un «;» punto y coma.
Probé cambiando en: If IsMissing(sCaracter) Then sCaracter = «,»
y queda de esta manera:
;;;;Franco;;;;;Cualquiera;;;;;;;;1542
necesito que quede
Franco;cualquiera;1542
Muchas gracias
Hola Franco,
Así sin tener en cuenta las funciones de añadir espacios, ceros:
Campo1 = C_Der(.Cells(i, 1) & «;», 20)
Campo2 = C_Der(.Cells(i, 2) & «;», 23)
Campo3 = C_Der(.Cells(i, 3) & «;», 28)
Campo4 = C_Izq(.Cells(i, 4) & «;», 4)
Resultado:
ADRIA; Diesel; Galicia; 001;
ALFAROMEO; Diesel; Andalucía; 047;
ALFAROMEO; Diesel; Aragón; 041;
Así teniendolas en cuenta.
Print #1, Campo1 & «;» & Campo2 & «;» & Campo3 & «;» & Campo4 & «;»
Resultado:
ADRIA ;Diesel ;Galicia ;0001;
ALFAROMEO ;Diesel ;Andalucía ;0047;
ALFAROMEO ;Diesel ;Aragón ;0041;
Hola, necesito desarrollar un excel de 4 solapas para luego convertir en txt que se importa en mi sitema de gestion contable (bejerman), tengo los parametros del sistema, pero no me doy mucho con las macros, te podria enviar las descripciones que tengo?
Hola Ricardo: Necesitaría un ejemplo sencillo de lo que tienes (un archivo con 4 hojas) y como quieres que quede el resultado, el TXT resultante. Y por supuesto las especificaciones.
Saludos. Correo: excelsignum@yahoo.es
Hola Segu! Ya te lo he enviado! Mil gracias!
Hola Segu, pudieron ver lo que les mande?
Hola Ricardo. Gracias por compartir tus conocimientos, han sido de mucha ayuda!
Tambien quiero aprovechar de preguntarte por una duda: tengo un excel con dos hojas ( «Hoja1» y «Hoja2») y estoy tratando de exportar a .txt sólo lo datos de la Hoja2. cómo harias ese pequeño cambio?
Hola Pedro:
Me llamo Segu.
En cuanto a los cambios de la macro, únicamente debes cambiar donde indica hoja 1: With Sheets(1)
Por: With Sheets(2) o por el nombre de tu segunda hoja With Sheets(«Hoja2»)
Y te funcionará.
Saludos.
Como se le haria para que en vez de que me lo lea por columna sea por filas, y solo las que yo le especique ya que no estan pegadas, tambien como limitar cada campo por ciertos espacios, saludos y gracias de antemano por brindar este tipo de ayuda.
Hola benromusic :
El código no recorre columnas, recorre celdas en cada fila. Tendría que ver un ejemplo de lo que necesitas para poder valorar qué solución aportar.
Saludos.
Buenos Dias, consulta como podria hacer, para mediante excel, para que el contenido de una celda se busque una carpeta determinada y abra un archivo .txt con el nombre del contenido de la celda de busqueda
saludos
Hola Diego:
Puedes utilizar el buscador de esta web y poner la palabra «listar», te aparecerán varios post con de se lista el contenido de un carpeta. Tendrás que modificar el post para que cuando liste el archivo con el nombre que has indicado lo abra,
Saludos.
como se podria eliminar la ultima fila en blanco al exportar el txt
El propio comando Print#1 incluye al final de la copia del archivo una nueva línea. Desconozco que se pueda realizar. Otra cosa sería utilizar un codigo a posteriori que elimine filas en campo en una archivo txt