Skip to content

Instantly share code, notes, and snippets.

@daniel0x00
Created March 17, 2021 10:00
Show Gist options
  • Save daniel0x00/06418be6b3e08464cef7279ba0a7e78c to your computer and use it in GitHub Desktop.
Save daniel0x00/06418be6b3e08464cef7279ba0a7e78c to your computer and use it in GitHub Desktop.
PowerShell function to convert Excel .xlsb files to .csv
##
# 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