transpose excel copied data

Hello,

I use this code to copy, paste data from excel to export in a csv file.

The probleme i have is the data I copy is a range of cells in one line.

So when I paste and export to a csv, the result in the csv is one one line

Here is what I have in the csv:

user1, user2, user3, user4

But I would like to have:

user1

user2

user3

user4

 

here is the code I use.

thanks for Your help

$Range_GG=“D1:AK1”

$xlCSV=23

$Excel = New-Object -comobject Excel.Application
$Excel.Visible = $False
$Excel.displayalerts=$False

$Workbook = $Excel.Workbooks.Open($ExcelFileName)
$WorkSheet = $WorkBook.Worksheets.Item(1)
$WorkSheet.activate()
$Range2 = $WorkSheet.Range($Range_GG)
$Range2.Copy()
$Worksheet = $Workbook.Worksheets.item(2)
$Range2 = $Worksheet.Range($Range_GG)
$Worksheet.Paste($range2)
$Worksheet.SaveAs($CSVfilename2,$xlCSV)
Get-Content -Path “$CSVfilename2” | Out-File -FilePath “$CSVUnifilename2” -Encoding Unicode
$Excel.Quit()
If(ps excel){kill -name excel}

you should definitely checkout ImportExcel module. I believe It can help you here.