Faster way of writing out to excel in powershell -


i have powershell script reads in csv , appends excel worksheet. runs quite painfully slow. have searched , seems limitation of using com write excel. suggestions have found speed write out entire ranges instead of cell cell. need format cells , doesn't seem possible when writing out ranges. suggestions on how optimize below code welcome. not have option use db.

$csvpath = "z:\script_test\" $outputfile = "z:\script_test\exceltest.xlsx"  foreach($csvfile in get-childitem $csvpath -filter "stats*.txt" ){ $csvfilepath = [io.path]::combine($csvpath, $csvfile) $rawcsvdata = import-csv -delimiter ";" -path $csvfilepath   $excel = new-object -comobject excel.application  $excel.visible = $false  $workbook = $excel.workbooks.open($outputfile) $excelworksheet = $excel.worksheets.item("2016") $excelworksheet.activate()  $excel.cells.item(1,1) = “pex”  $excel.cells.item(1,2) = “run date”  $excel.cells.item(1,3) = “execs”  $excel.cells.item(1,4) = “cpu av.”  $excel.cells.item(1,5) = “cpu hi.”  $excel.cells.item(1,6) = “cpu tot.”  $excel.cells.item(1,7) = “#value!”  $excel.cells.item(1,8) = “elaps av.”  $excel.cells.item(1,9) = “elapse hi.”  $excel.cells.item(1,10) = “elapse tot”   $i = $excelworksheet.usedrange.rows.count + 1  foreach($rawcsv in $rawcsvdata)   {   $rundate = $rawcsv.“run date     ”.replace("--1","")  $execs = $rawcsv."execs ".replace("?","")  $cpuav = $rawcsv.“cpu av.   ”.replace("-",":")  $cpuhi = $rawcsv.“cpu hi.   ”.replace("-",":")    $cputot = $rawcsv.“cpu tot.  ”.replace("-",":")   $elapseav = $rawcsv.“elapse av.”.replace("-",":")  $elapsehi = $rawcsv.“elapse hi.”.replace("-",":")   $elpasetot = $rawcsv.“elpase tot”.replace("-",":")  write-output("working" + $i) $excel.cells.item($i,1) = $rawcsv."pex "  $excel.cells.item($i,2) = $rundate     $excel.cells.item($i,2).numberformat = “yyyy/mm/dd” $excel.cells.item($i,3) = $execs  $excel.cells.item($i,4) = $cpuav   $excel.cells.item($i,4).numberformat = “hh:mm:ss.00” $excel.cells.item($i,5) = $cpuhi    $excel.cells.item($i,5).numberformat = “hh:mm:ss.00” $excel.cells.item($i,6) = $cputot  $excel.cells.item($i,6).numberformat = “hh:mm:ss.00” $excel.cells.item($i,7) = “=((hour(f"+$i+")*3600)+(minute(f"+$i+")*60)+second(f"+$i+"))*21”  $excel.cells.item($i,8) = $elapseav $excel.cells.item($i,8).numberformat = “hh:mm:ss.00” $excel.cells.item($i,9) = $elapsehi  $excel.cells.item($i,9).numberformat = “hh:mm:ss.00” $excel.cells.item($i,10) = $elpasetot $excel.cells.item($i,10).numberformat = “hh:mm:ss.00”  $i++ } $excelworksheet.usedrange.removeduplicates() #$workbook.saveas($outputfile)  $workbook.save() $excel.quit()  remove-variable -name excel  [gc]::collect()  [gc]::waitforpendingfinalizers()  move-item -path $csvfilepath -destination "z:\script_test\used files" }

the slow part com object performance. won't able speed enough if keep working com object, sadly.

back in days had project related excel , found great module uses external dll, can take on it: psexcel

best part not need have excel installed, com object.


-

Comments

Post a Comment

Popular posts from this blog

serialization - Convert Any type in scala to Array[Byte] and back -

i have following question: i have variable value in program declared value. i want convert value byte array.. how can serialize byte array , back? found examples related other types such double or int, not any. this should need. it's pretty similar how 1 in java. import java.io.{bytearrayinputstream, bytearrayoutputstream, objectinputstream, objectoutputstream} object serialization extends app { def serialise(value: any): array[byte] = { val stream: bytearrayoutputstream = new bytearrayoutputstream() val oos = new objectoutputstream(stream) oos.writeobject(value) oos.close stream.tobytearray } def deserialise(bytes: array[byte]): = { val ois = new objectinputstream(new bytearrayinputstream(bytes)) val value = ois.readobject ois.close value } println(deserialise(serialise("my test"))) println(deserialise(serialise(list(1)))) println(deserialise(serialise(map(1 -> 2)))) println(deserialise(seri
Read more

matplotlib support failed in PyCharm on OSX -

any idea how fix ? how can debug might problem ? not seem : https://youtrack.jetbrains.com/issue/py-15520 i in console: python 2.6.9 (unknown, jul 14 2015, 19:46:31) [gcc 4.2.1 compatible apple llvm 6.0 (clang-600.0.39)] on darwin >>> import matplotlib.pyplot plt matplotlib support failed the backend : >>> import matplotlib >>> print(matplotlib.rcparams['backend']) macosx
Read more

python - Matplotlib: TypeError: 'AxesSubplot' object is not callable -

i new programming please pardon me if rookie mistake. i writing python script have defined functions create subplots of distributions. i have slider widget end user change value of variables(mu , sigma) , see effect on distribution. here's code import numpy np print np.__version__ import scipy print scipy.__version__ scipy.stats import norm, lognorm, uniform import matplotlib print matplotlib.__version__ import matplotlib.pyplot plt matplotlib.widgets import slider, button, radiobuttons, checkbuttons #####calculating mean , standard deviation##### global mu_a1 mu_a1 = 1 global mu_b1 mu_b1 = 10 global mu_c1 mu_c1 = -13 global sigma_a1 sigma_a1 = 0.14 global sigma_b1 sigma_b1 = 1.16 global sigma_c1 sigma_c1 = 2.87 mu_x01 = -11 sigma_x01 = 1.9 #####_____##### #####generating random data##### a1 = 0.75*mu_a1 + (1.25 - 0.75)*sigma_a1*np.random.sample(10000) b1 = 8*mu_b1 + (12 - 8)*sigma_b1*np.random.sample(10000) c1 = -12*mu_c1 + 2*sigma_c1*np.random.sample(10000)
Read more