# 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);
Category: CSV
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