Cómo fusionar varios archivos de Excel de Mac, todos con encabezados iguales pero desordenados

Question

Estoy usando una Mac. Mis archivos de Excel tienen encabezados que coinciden pero están desordenados. Todos comienzan con un encabezado llamado "consulta" y luego se mezclan.

A continuación se muestran algunos ejemplos de encabezados:

• consulta|correo electrónico|facebookUrl|twitterUrl|número de teléfono|marca de tiempo|instagramUrl|error|linkedinUrl|youtubeUrl
• consulta|facebookUrl|linkedinUrl|número de teléfono|marca de tiempo|youtubeUrl|correo electrónico|error|instagramUrl|twitterUrl
• consulta|correo electrónico|facebookUrl|instagramUrl|marca de tiempo|linkedinUrl|youtubeUrl|phoneNumber|twitterUrl|error

Puedo usar Terminal para fusionar todos los archivos csv con cat *.csv >combined.csv. Pero no funciona bien porque todos los encabezados están desordenados.

¿Alguna idea de lo que puedo hacer?

Answer 1

Debe utilizar una herramienta compatible con CSV. Uno genial esMolinero

Si tiene, por ejemplo, estos 3 archivos CSV

#input_01.csv
a,b,c
0,2,5

#input_02.csv
b,a,c
0,2,5

#input_03.csv
b,a
0,2

puedes correr mlr --csv unsparsify ./input_0*.csv >./output.csvpara tener

+---+---+---+
| a | b | c |
+---+---+---+
| 0 | 2 | 5 |
| 2 | 0 | 5 |
| 2 | 0 | - |
+---+---+---+

Answer 2

Si sus archivos tienen un tamaño bestial, busque Power Query. Personalmente actualmente no tengo las habilidades para reordenar sus columnas en un orden particular, aunque podría reordenarlas alfabéticamente... creo). Así que no hablaré aquí sobre cómo lograrlo, al menos no hoy.

Puede hacer algunas cosas sorprendentes (sorprendente para alguien acostumbrado a estar limitado por los límites de Excel y sin suficiente experiencia en VBA para hacer algo más que cosas marginalmente complicadas). Definitivamente puede hacer el trabajo, eso lo sé, aunque no creo que lo haga pronto.

Sin embargo, si los archivos no tienen un tamaño bestial para que unMUYUn enfoque de fórmula simple podría hacer el trabajo sin bloquear su computadora, o al menos Excel, entonces el siguiente es un enfoque muy simple.

No vi si los archivos que utiliza deben continuar existiendo exactamente como están ahora o si pueden modificarse. Si deben permanecer como están, cree un subdirectorio y cópielos todos en él. Si cambiarlos está bien, es más fácil, un poquito ya que no hay copia de los archivos, pero si los archivos se seguirán usando y simplemente reordenar las columnas no causará problemas, sería mucho mejor cambiarlos. para que nunca más sea necesario cambiarlos en el futuro. "Uno y listo", como dicen.

Para reordenar las columnas sin mucho trabajo, simplemente use la siguiente fórmula:

=INDEX($I$1:$O$4,SEQUENCE(ROWS($I$1:$O$4)),MATCH($A$1:$G$1,$I$1:$O$1,0))

(Para la fórmula, puse una tabla de 6 columnas por 4 filas en A:G, luego otra en I:O con los mismos encabezados pero en orden mixto en relación con la tabla A:G).

INDEX()se le proporciona la tabla que desea reordenar, I1:O4 en este caso. Para decirle que use todas las filas, usamos SEQUENCE()un parámetro (dado que "filas" o salida es su primer parámetro, no necesitamos más). Ese parámetro es el número de filas de la tabla que necesita reordenar. El uso de secuencia le permite darle una dirección de rango como lo hice yo, un rango con nombre (realmente por qué uno lo usaría así),Osolo el número de filas. Después de todo, en la mayoría de las tablas eso se obtiene simplemente conociendo el lado derecho del rango, ¿eh? O tal vez simplemente reste algún valor para las filas superiores. Sólo con un rango con nombre realmente tienes que hacer algo más que simplemente decir algo como "Hmm... I1:O4... debe tener 4 filas, así que solo ingresaré 4...".

La parte final es la buena: el orden de las columnas. INDEX()le permite reorganizar las columnas y/o hacer que aparezcan más de una vez. Si supiera el orden que necesitaba para todos los archivos, podría ingresar algo como {4,2,19,7,...}para indicarlo, pero no lo sabe. Podrían ser de cualquier orden y cada uno diferente.

Pero si usa MATCH(), puede dejar que Excel haga el trabajo por usted para todos los diferentes pedidos. Le indica MATCH()que use los encabezados de un archivo, cualquiera que sea, no importa, pero usa el orden de los encabezados de ese archivo para TODOS los archivos que abre y reorganiza. Entonces, Match busca cada uno de ellos en los encabezados del archivo actual y alimenta el orden que encuentra para INDEX()luego volver a ensamblar sus datos en el mismo orden de encabezado que el archivo que eligió como fuente de orden.

Aún mejor, casi, es el hecho de que la fórmula produce una matriz SPILL 2-D, por lo que copiar la fórmula en una sola celda producirá la tabla de salida completa.

Dado que lo hará en un archivo que se puede guardar una vez finalizado el trabajo (ya sea un conjunto copiado o los originales, de una forma u otra trabajará con archivos que se pueden cambiar y guardar), lo siguiente que desea es seleccione el resultado completo, luego copie y Paste|Special|Valuesobtenga solo datos, no resultados de fórmula. Luego elimine el material original dejando solo la tabla de salida. Ahorrar. Pasa al siguiente.

Una ventaja, si planea hacerlo en los originales y se utilizarán más, sería seleccionar la tabla original y copiarla, luego Paste|Special|Formatsmantener el formato de la tabla en la tabla de salida. Incluso hazlo Paste|Special|Column widthspara mantener la misma apariencia.

Supongo que no necesitarás el ancho de las columnas, ahora que lo pienso, ya que parece que estás planeando unir archivos CSV... Prácticamente NINGUNO de los formatos importará. Aún así, si utiliza archivos activos y espera utilizarlos en el futuro, siga los pasos de formateo antes de eliminar las tablas originales. Guárdelo como archivos de Excel y luego una copia CSV. Supongo que tal vez ya sean archivos CSV, así que no te preocupes por nada de eso, pero tenlo en cuenta para otros usos del enfoque.

De todos modos, abra el primer archivo de la lista, copie esta fórmula y ajuste el direccionamiento según sea necesario, luego copie la celda de la fórmula, abra el segundo archivo de la lista y péguelo. Haga lo anterior para cambiarlo a datos, elimine el anterior y luego guárdelo nuevamente en el mismo formato CSV. Enjuague y repita hasta que se hayan arreglado todas las limas. Luego únete a ellos.

Aún mejor, si eres lo suficientemente bueno con VBA, abre un nuevo archivo, dale un nombre y guárdalo (como un archivo de macro, es decir, .xlsm), escribe una macro para dividir los archivos en el directorio, agregando la fórmula a cada uno, cambiando la salida solo a datos, eliminando todo lo que está a la izquierda para que la primera celda de la salida sea A1 y guardando cada uno como un CSV. Guarde este nuevo archivo nuevamente. Luego extiéndalo y déjelo funcionar.