PowerShell - paste data into Excel

Question

Today I have just thrown together this PowerShell script which

• takes a tab-delimited text file,

• reads it into memory,

• makes a variable number of filter queries based on distinct values of a certain column

• creates a new empty Excel workbook

• adds each of the subsets of filtered data to a new Excel worksheet

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

Answer 1

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.

Answer 2

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.

Answer 3

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; 
        }

    }