transpose excel copied data

Welcome Forums General PowerShell Q&A transpose excel copied data

This topic contains 1 reply, has 2 voices, and was last updated by

 
Senior Moderator
2 weeks ago.

  • Author
    Posts
  • #174751

    Participant
    Topics: 1
    Replies: 0
    Points: 12
    Rank: Member

    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}

  • #174805

    Senior Moderator
    Topics: 8
    Replies: 1041
    Points: 3,438
    Helping Hand
    Rank: Community Hero

    you should definitely checkout ImportExcel module. I believe It can help you here.
    https://www.powershellgallery.com/packages/ImportExcel/6.5.1

You must be logged in to reply to this topic.