# andyb stackoverflow# http://stackoverflow.com/questions/21979360/powershell-how-to-create-directories-based-on-last-modify-date-of-files # script takes files in folderRoot and moves then to folders at the PATH:YYYYMMDDlevel.file $folderRoot=“B:EXCEL”$days = 1 dir $folderRoot|?{(!($_.PsIsContainer)) -and ((get-date) – $_.lastwritetime).totaldays -gt $days }|%{ [string]$year=$([string]$_.lastwritetime.year) [string]$month=$_.lastwritetime.month [string]$day=$_.lastwritetime.day $dir=$folderRoot+$year+“”+$month+“”+$day if(!(test-path $dir)){ new-item -type container $dir } Write-output $_ move-item $_.fullname $dir}
Category: PowerShell
https://sourceforge.net/projects/conemu/files/latest/download lots of settings multi-tabs looks good runs everything so far
# 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