CODEWITHSUNDEEP
excelpowershell
knb
knb

Reputation: 9295

PowerShell - paste data into Excel

Today I have just thrown together this PowerShell script which

The last step is where I am stuck. Currently my code puts a few lines of data into a range in the worksheet, in the form of unrolled/transposed "key: value" entries, resulting in a horizontal data layout. The same range of data is always overwritten.

I want data in the form of a vertical layout, i.e., data in columns, just the same way as if the CSV file was imported with the import-file-wizard of MS Excel.

Is there a simpler way to do it than below?

I admit, some of the PowerShell features are pasted in here in a cargo-cult mode of programming. Please note that I have no PowerShell experience whatsoever. I did some batchfile, VBScript, and VBA coding a few years back. So, other criticisms are also welcome.

PARAM (
    [Parameter(ValueFromPipeline = $true)]
    $infile = ".\04-2011\110404-13.txt"
)

PROCESS {
    echo " $infile"
    Write-Host "Num Args:" $args.Length;

    $xl = New-Object -comobject Excel.Application;
    $xl.Visible = $true;

    $Workbook = $xl.Workbooks.Add();

    $content = Import-Csv -delimiter "`t" $infile;
    $ports = $content | Select-Object Port# | Sort-Object Port# -Unique -Descending;
    $ports | ForEach-Object {
        $p = $_;
        Write-Host $p.{Port#};

        $Worksheet = $Workbook.Worksheets.Add();
        $workSheet.Name = [string]::Format("{0} {1}", "PortNo", $p.{Port#});
        $filtered = $content | Where-Object {$_.{Port#} -eq $p.{Port#} };
        $filtered | ForEach-Object {
            Write-Host $_.{ObsDateTime}, $_.{Port#}
        }
        $filtered | clip.exe;
        $range = $Workbook.ActiveSheet.Range("a2", "a$($filtered.count)");
        $Workbook.ActiveSheet.Paste($range, $false);
    }
    $xl.Quit()
}

Data Output Example

Wrong

Port#       : 1
Obs#        : 1
Exp_Flux    : 0,99
IV Cdry     : 406.96
IV Tcham    : 16.19
IV Pressure : 100.7
IV H2O      : 9.748
IV V3       : 11.395
IV V4       : 0.759
IV RH       : 53.12

Right

Port#    Obs#    Exp_Flux    IV Cdry    IV Tcham    IV Pressure    IV H2O    IV V3    IV V4    IV RH
1      1    0,99    406.96    16.19    100.7    9.748    11.395    0.759    53.12

Upvotes: 2

Views: 10051

Answers (3)

knb
knb

Reputation: 9295

I used a modified Export-Xls function, a bit different as User empo suggested. This is my call to it

Export-Xls  $filtered -Path $outfile -WorksheetName "$wn" -SheetPosition "end"  | Out-Null #  -SheetPosition "end";

However, the current release of Export-Xls re-orders the columns of the in-memory representation of the csv-text -file. I want the data columns of the text file in their original order, so I had to hack and simplify the original code as follows:

    function Add-Array2Clipboard {
        param (
            [PSObject[]]$ConvertObject,
            [switch]$Header
        )
        process{
            $array = @();
            $line =""
            if ($Header) {

                $line = @()
                $row = $ConvertObject | Select -First 1
                $row.psobject.properties | Foreach {$line += "$($_.Name)" }
                $array += [String]::Join("`t", $line)


            }
            else {
                foreach($row in $ConvertObject){
                    $line =""
                    $vals = @()
                    $row.psobject.properties | Foreach {$vals +=  $_.Value}                      
                    $array += [String]::Join("`t", $vals)

                }

            }
            $array | clip.exe; 
        }

    }

Upvotes: 0

Emiliano Poggi
Emiliano Poggi

Reputation: 24826

Try Export-Xls, it looks very nice. Never had the chance to use it, but (virtually) knowing the person who worked on it, I'm sure you will be very happy to use it. If you'll go with it, please provide a feedback here will be appreciated.

POSSIBLE WORKAROUND FOR UNORDERED PROPERTIES IN Export-Xls

The function Add-Array2Clipboard could be changed so that it accepts a new input parameter: an array providing the name of the properties ordered as required.

Then the you can change the section where get-member is used. Silly example:

"z", "a", "c" | %{ get-member -name $_ -inputobject $thecurrentobject }

This is just an example on how you can achieve ordered properties from get-member.

Upvotes: 2

Torbjörn Bergstedt
Torbjörn Bergstedt

Reputation: 3429

I've used the $Workbook.ActiveSheet.Cells.Item($row, $col).Value2 function to more be able to pinpoint more precisely where to put the data when exporting to Excel.

Something like

$row = 1
Get-Content $file | Foreach-Object {
   $cols = $_.split("`t")
   for ($i = 0; $i < $cols.count; $i++)
   {
      $Workbook.ActiveSheet.Cells.Item($row, $i+1).Value2 = $cols[$i]
   }
   $row++
}

Warning: dry-coded! You'll probably need some try..catch as well.

Upvotes: 2

Related Questions