Author: ryan

# powershell script: 06-27-16## 1) gets list of all xlsb files in directory# 2) goes through each and saves worksheets as separate csv##If you want to search through subdirectories also, add ” -Recurse” before “| Foreach-Object”$scriptpath = “B:”#path to searchAdd-Type -AssemblyName Microsoft.Office.Interop.Excel$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV#needed to lookup$excel = new-object -ComObject “Excel.Application”;$excel.DisplayAlerts=$false;$excel.Visible =$false; $csvloc = “b:csv”#output directory Get-ChildItem -LiteralPath b: -Filter *.xlsb | ForEach-Object {     $wb = $excel.Workbooks.Open($_.FullName)    $lastmod = $_.LastWriteTime.ToString(‘yyyy-MM-ddThh-mmss’)    $wbn = $wb.name      foreach($ws in $wb.Worksheets) {                                            $n = $lastmod + “_” + $wbn + “_” + $ws.Name            $ws.SaveAs($csvLoc + $n + “.csv”, $xlFixedFormat);       }     $wb.close($False)    #End file-specific code }     $excel.Quit();[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel);

Problem: Some of my Excel files are XLSB and therefore don’t play well others (aka Python)Solution: Use PowerShell to Loop and Convert Excel Files to CSV