I gave a talk at STL Python last week about learning Python from an Excel User point of view. Check out the GitHub repo for slides and examples.
Category: Excel
#! py35# open file, process, and save output to new file stdin = r’C:projectstxt.txt’stdout = r’C:projectstext.csv’ with open(stdin, ‘r’, encoding=’utf-8′) as f: # open input file lines = f.readlines() # iterate one line at time for line in lines: # for each line l = line.split(‘ ‘, 1)[1] # remove left of first space with open(stdout, ‘a’) as fout: # open output file fout.write(l) …
# 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