excel - PowerShell Reverse CSV-data -
on web found function import data excel sheet in powershell. i've tried enhance bit worked out quite well. however, last hurdle need take reverse data. not data in excel sheet aligned vertically, horizontally. think might become more clear example.
example of 'test.xlsx':
below find parameters: header 1 | text 1 | text 11 header 2 | text 2 | text 22 header 3 | text 3 | text 33 current wrong result:
header 1 text 1 text 11 -------- ------ ------- header 2 text 2 text 22 header 3 text 3 text 33 desired result:
header 1 header 2 header 3 -------- -------- -------- text 1 text 2 text 3 text 11 text 22 text 33 so can see, in way or need able reverse data columns , headers correct. nice if added function in way of switch. when it's not needed, it's still able import rows , columns in old way.
function:
function import-excel { param ( [parameter(mandatory=$true,position=0)] [validatescript({test-path $_ -pathtype leaf })] [string]$filename, [parameter(mandatory=$true,position=1)] [string]$worksheetname, [parameter(mandatory=$false,position=2)] [int]$skiplines ) $csvfile = join-path $env:temp ("{0}.csv" -f (get-item -path $filename).basename) if (test-path -path $csvfile) { remove-item -path $csvfile } function findsheet([object]$workbook, [string]$name) { $sheetnumber = 0 ($i=1; $i -le $workbook.sheets.count; $i++) { if ($name -eq $workbook.sheets.item($i).name) { $sheetnumber = $i; break } } return $sheetnumber } function setactivesheet([object]$workbook, [string]$name) { if (!$name) { return } $sheetnumber = findsheet $workbook $name if ($sheetnumber -gt 0) { $workbook.worksheets.item($sheetnumber).activate() } return ($sheetnumber -gt 0) } # convert excel file csv file $xlcsvtype = 6 # see: http://msdn.microsoft.com/en-us/library/bb241279.aspx $excelobject = new-object -comobject excel.application $excelobject.visible = $false $workbookobject = $excelobject.workbooks.open($filename) # check if worksheet exists foreach ($sheet in $workbookobject.worksheets) { if ($sheet.name -eq $worksheetname) { $sheetavailable = $true } } if (-not $sheetavailable) { $workbookobject.close() $excelobject.quit() throw "import-excel: worksheet '$worksheetname' not found in workbook '$filename'" } setactivesheet $workbookobject $worksheetname | out-null $workbookobject.saveas($csvfile,$xlcsvtype) $workbookobject.saved = $true $workbookobject.close() # cleanup [system.runtime.interopservices.marshal]::releasecomobject($workbookobject) | out-null $excelobject.quit() [system.runtime.interopservices.marshal]::releasecomobject($excelobject) | out-null [system.gc]::collect() [system.gc]::waitforpendingfinalizers() # import , return data $result = get-content -path $csvfile | select-object -skip $skiplines | {$_ -notmatch "^[,]+$"} | convertfrom-csv write-output $result } import-excel c:\test.xlsx -worksheetname 'parameters' -skiplines 1 as guys, thank help.
solution themadtechnician:
function import-excel { [cmdletbinding(supportsshouldprocess=$true)] param ( [parameter(mandatory=$true,position=0)] [validatescript({test-path $_ -pathtype leaf })] [string]$filename, [parameter(mandatory=$true,position=1)] [string]$worksheetname, [parameter(mandatory=$false,position=2)] [int]$skiplines=0, [switch]$reverse ) begin { $csvfile = join-path $env:temp ("{0}.csv" -f (get-item -path $filename).basename) if (test-path -path $csvfile) { remove-item -path $csvfile } } process { # convert excel file csv file $xlcsvtype = 6 # see: http://msdn.microsoft.com/en-us/library/bb241279.aspx $excelobject = new-object -comobject excel.application $excelobject.visible = $false $workbookobject = $excelobject.workbooks.open($filename) # check if worksheet exists $ws1 = $workbookobject.worksheets | {$_.name -eq $worksheetname} if ($ws1 -ne $null) { $ws1.activate() } else { $workbookobject.close($false) $excelobject.quit() throw "import-excel: worksheet '$worksheetname' not found in workbook '$filename'" } if($reverse){ $usedrange = $workbookobject.activesheet.usedrange $ws = $workbookobject.activesheet # remove first lines ($i = 1; $i -le $skiplines; $i++) { [void]$ws.cells.item(1, 1).entirerow.delete() } # remove empty lines $lastcell = $usedrange.specialcells(11) $row = $lastcell.row ($i = 1; $i -le $row; $i++) { if ($ws.cells.item($i, 1).value() -eq $null) { $range = $ws.cells.item($i, 1).entirerow $range.delete() | out-null } } $usedrange.copy() | out-null $newworkbookobject = $excelobject.workbooks.add() $newworkbookobject.activesheet.range("a1").pastespecial(12,-4142,$false,$true)| out-null $newworkbookobject.saveas($csvfile,$xlcsvtype) $newworkbookobject.saved = $true $newworkbookobject.close() $workbookobject.close($false) # pause let csv written while(!(test-path $csvfile)){start-sleep -milliseconds 50} # import , return data $result = get-content -path $csvfile | convertfrom-csv write-output $result } else{ $workbookobject.saveas($csvfile,$xlcsvtype) $workbookobject.saved = $true $workbookobject.close() # pause let csv written while(!(test-path $csvfile)){start-sleep -milliseconds 50} # import , return data $result = get-content -path $csvfile | select-object -skip $skiplines | {$_ -notmatch "^[,]+$"} | convertfrom-csv write-output $result } # cleanup [system.runtime.interopservices.marshal]::releasecomobject($workbookobject) | out-null if($reverse){[system.runtime.interopservices.marshal]::releasecomobject($newworkbookobject) | out-null} $excelobject.quit() [system.runtime.interopservices.marshal]::releasecomobject($excelobject) | out-null [system.gc]::collect() [system.gc]::waitforpendingfinalizers() } } import-excel c:\test.xlsx -worksheetname 'parameters' -reverse -skiplines 1
i've found solution problem. add parameter section [switch]$reverse , replace end of code this:
# import , return data $result = get-content -path $csvfile | select-object -skip $skiplines | {$_ -notmatch "^[,]+$"} if ($reverse) { $result | foreach-object {$header += "$($_.split(',')[0]),"; $content += "$($_.split(',')[1]),"} $result = "$header`n$content" } $obj = $result | convertfrom-csv write-output $obj
Comments
Post a Comment