Powershell Transformation - Entire table?

1

I’d like to do a Powershell Transformation using multiple columns. The only option for specifying Input to Powershell is a single column. Is the recommended way to concatenate multiple values into a single column? It would be nice to be able to specify multiple columns or the entire table in the future.

Sidebar - the powershell transformation seems very powerful and is quite fast in my early testing. Thank you for adding it

2

Hi Joe,

Thank you for the suggestion.

Yes, at this point the only option for multiple columns is concatenating them into one before sending to PowerShell. Here is a sequence of transformations that concatenates all columns in a table into one column:

concatenate all columns.morph (2.4 KB)

It's a derived table. Just point it to a table which columns need to be concatenated.

I'm curious what statement do you use in PowerShell to split a row back into separate values? How do you deal with values that contain line breaks (if it happens)?

You're welcome! The PowerShell transformation appeared to a big extent due to your suggestions on the forum.

3

Thanks Dmitry! I use the Split function

Here’s a portion of what I’m working on:

$data = @($Input) 
$ct = $data.Count
$arr = New-Object string[] $ct
for($i = 0; $i -lt $ct; $i++)
{
    $row = $data[$i].Split('|')
    $arr[$i] = $row[0]
}
$arr

I will post a more complete example later

I’m not certain it will be better in Powershell yet vs a series of EM transformations. I need to cancel out records that offset each other

4

We decided to add full table input for PowerShell in v3.8. The table will be provided as a sequence of arrays, where each array is 1 table row.

There are some ideas for capturing output too. Two new modes are possible:

  1. Capturing object properties by name. In this mode the user specifies in the transformation the names of object properties to capture. Each property captured into a column named after it.
  2. Capturing a jagged array (array of arrays). This is basically the same format as for the full table input.

Thoughts?

5

Feel free to open a new topic on this and attach an example.

6

That’s excellent news! For the output, jagged arrays seems preferable to have parity with the input and it may perform better.