any excel-compatible file to csv with powershell
at this point i spend more time working out an automated solution to small problems than it would take to just do it manually. relevant xkcd.
$Path = 'M:' $SearchPath = Join-Path $Path '*' $SavePath = Join-Path $Path 'xl-to-csv-files\' $Files = Get-ChildItem -Path $SearchPath -Include '*.xlsx' Function ConvertToCSV { Param( [parameter(ValueFromPipeline=$True)]$FileName ) Process{ If (!(Test-Path -Path $SavePath)) { New-Item -Path $Path -Name 'xl-to-csv-files' -ItemType Directory } $NewFilename = "$($SavePath)$($Filename.Basename).csv" Write-Output $NewFilename $Excel = New-Object -ComObject Excel.Application $Excel.visible = $False $Excel.displayAlerts = $False $Workbook = $Excel.Workbooks.Open($Filename) Try { $Worksheet = $Excel.Worksheets.item("_Shellum_T1") $Worksheet.Activate() } Catch [System.Management.Automation.GetValueInvocationException] { $Worksheet = $Excel.Worksheets.item("_$($Filename.Basename)_T1") $Worksheet.Activate() } Catch { Write-Host "Oh No!" [System.Media.SystemSounds]::Beep.Play(); } $Workbook.SaveAs($NewFilename,6) # 6 represents the Excel Constant for the .csv Filetype $Excel.quit() } } $Files | ConvertToCSV [System.Media.SystemSounds]::Exclamation.Play();
