Empiezo advirtiendo que lo que explicaré está basado en un proceso muy concreto, pero bueno, también puede servir para observar, entender y ofrecer una solución con powershell.
¿Que va hacer este script?
Bien, de vez en cuando se nos plantean problemas que hasta el momento no sabrías que responder. Esto me pasó a mi hace poco.
Se me presentó una tarea tediosa, 5 documentos excel (xls), con 3500 registros cada documento. En cada registro está el campo Nombre, Apellidos, Sede y E-Mail.
El problema planteado era que en el campo E-Mail podían haber 1 o más e-mails separados por una coma. El trabajo consistía en dejar un E-Mail por registro, y en el caso de que hubiesen más, replicar los campos que le pertenecen tantas veces como sea necesario.
Ej:
Nombre;Apellidos;Sede;Email
Jorge;Alzaro;Barcelona;jorge-alzaro@hotmail.com, alzaroj@gmail.com, jorge@alzaro.com
La transformación sería la siguiente:
Nombre Apellidos Sede Email
Jorge Alzaro Barcelona jorge-alzaro@hotmail.com
Jorge Alzaro Barcelona alzaroj@gmail.com
Jorge Alzaro Barcelona jorge@alzaro.com
Como veis, serían muchas horas hacerlo a mano. Así que aquí entra Powershell.
Empezamos:
De buenas no podremos ejecutar scripts en powershell, esto lo podemos comprobar escribiendo
Nos devolverá
RestrictedEsto significa que no va a poder cargar ni archivos de configuración ni ejecutar scripts desde la consola powershell. Para cambiar esto, ejecuntado powershell con privilegios de administrador escribiremos
Set-ExecutionPolicy Unrestricted
Ahora ya podemos empezar con el script:
Las primeras líneas serán un clear, un mensaje y una función para eliminar ficheros que usaremos más adelante:
cls
Write-Host "----=[Procesando ficheros"
Function Remove-File($fileName) {
if(Test-Path -path $fileName) { Remove-Item -path $fileName }
}
Se puede observar como el cmdlet Test-Path lo que hace es comprobar que exista la ruta pasada por parámetro y Remove-Item lo elimina.
La primera acción que se realizará; será recorrer la carpeta donde tenemos los ficheros xls con los que queremos tratar, para posteriormente convertirlo a CSV.
#--------------------------------------------------------------------------------------------------#
#--------------------------=[ CONVERTIR XLS DE LA CARPETA EN CSV ]=--------------------------------#
#--------------------------------------------------------------------------------------------------#
$carpeta = "C:\separar_comas\ficheros_separar"
ls $carpeta | foreach-object{
$excelFile = "$carpeta\$($_)"
if(Test-Path -path $excelFile) {
$csvFile = ("C:\separar_comas\ficheros_csv" + "\" + ((Get-Item -path $excelFile).name).Replace(((Get-Item -path $excelFile).extension),".csv"))
Remove-File $csvFile
$excelObject = New-Object -ComObject Excel.Application
$excelObject.Visible = $false
$workbookObject = $excelObject.Workbooks.Open($excelFile)
$workbookObject.SaveAs($csvFile,23) # http://msdn.microsoft.com/en-us/library/bb241279.aspx
$workbookObject.Saved = $true
$workbookObject.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbookObject) | Out-Null
$excelObject.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObject) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
get-content $csvFile | Out-File -Encoding UTF8 "$csvFile.utf"
Remove-File $csvFile
Write-Host "$($_) ·····=| convertido a CSV"
}
}
Cabe decir que las carpetas se tendrán que preparar previamente a la ejecución del script, sino saltará un error pues no encontrará la carpeta destino.
El siguiente paso sería separar las comas del mail y duplica las filas que sean necesarias
#--------------------------------------------------------------------------------------------------#
#--------------------------------=[ SEPARAR COMAS FICHEROS CSV ]=----------------------------------#
#--------------------------------------------------------------------------------------------------#
$carpeta = "C:\separar_comas\ficheros_csv"
ls $carpeta | foreach-object{
$fichero = "$carpeta\$($_)"
$fich = "$($_)";
echo "Nombres;Apellidos;Sede;E-mail" > "C:\separar_comas\ficheros_separados\$fich";
import-csv -path $fichero -header("Nombres", "Apellidos","Sede", "Email") -Delimiter ";" | foreach-object{
$mails = "$($_.Email)";
$split = $mails.split(",");
$filanterior = "";
foreach($filas in $split){
$filas = $filas.Replace(" ","");
$filtrar = $filas -match '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$';
if($filtrar){
if($filas -ne $filanterior){
echo "$($_.Nombres);$($_.Apellidos);$($_.Sede);$filas" >> "C:\ficheros_separados\$fich";
$filanterior = $filas;
}
}
}
}
Write-Host "$fich ·····=| Proceso de delimitadores realizados"
}
El proceso es similar, recorremos el directorio donde hemos guardado los csv en el paso anterior para que estos sean procesados uno a uno gracias a foreach-object, muy fácil de implementar con un pipe después de hacer un ls o dir a $carpeta.
$_ = Devolverá el resultado que corresponda en ese momento a la fila obtenida por foreach-object, al indicar $($_.Email) estamos extrayendo el campo Email de $_ que es la primera fila.
Al hacer el import-csv es inteligente y lo monta en un array asociativo según el -header indicado y el -Delimiter asociado, o sino lo indicamos obtendrá los datos de la línea 0. En mi caso lo indico pues en el campo E-Mail me causaba problemas con el "-".
Es de vital importancia usar rutas absolutas si generamos el proyecto en una carpeta de usuario, ya que hasta el momento he tenido muchos problemas con las rutas relativas. Si lo generáis en C:\ con que pongais ..\ ya os reconocerá la raíz del sistema.
A parte de generar la transformación gracias a la recursividad entre los foreach , este comprobará que los mails tengan el formato correcto mediante una expresión regular.
Esto sería todo de momento, ya que de nuevo se podrían convertir a xls o xlsx, aunque todo sea dicho, powershell es muy lento aun al generar ficheros de gran magnitud de información bajo la API de .NET Framework orientado a office. Si me veo con tiempo explicaré el código para hacerlo reversible y volver a generar el fichero excel.
Saludos
