How to add an array as a column to csv file using powershell?

Question

I'm kind of new to PowerShell. Currently I'm trying to get columns from abd excel files and stock them into a new CSV file.

I managed to get the columns I needed from the excel using this:

$emails=$file.Worksheets['Form1'].UsedRange.Rows.Columns[4].Value2 
$Response=$file.Worksheets['Form1'].UsedRange.Rows.Columns[8].Value2

Where $emails $Response are System.array containing

    email1
...
    email 20

and

Resp1 ...
Resp20

However I didn't mange to get this format :

Email , Response
----------------
email1, Resp1
Email2, Resp2

Instead by applying [pscustomobject]@{'emails' = $emails; 'Response' = $Response} I get sth like this:

emails                                          Response
---                                                ---                                                                                                                                          
{Email1, email2 ... email20}               {Resp1..Resp20}

Can anyone one help me with this ? Thank you very much !!

Using this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [Is there a PowerShell equivalent of `paste` (i.e., horizontal file concatenation)?](https://stackoverflow.com/a/68070763/1701026)): `$emails |Join $Response -Name Email, Response` — iRon, Jun 25 '21 at 12:56

score 0 · Answer 1 · answered Jun 25 '21 at 10:33

0

You are not having Array. It is a runtime object.

you can try below code to generate csv file.

$emails = {'test.test.com', 'test1.test.com'}
$responses = {'testresponse', 'test1response'}
$emailarray = $emails.ToString().Split(",")
$emailarray
$collection = @()
for($i = 0; $i -lt $emailarray.Length ; $i++)
{
$item = @{}
$item.email = $emails.ToString().Split(",")[$i]
$item.response = $responses.ToString().Split(",")[$i]
$collection += New-Object psobject -Property $item
}
$collection  | Export-Csv -LiteralPath $yourDestinationpath -NoTypeInformation -Encoding UTF8

answered Jun 25 '21 at 10:33

Venkataraman R

12,181
2
31
58

Thanks for the response, actually in my case when I do $emails.ToString().Split(",") it gives me this as an output : System.Object[] Besides $emails.Get-Type() gives System.Array as BaseType, any thoughts ? – Iheb Jun 25 '21 at 10:44
@lheb, in that case, you can simply use the array in the for loop, as specified in the code. – Venkataraman R Jun 25 '21 at 11:33

score 0 · Accepted Answer · answered Jun 25 '21 at 11:38

You can use a simple for() loop to go through the arrays and create objects from them.
Having objects, you can easily write that out to CSV file for instance.

$emails   = $file.Worksheets['Form1'].UsedRange.Rows.Columns[4].Value2 
$Response = $file.Worksheets['Form1'].UsedRange.Rows.Columns[8].Value2

# use a loop with a counter to go through the arrays element by element
# and output objects. Capture these objects in variable $result
$result = for ($i = 0; $i -lt $emails.Count; $i++) {
    [PsCustomObject]@{
        Email    = $emails[$i]
        Response = $Response[$i]
    }
}

# output on screen
$result

# output to CSV file
$result | Export-Csv -Path 'Path\To\The\results.csv' -NoTypeInformation

On screen, you will get something like:

Email   Response
-----   --------
email1  Resp1
email2  Resp2

How to add an array as a column to csv file using powershell?

2 Answers2