Convertir plusieurs fichier CSV en feuilles Excel via PowerShell / How to convert multiple CSV files into into Excel Workbooks with PowerShell
[MAJ 06/03/2017] Dans le même esprit que mon précédent article, je vous partage un script PowerShell permettant de convertir un fichier CSV en feuille Excel (.xlsx). Le fichier source est disponible ici.
#region function definitions
Function Remove-Ref
{
[CmdletBinding()]
param
(
[parameter(Mandatory = $true, HelpMessage = 'Please specify a reference')]
[Object] $ref
)
<#
.SYNOPSIS
Releases a COM Object
.DESCRIPTION
Releases a COM Object
.PARAMETER ref
The COM Object to release
.EXAMPLE
$Excel=new-object -ComObject "Excel.Application"
...
Remove-Ref ($Excel)
#>
$null = Remove-Variable -Name $ref -ErrorAction SilentlyContinue
while ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
{
}
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
Function ConvertTo-XLSX
{
<#
.SYNOPSIS
Convert a CSV File into a XLSX document
.DESCRIPTION
Convert a CSV File into a XLSX document
.PARAMETER FullName
The CSV File to convert specified by its full name or by a System.IO.FileInfo object
.PARAMETER Force
A switch to specify if we overwrite the XLSX document even if it is newer than that the CSV File
.PARAMETER Visible
A switch to specify if the Excel application will be visible during the processing
.EXAMPLE
Get-ChildItem "*.csv" | ConvertTo-XLSX -Verbose
Will convert all CSV Files into XLSX documents but only if the CSV File is newer than the related XLSX document (if any)
.EXAMPLE
Get-ChildItem "*.csv" | ConvertTo-XLSX -Verbose -Force
Will convert all CSV Files into XLSX documents even if the XLSX document is newer than the original CSV File
.EXAMPLE
Get-ChildItem "*.csv" | ConvertTo-XLSX -WhatIf
Will convert all CSV Files into XLSX documents in risk mitigation mode
.EXAMPLE
ConvertTo-XLSX -FullName "processes.csv","services.csv"
Will convert the two CSV Files into XLSX documents
#>
[CmdletBinding(SupportsShouldProcess = $true)]
Param(
#The CSV File to convert
[Parameter(Mandatory = $true,HelpMessage = 'Please enter the full path of a CSV file', ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
[ValidateScript({
(Test-Path -Path $_ -PathType Leaf) -and ($_ -match '\.csv$')
})]
[String[]]$FullName,
#To overwrite the previously generated XLSX files."
[parameter(Mandatory = $false)]
[switch]$Force,
#To display the Excel application
[parameter(Mandatory = $false)]
[switch]$Visible
)
begin
{
#Loading Excel Properties
$null = Add-Type -AssemblyName Microsoft.Office.Interop.Excel
#Static value for the Excel Workbook default
$xlWorkbookDefault = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
#For Gray color for first row heading
$Gray = 15
Write-Verbose -Message 'Running the Excel application ...'
#Opening the Excel Application
$Excel = New-Object -ComObject 'Excel.Application'
$Excel.Visible = $Visible
$Excel.Application.DisplayAlerts = $false
}
process
{
#For all files passed as argument outside a pipeline context
Foreach ($CurrentFullName in $FullName)
{
$CurrentFullName = Get-Item -Path $CurrentFullName
#Getting the fullname of the processed CSV File
$SourceCSVFullName = $CurrentFullName.FullName
#Getting the name of the processed CSV File
$SourceCSVName = $CurrentFullName.Name
#Getting the final XLSX full name (same directory and name that the CSV File)
$TargetXLSXDocumentFullName = $SourceCSVFullName -replace '\.csv$', '.xlsx'
#Getting the write time of the CSV File
$SourceCSVTimeWritten = $(Get-Item -Path $SourceCSVFullName).LastWriteTime
#Getting if -Force was specified or if the XLSX doesn't exist or if the CSV File is newer that a previously generated XLSX Document.
If (($Force) -or (!(Test-Path -Path $TargetXLSXDocumentFullName)) -or ($(Get-Item -Path $TargetXLSXDocumentFullName).LastWriteTime -lt $SourceCSVTimeWritten))
{
#If -Force was specified
If ($Force)
{
Write-Verbose -Message ('Forcing {0} ...' -f $SourceCSVFullName)
}
#If the XLSX file doesn't exist
ElseIf (!(Test-Path -Path $TargetXLSXDocumentFullName))
{
Write-Verbose -Message ('Processing {0} ...' -f $SourceCSVFullName)
}
#If the CSV file is newer that a previously generated XLSX Document.
ElseIf ($(Get-Item -Path $TargetXLSXDocumentFullName).LastWriteTime -lt $SourceCSVTimeWritten)
{
Write-Verbose -Message ('Updating {0} ...' -f $SourceCSVFullName)
}
#Risk Mitigation : support of -whatif and -confirm
If ($pscmdlet.ShouldProcess($SourceCSVFullName, 'Converting'))
{
Write-Verbose -Message 'Opening the CSV File ...'
$WorkBook = $Excel.Workbooks.Open($SourceCSVFullName)
#Adding a worksheet
$WorkSheet = $WorkBook.worksheets.Item(1)
$Range = $WorkSheet.UsedRange
$null = $Range.EntireColumn.AutoFit()
#Getting teh first row
$firstRow = $WorkSheet.cells.item(1,1).entireRow
#Make Headings Bold
$firstRow.Font.Bold = $true
#Freezing header row
$Excel.ActiveWindow.SplitColumn = 0
$Excel.ActiveWindow.SplitRow = 1
$Excel.ActiveWindow.FreezePanes = $true
#Add Data Filters to Heading Row
$null = $firstRow.AutoFilter()
#Setting header row gray
$firstRow.Interior.ColorIndex = $Gray
Write-Verbose -Message 'Saving the XLSX document ...'
$WorkBook.SaveAs($TargetXLSXDocumentFullName, $xlWorkbookDefault)
Write-Verbose -Message 'Closing the CSV File ...'
$WorkBook.Close()
Write-Verbose -Message 'Releasing firstRow ...'
Remove-Ref -ref ($firstRow)
Write-Verbose -Message 'Releasing Range ...'
Remove-Ref -ref ($Range)
Write-Verbose -Message 'Releasing WorkSheet ...'
Remove-Ref -ref ($WorkSheet)
Write-Verbose -Message 'Releasing WorkBook ...'
Remove-Ref -ref ($WorkBook)
}
# Write-Host -Object "File saved to:" $TargetXLSXDocumentFullName
Write-Host -Object ("The XLSX file is available at : '{0}'" -f $TargetXLSXDocumentFullName)
}
else
{
Write-Verbose -Message ("Skipping '{0}' because it is up-to-date`r`nUse -Force to overwrite previously generated XLSX file" -f $SourceCSVName)
}
}
}
end
{
Write-Verbose -Message 'Exiting the Excel application ...'
$null = $Excel.Quit()
Remove-Ref -ref ($Excel)
}
}
#endregion
Clear-Host
# To get the directory of this script
$CurrentDir = Split-Path -Path $MyInvocation.MyCommand.Path -Parent
Get-ChildItem -Path $CurrentDir -Filter '*.csv' -Recurse | ConvertTo-XLSX -Force -Verbose
# ConvertTo-XLSX -FullName "$CurrentDir\processes.csv","CurrentDir\services.csv" -Force
# Get-ChildItem -Path $CurrentDir -Filter "*.csv" -recurse | ConvertTo-XLSX -Verbose
# Get-ChildItem -Path $CurrentDir -Filter "*.csv" -recurse | ConvertTo-XLSX -WhatIf -Verbose
# Get-ChildItem -Path $CurrentDir -Filter "*.csv" -recurse | ConvertTo-XLSX -Visible
Ce script est aussi disponible dans le TechNet Script Center : https://gallery.technet.microsoft.com/Convert-multiple-CSV-files-0581efca?redir=0
[Updated 03/06/2017] In the same way as my previous article, I share you a PowerShell script to convert a CSV file into an Excel spreadsheet (.xlsx). The source file is available here.
#region function definitions
Function Remove-Ref
{
[CmdletBinding()]
param
(
[parameter(Mandatory = $true, HelpMessage = 'Please specify a reference')]
[Object] $ref
)
<#
.SYNOPSIS
Releases a COM Object
.DESCRIPTION
Releases a COM Object
.PARAMETER ref
The COM Object to release
.EXAMPLE
$Excel=new-object -ComObject "Excel.Application"
...
Remove-Ref ($Excel)
#>
$null = Remove-Variable -Name $ref -ErrorAction SilentlyContinue
while ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
{
}
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
Function ConvertTo-XLSX
{
<#
.SYNOPSIS
Convert a CSV File into a XLSX document
.DESCRIPTION
Convert a CSV File into a XLSX document
.PARAMETER FullName
The CSV File to convert specified by its full name or by a System.IO.FileInfo object
.PARAMETER Force
A switch to specify if we overwrite the XLSX document even if it is newer than that the CSV File
.PARAMETER Visible
A switch to specify if the Excel application will be visible during the processing
.EXAMPLE
Get-ChildItem "*.csv" | ConvertTo-XLSX -Verbose
Will convert all CSV Files into XLSX documents but only if the CSV File is newer than the related XLSX document (if any)
.EXAMPLE
Get-ChildItem "*.csv" | ConvertTo-XLSX -Verbose -Force
Will convert all CSV Files into XLSX documents even if the XLSX document is newer than the original CSV File
.EXAMPLE
Get-ChildItem "*.csv" | ConvertTo-XLSX -WhatIf
Will convert all CSV Files into XLSX documents in risk mitigation mode
.EXAMPLE
ConvertTo-XLSX -FullName "processes.csv","services.csv"
Will convert the two CSV Files into XLSX documents
#>
[CmdletBinding(SupportsShouldProcess = $true)]
Param(
#The CSV File to convert
[Parameter(Mandatory = $true,HelpMessage = 'Please enter the full path of a CSV file', ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
[ValidateScript({
(Test-Path -Path $_ -PathType Leaf) -and ($_ -match '\.csv$')
})]
[String[]]$FullName,
#To overwrite the previously generated XLSX files."
[parameter(Mandatory = $false)]
[switch]$Force,
#To display the Excel application
[parameter(Mandatory = $false)]
[switch]$Visible
)
begin
{
#Loading Excel Properties
$null = Add-Type -AssemblyName Microsoft.Office.Interop.Excel
#Static value for the Excel Workbook default
$xlWorkbookDefault = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
#For Gray color for first row heading
$Gray = 15
Write-Verbose -Message 'Running the Excel application ...'
#Opening the Excel Application
$Excel = New-Object -ComObject 'Excel.Application'
$Excel.Visible = $Visible
$Excel.Application.DisplayAlerts = $false
}
process
{
#For all files passed as argument outside a pipeline context
Foreach ($CurrentFullName in $FullName)
{
$CurrentFullName = Get-Item -Path $CurrentFullName
#Getting the fullname of the processed CSV File
$SourceCSVFullName = $CurrentFullName.FullName
#Getting the name of the processed CSV File
$SourceCSVName = $CurrentFullName.Name
#Getting the final XLSX full name (same directory and name that the CSV File)
$TargetXLSXDocumentFullName = $SourceCSVFullName -replace '\.csv$', '.xlsx'
#Getting the write time of the CSV File
$SourceCSVTimeWritten = $(Get-Item -Path $SourceCSVFullName).LastWriteTime
#Getting if -Force was specified or if the XLSX doesn't exist or if the CSV File is newer that a previously generated XLSX Document.
If (($Force) -or (!(Test-Path -Path $TargetXLSXDocumentFullName)) -or ($(Get-Item -Path $TargetXLSXDocumentFullName).LastWriteTime -lt $SourceCSVTimeWritten))
{
#If -Force was specified
If ($Force)
{
Write-Verbose -Message ('Forcing {0} ...' -f $SourceCSVFullName)
}
#If the XLSX file doesn't exist
ElseIf (!(Test-Path -Path $TargetXLSXDocumentFullName))
{
Write-Verbose -Message ('Processing {0} ...' -f $SourceCSVFullName)
}
#If the CSV file is newer that a previously generated XLSX Document.
ElseIf ($(Get-Item -Path $TargetXLSXDocumentFullName).LastWriteTime -lt $SourceCSVTimeWritten)
{
Write-Verbose -Message ('Updating {0} ...' -f $SourceCSVFullName)
}
#Risk Mitigation : support of -whatif and -confirm
If ($pscmdlet.ShouldProcess($SourceCSVFullName, 'Converting'))
{
Write-Verbose -Message 'Opening the CSV File ...'
$WorkBook = $Excel.Workbooks.Open($SourceCSVFullName)
#Adding a worksheet
$WorkSheet = $WorkBook.worksheets.Item(1)
$Range = $WorkSheet.UsedRange
$null = $Range.EntireColumn.AutoFit()
#Getting teh first row
$firstRow = $WorkSheet.cells.item(1,1).entireRow
#Make Headings Bold
$firstRow.Font.Bold = $true
#Freezing header row
$Excel.ActiveWindow.SplitColumn = 0
$Excel.ActiveWindow.SplitRow = 1
$Excel.ActiveWindow.FreezePanes = $true
#Add Data Filters to Heading Row
$null = $firstRow.AutoFilter()
#Setting header row gray
$firstRow.Interior.ColorIndex = $Gray
Write-Verbose -Message 'Saving the XLSX document ...'
$WorkBook.SaveAs($TargetXLSXDocumentFullName, $xlWorkbookDefault)
Write-Verbose -Message 'Closing the CSV File ...'
$WorkBook.Close()
Write-Verbose -Message 'Releasing firstRow ...'
Remove-Ref -ref ($firstRow)
Write-Verbose -Message 'Releasing Range ...'
Remove-Ref -ref ($Range)
Write-Verbose -Message 'Releasing WorkSheet ...'
Remove-Ref -ref ($WorkSheet)
Write-Verbose -Message 'Releasing WorkBook ...'
Remove-Ref -ref ($WorkBook)
}
# Write-Host -Object "File saved to:" $TargetXLSXDocumentFullName
Write-Host -Object ("The XLSX file is available at : '{0}'" -f $TargetXLSXDocumentFullName)
}
else
{
Write-Verbose -Message ("Skipping '{0}' because it is up-to-date`r`nUse -Force to overwrite previously generated XLSX file" -f $SourceCSVName)
}
}
}
end
{
Write-Verbose -Message 'Exiting the Excel application ...'
$null = $Excel.Quit()
Remove-Ref -ref ($Excel)
}
}
#endregion
Clear-Host
# To get the directory of this script
$CurrentDir = Split-Path -Path $MyInvocation.MyCommand.Path -Parent
Get-ChildItem -Path $CurrentDir -Filter '*.csv' -Recurse | ConvertTo-XLSX -Force -Verbose
# ConvertTo-XLSX -FullName "$CurrentDir\processes.csv","CurrentDir\services.csv" -Force
# Get-ChildItem -Path $CurrentDir -Filter "*.csv" -recurse | ConvertTo-XLSX -Verbose
# Get-ChildItem -Path $CurrentDir -Filter "*.csv" -recurse | ConvertTo-XLSX -WhatIf -Verbose
# Get-ChildItem -Path $CurrentDir -Filter "*.csv" -recurse | ConvertTo-XLSX -Visible
The script file is also available at the TechNet Script Center repository, at: https://gallery.technet.microsoft.com/Convert-multiple-CSV-files-0581efca?redir=0
Laurent.