Delete extra rows in an excel file with powershell? -
i have been tasked automate part of logging process on spla server owned company. task date, archive, , remove old files, move onto generating report emailed department. task supposed ran @ end of every week.
i figured powershell best option complete task. first time working powershell had bit of learning do.
my question:
is possible loop through excel worksheet , delete unused rows using script?
my condition if there 2 empty rows -> delete 1 row , keep going
i taking info log , splitting csv converting csv excel formatting.
sample of excel spreadsheet, others vary in excess rows between information
get-childitem c:\scriptsdirectory1\*.log | foreach{ $input = get-content $_.fullname #initialize input $a = get-date #save current date (for if/else wrapper) #=============================================# # file name changer # #=============================================# $x = $_.lastwritetime.toshortdatestring() #save temp variable lastwritetime , send string $new_folder_name = get-date $x -format yyyy.mm.dd #create new folder contains string information $des_path = "c:\archive\archivedlogs\$new_folder_name" #send new folder archive directory #=============================================# $data = $input[1..($input.length - 1)] #initialize array , set length of input file. $maxlength = 0 $objects = foreach($record in $data) { #loop through each object within array $split = $record -split ": " #split objects within array @ ": " string if($split.length -gt $maxlength){ $maxlength = $split.length } $properties = @{} for($i=0; $i -lt $split.length; $i++) { #adds split information strings array $properties.add([string]($i+1),$split[$i]) } new-object -typename psobject -property $properties } $objects | format-table $headers = [string[]](1..$maxlength) $objects | select-object $headers | export-csv -notypeinformation -path "c:\archive\csvreports\$new_folder_name.csv"#export csv path using new folder name prevent overwrite if (test-path $des_path){ #test if path exists, , fill directory file archived move-item $_.fullname $des_path } else { new-item -itemtype directory -path $des_path move-item $_.fullname $des_path } } #end of parser #===============================================================================# #======================================#========================================# #===============================================================================# # file archiver , zipper (after parse/csv) # #===============================================================================# #======================================#========================================# #===============================================================================# $files = get-childitem c:\archive\archivedlogs #fill $files variable new files in archive directory #********************************# #loop through , compress/delete# #********************************# foreach ($file in $files) { write-zip $file "c:\archive\archivedlogs\$file.zip" -level 9 #write compressed file } #end of archiver remove-item c:\archive\archivedlogs\* -exclude *.zip -recurse #remove un-needed files within archive folder #run formatting , conversion script csv-to-xlsx #c:\scriptsdirectory1\script\testrunner1.ps1 #<---can ran using invoke call #===============================================================================# #======================================#========================================# #===============================================================================# # csv xlsx format/conversion # #===============================================================================# #======================================#========================================# #===============================================================================# get-childitem c:\archive\csvreports | foreach{ $excel_file_path = $_.fullname #create file path variable initialize formating $excel = new-object -comobject excel.application #start new excel application $excel.visible = $true $excel.displayalerts=$false $excel_workbook = $excel.workbooks.open($excel_file_path) #create workbook variable , open workbook in path $filename = $_.basename #save base file name of current value $excel.activesheet.listobjects.add(1,$excel_workbook.activesheet.usedrange,0,1) $excel_workbook.activesheet.usedrange.entirecolumn.autofit() $spla1wksht = $excel_workbook.worksheets.item(1) #create new sheet (spla1wksht) #*******************************************************# # formating title cell # #*******************************************************# $spla1wksht.name = 'spla info report' #change worksheet name $spla1wksht.cells.item(1,1) = $filename #title (date of log) in cell a1 $spla1wksht.cells.item(1,2) = 'spla weekly report' #title excel reports $spla1wksht.cells.item(1.2).font.size = 18 $spla1wksht.cells.item(1.2).font.bold=$true $spla1wksht.cells.item(1.2).font.name="cambria" $spla1wksht.cells.item(1.2).font.themefont = 1 $spla1wksht.cells.item(1.2).font.themecolor = 5 $spla1wksht.cells.item(1.2).font.color = 8210719 #*******************************************************# #************************************# # adjust , merge cell b1 # #************************************# $range = $spla1wksht.range("b1","h2") $range.style = 'title' $range = $spla1wksht.range("b1","g2") $range.verticalalignment = -4108 #center align vertically (value -4108 center) #************************************# #***********************************************************************# # horizontal centering cells # #***********************************************************************# $columnrange = $spla1wksht.range("a1","a500").horizontalalignment =-4108 #center cells in range -4108 $columnrange = $spla1wksht.range("b1","b500").horizontalalignment =-4108 #**********************************************# # delete blank rows inneffective- logs have different #data end different amount of rows , offsets deletion # # method deletes first row #moves onto #**********************************************# # next-in-line blank lines , deletes 1 #$spla1wksht.cells.item(2,1).entirerow.delete() # # line until blank spots in perfect format # #$spla1wksht.cells.item(4,1).entirerow.delete() # #$spla1wksht.cells.item(4,1).entirerow.delete() # #$spla1wksht.cells.item(4,1).entirerow.delete() # #$spla1wksht.cells.item(4,1).entirerow.delete() # # #$spla1wksht.cells.item(19,1).entirerow.delete()# #$spla1wksht.cells.item(19,1).entirerow.delete()# #$spla1wksht.cells.item(19,1).entirerow.delete()# #$spla1wksht.cells.item(19,1).entirerow.delete()# # #$spla1wksht.cells.item(25,1).entirerow.delete()# #$spla1wksht.cells.item(25,1).entirerow.delete()# #$spla1wksht.cells.item(25,1).entirerow.delete()# #$spla1wksht.cells.item(25,1).entirerow.delete()# # #$spla1wksht.cells.item(33,1).entirerow.delete()# #$spla1wksht.cells.item(33,1).entirerow.delete()# #$spla1wksht.cells.item(33,1).entirerow.delete()# #$spla1wksht.cells.item(33,1).entirerow.delete()# #**********************************************# #*****************************************************************# # final export csv-to-xlsx file # #*****************************************************************# $excel_workbook.saveas("c:\archive\excelreports\$filename.xlsx",51) #save file in proper location $excel_workbook.saved = $true $excel.quit() # find way optimize process #potential optimization places: # 1.) don't open , close excel file , instead write changes , save # 2.) change way empty rows formatted instead of seperate calls each time } #end of format/converter #******end******# #---------------#--#--------------# #---------------------------------# # add script # #---------------------------------# #---------------#--#--------------# # -[/] <-complete -[] <- incomplete # -[] archive or delete csv files # -[] add if/else statement checks if files >7 days old # -[] compile weekender report indicates spla programs changed keep compliance # -[] filter spla files (need list) # -[] loop through csv/excel file , delete empty rows the following code worked run through program:
for($i = 350 ; $i -ge 0 ; $i--) { if ($spla1wksht.cells.item($i, 1).text-eq "") { $range = $spla1wksht.cells.item($i, 1).entirerow [void]$range.delete() echo $i } if ($spla1wksht.cells.item($i, 2).text-eq "") { $range = $spla1wksht.cells.item($i, 2).entirerow [void]$range.delete() echo $i } if($i -eq 2){ break;} }
this should relatively straight forward
$file = c:\path\to\file.csv $csv = import-csv $file foreach($row in $csv) { # logic delete row # $csv array, can make row = null delete } # spit out updated excel sheet export-csv | $csv -notypeinformation
Comments
Post a Comment