Excel varias pestañas y datos dispersos en varias filas o columnas pasarlo a fichero tabulado
Me pasan fichero excel con multitud de pestañas tal que así
Dentro de cada hoja hay datos agrupados por Repartos como código cliente, nombre, población y orden
pero pueden estar en cualquier parte de la hoja excel (columna y fila), en este caso empiezan en la columna A y F
pero también pueden empezar en la columna B fila 23
Se require fichero tabulado en el cual la primera columna sea el nombre de la pestaña/hoja del excel
y el resto las columnas antes mencionadas: reparto1 o reparto2, codigocliente, población, orden
este sería un ejemplo para pestaña 174 reparto 2
En lugar de manipular todos estos datos a mano, un script utilizando herramientas disponibles en linux genera
el fichero deseado en 1 segundo:
```
#!/usr/bin/env bash
# Función: crear YVLG_KMS_ORDEN segun excel que nos pasen
# Fecha creación: 13.04.2023
# Autor: Julio Briso-Montiano
# Versión: 1.0
# Detalle:
# - correr primero xlsx2csv -s 0 -d tab EXCEL_ORDEN.xlsx > EXCEL_ORDEN.txt
# - tu responsabilidad chequear
# - si quieres excluir alguna ruta renombra la hoja del excel que sea añadiendo alguna letra p.e "x"
#
set -ueo pipefail
if [ $# -lt 1 ]
then
echo "Formato: ./kms.sh <excel.xlsx>"
exit
fi
xlsx2csv -s 0 -d tab $1 > tmpkms.tsv
awk -F"\t" '{
gsub("\r","")
if ( substr($1,1,7) == "-------" && NF == 1) {
gsub(" ","")
split($1, a, "-")
#ruta=sprintf("%06i",a[10])
ruta=substr("000000"a[10],1 + length("000000"a[10]) - 6)
}
if ( ruta*1 > 1 )
for (i=1; i <= NF; i++) {
if ( match($i," Reparto ") ) {
reparto=substr($i, RSTART+9,1)
colreparto[i]=reparto
}
if ( ($i*1 > 10000000 && $i*1 < 19999999) || $i == "CE20" ) {
if ( $i == "CE20" )
tipo="A"
else
tipo="E"
print ruta,colreparto[i],"01.01.2023","31.12.9999",$i,$(i+3),tipo,"X"
}
}
}' OFS="\t" tmpkms.tsv > tmpyvlg_kms_orden.tsv
sort -t$'\t' -k1 -k2 -nk6 tmpyvlg_kms_orden.tsv > yvlg_kms_orden.tsv
echo -e "Generado fichero yvlg_kms_orden.tsv"
```
Ampliación de registros dependiendo de que otros que ya existan de antemano
En el almacén quieren optimizar el espacio en las baldas de las estanterías y van a ampliar a 2 ubicaciones más.
A nosotros nos dicen que aquellas baldas que tenga hasta 6 posiciones dentro de ellas, que creemos la posición 7 y 8.
Nos pasan el fichero total de todas sus ubicaciones ya creadas pero no aportan ninguna regla sobre ellas.
Al ver el fichero que nos dan vemos que:
- los registros tienen distinta longitud
- algunos registros ya terminan en 7, no tenemos que sugerir su creación
- no todas "las series o patrones" que pudiera deducirse que siguen los registros de ubicaciones tienen hasta posición 6
Nos piden que solo lo que termine en 6 tenemos que crear el registro 7 y 8, a no ser que exista ya.
AWK una vez más con pocas líneas nos las creará:
el fichero que nos dan lo llamaremos lagp.txt lo leemos previamente para saber las que ya existen solo si termina en 6 miramos de crear la 7 y 8
```
dos2unix -q lagp.txt
awk '
FNR==NR { lagp[$1]="e"; next}
END {
for ( ubi in lagp ) {
lon=length(ubi)
if ( substr(ubi,lon-1,2) == "06" )
for ( bandeja=7; bandeja<=8; bandeja++ ) {
nue=substr(ubi,1,lon-1)bandeja
if ( lagp[nue] != "e" ) print nue
}
}
}' lagp.txt | sort > lagp_nuevas.txt
```
En 1 segundo tenemos todos las ubicaciones nuevas sea cual sea el patrón que tenga cada parte del almacén.
Redmine, diversos scripts III
Otro script en bash y sobre todo awk.
Es lo que mas me viene siempre a mano en cuanto se me ocurre automatizar alguna tarea, o sobre todo sacar resumen de una fuente ingente de datos.
Esta vez el script tiempo_reaccion.sh exporta tickets de redmine entre las fechas que se le meta como parámetros y analiza el tiempo de respuesta en conjuntoe:
- descarga todos aquellos tickets entre fechas
- los que tengan tiempo de respuesta superior a una dada analiza que:
- si fueron dados de alta durante el finde de semana los excluye (desde el viernes por la tarde)
- si fueron dados de alta fuera jornada laboral en día de diario también los excluye
- si son tickets creados por nosotros, no contabilizan para el tiempo de respuesta
- crea por cada casuística 1 fichero csv
Como siempre mola obtener todos esos datos de forma rápida y se agradece no tener que trabajar de forma manual con el excel.
awk es una herramienta bestial, creada hace muchos años y que se maneja muy bien con cantidad de datos enorme.
mRemoteNG sacar passwords almacenadas
Cuando se exporta todos los datos desde el propio programa mRemoteNG sea a xml o csv, este lo hace con las claves password cifradas.
Gracias al programa en python de https://github.com/kmahyyg/mremoteng-decrypt se pueden extraer de 1 en 1.
Con este programa pasando como parámetros un fichero a mi no me ha funcionado. Sí que lo hace si se pasa con la opción "-s" "passwordciffrado".
Un ejemplo del xml exportado
Para no tener que ir utilizando varias veces lanzando el programa mremoteng-decrypt.py -s "xxefseasdf.." hice este script en awk que sacaría en este formato de pantalla el password descifrado por cada nodo que tenga el xml.
La salida sería parecida a esto:
Lo modificaré para que me cree un script/fichero por cada nodo en el xml, que lance el servicio que sea según tipo conexión RDP/VNC/SSH, con el password y username. Así tendría un fichero/script por cada conexión sin tener que tener que importar datos a otro programa tipo Reminna.
```
awk '{
for (i=0;i<=NF;i++) {
if ($i ~ /^Name|^Node\ Name=|^Username=|^Domain=|^Password=|^Hostname=|^Protocol=|^Port=/) {
if ($i ~/^Name/) print "----------"
print $i
if ($i ~ /^Password=/) {
split($i,p,"\"");
pass="";
if (length(p[2]) > 2) {
"python mremoteng_decrypt.py -s "p[2] | getline pass;
print "** "pass;
}
}
}
}
}' xml
```
Tags: awk
DHCP detectar nuevos equipos en la red con BASH
El script empezó siendo mucho más simple y solo en bash, pero al final quería que me mandase un email cada vez que encontrase un nuevo equipo, y tuve que utilizar un programa hecho por mi en Go para mandar los emails.
Además utiliza primordialmente awk que es bastante rápido en procesar ficheros de texto.
Ya lo tenía hace muchos años hecho y funcionando perfectamente en la red de mi trabajo; estaba hecho en python para redes windows. detectar-y-avisar-nuevas-concesiones-de-ip-en-nuestra-red-dhcp.html
El script busca nuevos equipos por MACs y es sabido que ya hay formas de crear nuevas macs para cada conexión así que puede haber nuevos falsos positivos por ser el mismo equipo.
Para adaptarse a uno mismo se tendría que cambiar:
- los interfaces a poner en arp-scan
- la red a buscar
- todo lo referente al envío del email
Finalmente se pone este script en el crontab para que se ejecute cada cierto tiempo y ya está.
Una vez que recibo un aviso, busco el equipo, edito el fichero dhcp.txt poniendo el nombre que quiera en la última columna y ordeno por mac (esto último porque sí).
El fichero dhcp.txt tiene esta pinta:
Solo por poner ideas.
```
#!/usr/bin/env bash
# Función: enviar email cada vez que se detecte una mac distinta conectada a la red
# Fecha creación: 29.12.2021
# Autor: Julio
# Versión: 1.0
# Detalle:
# - se necesita instalar arp-scan
# - se necesita mandaemail hecho por mi en Go
# - contiene passwd
#
now=$(date +%d/%m/%Y"_"%T)
sudo arp-scan -x -I wlp59s0 192.168.0.0/24 > wdhcp_now.txt
sudo arp-scan -x -I enp58s0f1 192.168.0.0/24 >> wdhcp_now.txt
cat wdhcp_now.txt | sort -k2,2 | uniq > dhcp_now.txt
awk -F"\t" -v now="$now" '
NR == FNR {
mac[$2]=$0;
next;
}
{
if (mac[$2] == "") {
nuevo+=1
printf("\n%s\tdispositivo nuevo: %s", nuevo, $0)
#gsub("\\:",":",rit2)
print $0"\t"now"\t?" >> "dhcp.txt"
}
}
END {
if (nuevo>0) {
printf("\nse encontraron %s nuevos\n", nuevo)
print "semaforo" > "dhcp_nuevos.txt"
}
else {
print("\ntodo ok")
}
}
' dhcp.txt dhcp_now.txt
if test -f "/tu_ruta/dhcp_nuevos.txt"
then
cadena="email_emisor|tupassword|tuservidor_smtp|tu_puerto_smtp|destinatario|Nuevos_equipos_detectados_"$now"|.|dhcp.txt|"
echo -e "\nmando email"
mandaemail "$cadena"
rm dhcp_nuevos.txt
fi
rm wdhcp_now.txt
rm dhcp_now.txt
```
Ahorrando tiempo de excel con bash awk
Tarea repetitiva con excel
Debido a un proceso de unificación de bases de datos, me pedían varias veces un excel resultante de la combinación de varios excel.
Si veo que esto será un proceso habitual y necesario a lo largo del tiempo, no me complico y prefiero hacer un programa que me lo genere directamente, pero como no es el caso pues será para unas pocas veces prefiero combinar los datos que ya tengo a un fichero.
Los ficheros excel de los que partía tienen varias columnas, más de 80, y el usuario que pedía el informe quería sólo unas columnas y en un orden específico. Ambas excel contenían en total varios miles de registros (>100K) por lo que siempre tocaba hacer:
- abrir los dos excel
- eliminar las columnas innecesarias
- insertar columnas nuevas para por ejemplo sumas de otras (ventas, stock ..)
- utilizar el BUSCARV para enlazar datos de una excel con la otra
- reordenar las columnas con lo orden que se nos pide (cortar/insertar columna).
Bien, al final con la práctica y dependiendo de la memoria del pc que se utilice, todo eso lleva unos minutos.
Pero el Excel/Libreoffice cada vez que mueve columnas con fórmulas, si se tiene el cálculo automático activado, sufre de pausas de tiempo que molestan mucho. Lo mismo como hayas añadido tablas dinámicas.
Y menos mal que esta vez NO hacemos filtros de datos ni tampoco ordenamos, porque eso multiplica el tiempo de pausas en cada operación de cortar-insertar-filtrar-ordenar del Excel.
Ahorrando tiempo con awk
Me fastidia hacer siempre lo mismo pudiendo emplear el tiempo para otras cosas, además de que en las tareas repetitivas manuales es más fácil equivocarse y que te toque deshacer para volver a empezar.
Así que decido hacer un script en bash utilizando awk que me lea los dos ficheros csv y me genere un resultante con todo lo pedido. ¡no tarda más de 2 segundos de reloj! también dependiendo de la máquina.
Este es el script. Los ficheros csv iniciales son bv.csv y bz.csv de unos 21Mb cada uno.
#!/usr/bin/env bash
#cp /media/julio/vbbdd/datos_valladolid/ARTICULOS_Valladolid.csv bv.csv
#cp /media/julio/vbbdd/datos_zamora/ARTICULOS_Zamora.csv bz.csv
#
awk -F';' '
NR==FNR {
zstock[$1]=$8;
zventas[$1]=$85;
zmcam[$1]=$86;
next;
}
{
gsub("\r","");
vventas=$85;
vstock=$8;
vmcam=$86;
cventas=vventas+zventas[$1];
cstock=vstock+zstock[$1];
cmcam=vmcam+zmcam[$1];
cggc=" ";
encargo=" ";
if ($1 == "CODIGO") {
cventas="COOP_VENTAS_2019";
cstock="COOP_STOCK";
cmcam="COOP_UNIDADES_PDTES_PROVEEDOR";
cggc="CODGGC";
encargo="ENCARGO";
}
print $1,cggc,$6,$2,$40,$38,$39,$22,$20,$21,$30,$26,$41,$54,$55,$56,$57,$35,$3,cstock,cventas,cmcam,$5,encargo,$4
} ' OFS='\t' bz.csv bv.csv > bbdd_arts.csv
Dije 2 segundos y no llega ni a uno.
Lee primero un fichero separado por ";" donde recogemos los datos que necesitaremos consolidar, luego con el segundo fichero que lee va componiendo las columnas con las sumas, se crea la cabecera de texto con las columnas nuevas, y por fin guardamos todo un fichero separado por tabuladores listo para leer por el destinatario en su Excel.
Hay que tener en cuenta, repito, que no ordenamos ni tampoco hacemos filtros de datos. Si fuera así, el trabajo en Excel sería todavía más tedioso y en cambio con bash serían un par de palabras más añadidas al script.
Ver la cabecera y número de columna de un fichero csv
El inconveniente que se podría decir es que es laborioso con awk saber el número de campo que queremos imprimir, pues nos toca contar el orden de los campos para saber por ejemplo que la columna "ALTO" corresponde con el número de campo $54 del csv.
Para ello se tiene otra función que se carga automáticamente en bash que es fieldsc y muestra los campos de un csv y su numeración de forma fácil, y tampoco es una función muy complicada:
Como decía esta función y muchas otras las tengo disponibles siempre en el terminal pues se carga en el .bashrc con source bash_functions.sh.
Por cierto esta función es gentileza de Robert Mesibov ( BASHing data ) un maestro en lo que él mismo se autodenomina "data auditor and cleaning". Mucho que aprender de él en estos tiempos del Big Data.