Unir varios ficheros texto en excel y agregarle una columna
Tengo la siguiente macro que me une varios ficheros text en un fichero excel, necesito que las columnas sean texto para que me mantenga los ceros a la izquierda y además necesito agregarle una columna que sea los últimos 10 dígitos del nombre del fichero que se importa. ¿Alguna ayuda?
Saludos... Aleida
Sub JoinList()
Dim Hoja As Object
Application.ScreenUpdating = False
'Definir la variable como tipo Variante
Dim X As Variant
'Abrir cuadro de dialogo
X = Application.GetOpenFilename _
("Text Files (*.txt*), *.txt*", 2, "Abrir archivos", , True)
'Validar si se seleccionaron archivos
If IsArray(X) Then ' Si se seleccionan
'Crea Libro nuevo
Workbooks.Add
'Captura nombre de archivo destino donde se grabaran los archivos seleccionados
A = ActiveWorkbook.Name
'*/********************
For y = LBound(X) To UBound(X)
Application.StatusBar = "Importando Archivos: " & X(y)
Workbooks.Open X(y)
b = ActiveWorkbook.Name
For Each Hoja In ActiveWorkbook.Sheets
Hoja.Copy after:=Workbooks(A).Sheets(Workbooks(A).Sheets.Count)
Next
Workbooks(b).Close False
Next
Application.StatusBar = "Listo"
Call Unir_Hojas
End If
Application.ScreenUpdating = False
End Sub
Sub Unir_Hojas()
Dim Sig As Byte, Eliminar As Boolean
For Sig = 2 To Worksheets.Count
Worksheets(Sig).UsedRange.Copy _
Worksheets(1).Range("a1000000").End(xlUp).Offset(1)
Next
Application.DisplayAlerts = False
For Sig = 2 To Worksheets.Count
Worksheets(2).Delete
Next
Application.DisplayAlerts = True
End Sub
1 Respuesta
No tengo mucho conocimiento ni de visual basic ni de excel aunque a veces me meto en este tipo de cosas. Pero tal como planteas la cuestión se me ocurre que tal vez se pueda hacer buena parte del trabajo desde MSDOS con programación batch. Por ejemplo se pueden unir archivos de texto mediante el comando COPY con la sintaxis
COPY ARCH1.TXT + ARCH2.TXT + ARCH3.TXT GLOBAL.TXT
La columna que pretendes añadir da la impresión de que quiere asociar cada registro con el archivo del que proviene. Para esto se podría usar un bat que generase un CSV para cada uno de los archivos copiando el contenido de cada línea y añadiéndole un ";" y luego los últimos 10 caracteres del nombre del archivo. A esos CSVs les aplicaríamos luego el COPY + con lo que crearíamos un GLOBAL. CSV (o el nombre que prefieras). Ese CSV ya se podría abrir directamente con Excel.
¿Qué te parece la propuesta?
Expresado así suena bien... En excel necesito que los campos/columnas sean del tipo text porque tengo ceros a la izquierda que se deben mantener el campo como "09876".
La columna que se agrega es para asociarla con una parte del nombre del archivo que proviene.
He hecho la prueba de cambiar a CSV un TXT con números con ceros delante y al abrirlo con Excel le quita los ceros. Para evitarlo he comprobado que se puede insertar delante un carácter Alt + 255.
Prueba este BAT:
@echo off del /q global.csv>nul 2>&1 :Bucle Echo %1 %~n1 set nom10=%~n1 set nom10=%nom10:~-10% for /f "tokens=* delims=" %%a in (%1) do echo %%a;%nom10%>>global.csv shift if "%1"=="" goto:eof goto :bucle
Una vez creado supongamos que le llamas ALEIDA.BAT y que está en la misma carpeta que los ficheros a tratar que se llaman (es un ejemplo) ALEIDADAT1.TXT, ALEIDADAT2.TXT, ALEIDADAT3.TXT y ALEIDADAT4.TXT.
Esta "invocación" al BAT debería dejar en GLOBAL. CSV todos los datos junto con los nombres de los archivos separados por ";" con lo que al abrirlo con Excel debería tener el aspecto que estás buscando:
ALEIDA ALEIDADAT1.txt aleidadat2.txt aleidadat3.txt aleidadat4.TXT
Como digo al principio para evitar que Excel suprima los ceros a la izquierda es fundamental que antes del %%a del comando ECHO figure un carácter Alt + 255. Si con el copiar y pegar se perdiera tendrías que reponerlo a mano.
Esta versión funciona arrastrando sobre el bat la lista de archivos que se quieren tratar:
@echo off
del /q "d:\carpeta prueba\global.csv">nul 2>&1
:Bucle
Echo Tratando archivo %1
set nom10=%~n1
set nom10=%nom10:~-10%
for /f "tokens=* delims=" %%a in ('type %1') do echo %%a;%nom10%>>"d:\carpeta prueba\global.csv"
shift
if "%~1"=="" goto:eof
goto :bucleTendrás que decidir la ubicación del GLOBAL.CSV
Para que la ejecución del BAT vía arrastre funcione hay que tomar ciertas precauciones (como incluir el PATH del archivo GLOBAL. CSV, el TYPE %1 y el %~1 en el IF).
Hola ggg! Muy buen día..
Estoy probando su bat funciona pero me pasa que cuando abro Global.csv no me respeta la estructura de columnas, lo pone todo como una columna y elimina los separadores de campos, aquí lo se lo muestro. Mis ficheros de entrada a unir son del tipo text document, Tab delimiter.
Seguí probando, y resolví una parte.. pero el cero delante del numero lo pierde y la columna agregada queda concatenada a la ultima columna separada por ";" lo ideal seria ponerle el mismo delimitador para que al abrirlo en excel ya quede separada la columna agregada.
Ya!, ya funciono correctamente! ud es un maestro! Muchas gracias...
Tenga muy buen día, le agradezco infinitamente su tiempo, dedicación y resolución del problema,
Un saludo,
Aleida
Una ultima preguntica, ¿si los ficheros a importar y el global.csv están en la misma carpeta es tengo que ponerle el Path del global.csv en en fichero bat?
He estado fuera de casa y, aunque puedo leer en mi celular (móvil, para los españoles) los mensajes, no me siento cómodo contestando en la página desde ese dispositivo. Ahora ya estoy en casa.
En principio pensé que tal vez trabajes con una configuración regional en la que los CSV, como su nombre indica, tienen los campos separados por comas (",") en vez de la mía en la que están separados por "punto y coma" (";"). En ese caso habría que sustituir el ";" del bat por una ",". Pero si ya lo has conseguido no hay más que hablar de este punto.
Respecto a tu última "preguntica" la respuesta dependerá de si usas el arrastre de ficheros sobre el bat (según el esquema del segundo BAT) o si lo ejecutas desde una ventana CMD (según el esquema del primer BAT). En el primer caso hay que incluir el path del global. Csv porque el bat no sabría trabajar sin una referencia de path y no se ejecutaría (de hecho me costó un poco determinar la razón por la que no me funcionaba con los arrastres). En el segundo caso no haría falta el path.
Yo use la variante del arrastre de ficheros sobre el BAT y teniéndolos en la misma carpeta, no me fue necesario poner el Path, se ejecuto perfecto.!
Para el caso de abrirlo en Excel lo solucione poniéndole los dos delimitadores la ", " y el ";"
Creo que bastaría con la "," porque confirma mi sospecha de que la configuración regional con la que trabajas utiliza la coma como "separador de listas", que es el separador original de los "comma separated values". Supongo que ya lo sabías pero por si acaso: https://en.wikipedia.org/wiki/Comma-separated_values.
En cuanto al arrastre de ficheros sobre el BAT si lo hiciste sobre la segunda variante es normal que te funcione bien, pero habrás tenido que rellenar el path del archivo global. Csv. Si no es así sería una sorpresa para mí. En todo caso creo que sería más razonable usar una variable para eso. Algo como:
@echo off
set destino=d:\carpeta prueba
del /q "%destino%\global.csv">nul 2>&1
:Bucle
Echo Tratando archivo %1
set nom10=%~n1
set nom10=%nom10:~-10%
for /f "tokens=* delims=" %%a in ('type %1') do echo %%a;%nom10%>>"%destino%\global.csv"
shift
if "%~1"=="" goto:eof
goto :bucleSi no rellenas el path me gustaría que me copiaras por aquí el bat que usas.
Y cuando consideres respondida la pregunta puedes cerrarla.
El bat que use es el mismo que ud me mando sin el Path.
@echo off
del /q global.csv>nul 2>&1
:Bucle
Echo Tratando archivo %1
set nom10=%~n1
set nom10=%nom10:~-10%
for /f "tokens=* delims=" %%a in ('type %1') do echo %%a;%nom10%>>global.csv
shift
if "%~1"=="" goto:eof
goto :bucleEn a misma carpeta puse en bat y los ficheros a arrastrar. Yo necesito no fijar el path pues el bat lo usare en carpetas que tienen nombres diferentes y tendria que estar cambiando constantemente el path de global.csv.
En Excel si no ponia el ";" me dejaba el campo adicionado concatenado con el ultimo campo que contenia el fichero, mire como se me mostraba..
Lo he probado y, efectivamente, no es necesario definir el path del global. Csv. Parece que no era esa la causa de los problemas que me daba el BAT respecto a la ejecución por arrastre. Pero si no indicas path te crea el archivo en el path del usuario, no en el de los archivos arrastrados. Si quieres determinar la ubicación del archivo se debe hacer con path.
Y no acabo de entender lo que comentas:
En Excel si no ponia el ";" me dejaba el campo adicionado concatenado...
A no ser que haya una errata en tu texto y en lugar de ";" debiera decir ",". En todo caso me gustaría que me confirmaras si el delimitador de CSV en tu configuración regional es "," o ";"
El delimitador de csv mio es ",".
Lo que me referia es que la columna adicionada aparece en el csv con delimitador de " ; " por lo que si al abrirlo en excel no especifico ademas el delimitador " ; " no me lo reconoce y la ultima columna adicionada me queda como concatenada con ";" con la ultima columna del fichero. Al poner el ";" como delimitador tambien lo reconoce perfecto.
Mire aquí como se ve la ultima columna adicionada con el delimitador ";"
Me parece que hay algún malentendido. Sí, como parece, tienes claro que el delimitador de CSV con tu configuración regional es "," basta con que se utilice la coma como separador de campos en los archivos CSV para que Excel automáticamente (sin necesidad de configurar ningún delimitador) "formatee" el CSV a hoja Excel. En ese caso el comando ECHO del bat debería ser:
Echo %%a,%nom10%>>global. Csv
(cambiando el ";" por la ",").
Si no lo tienes muy claro te sugiero que pruebes a generar un archivo de texto (con el Notepad-Bloc de notas por ejemplo) separando con comas diferentes campos en una o dos líneas. Luego lo salvas con extensión CSV y luego lo abres directamente con Excel. Si el delimitador de CSV (también llamado "separador de listas" en la configuración regional) es "," al abrirlo con Excel debe salir formateado en filas y columnas (una fila por línea y una columna por cada coma). Luego cambias las comas por ";" y repites la prueba y verás que todo se almacena en una sola columna.
En resumen que en el ECHO debe figurar o bien "," o bien ";" pero nunca las dos. Y que no debe ser necesario hacer ningún retoque en Excel.
El formato CSV me parece una idea "milagrosa" para establecer un puente entre el "bajo nivel" (shell MSDOS) y el "alto nivel" (Excel, Visual Basic, etc.). Con un bat puedes "formatear" filas y columnas de manera que luego Excel las trate como has previsto.
No sé si piensas hacer algo más en este tema o si ya lo das por cerrado, en cuyo caso procedería, creo, cerrar la pregunta.
