Created
March 17, 2021 10:00
-
-
Save daniel0x00/06418be6b3e08464cef7279ba0a7e78c to your computer and use it in GitHub Desktop.
PowerShell function to convert Excel .xlsb files to .csv
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
## | |
# Convert .xlsb to .csv - PowerShell function. | |
## | |
## USAGE: | |
# Open Windows PowerShell, change your paths on below one liner and paste it in Windows PowerShell: | |
# Set-ExecutionPolicy Bypass -Scope Process -Force; $username = $env:username; . "C:\Users\$username\Documents\ConvertFrom-XLSB.ps1"; Get-ChildItem "C:\Users\$username\Desktop\*xlsb' | Sort-Object LastWriteTime -Descending | Select-Object -First 1 -ExpandProperty Fullname | ConvertFrom-XLSB -OutputPath 'C:\Users\$username\Desktop\' | |
## | |
function ConvertFrom-XLSB { | |
param( | |
[Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$true)] | |
[Alias('filename','file','fullname')] | |
[string] $Path, | |
[Parameter(Mandatory=$false, ValueFromPipeline=$false)] | |
[string] $OutputPath=((Get-Location).Path) | |
) | |
Add-Type -AssemblyName Microsoft.Office.Interop.Excel | |
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV | |
$excel = new-object -ComObject "Excel.Application" | |
$excel.DisplayAlerts = $false | |
$excel.Visible = $false | |
$Workbook = $excel.Workbooks.Open($Path) | |
foreach($Worksheet in $Workbook.Worksheets) { | |
# Filename: | |
$Filename = [system.io.path]::combine($OutputPath,[string]::concat($Worksheet.Name,'.csv')) | |
Write-Verbose "Output filename: $Filename" | |
# Delete file if exists: | |
if (Test-Path -Path $Filename) { Remove-item -Path $Filename -ErrorAction Stop } | |
# Save file as CSV: | |
$Worksheet.SaveAs($Filename, $xlFixedFormat) | Out-Null | |
} | |
$Workbook.close($False) | |
$excel.Quit() | |
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment