none
How to move data fields to specified locations on the export file using Powershell RRS feed

  • Question

  • I wrote some powershell code to export a file. I need data points to be in specific locations on the output file. For example, in the code I wrote below, if I would like 'value2' listed on the 3rd column of the csv export file (C:1 in excel). It currently exports to the second column because it is the second value provided. 

    The end of the code is below:

    $extractFile = "C:\Users\testname + $(((get-date).ToUniversalTime()).ToString("yyyyMMddThhmmssZ")) + $filename
    $testsummary= $QueryBaseResults.Tables[2] | Select 'value1', 'value2', 'value3', 'value4', 'value5' -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors  | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 |Set-Content -Path $extractFile

    Thank you.

    Thursday, August 16, 2018 8:59 PM

All replies

  • Switch value 'value2' and 'value3'.

    $extractFile = "C:\Users\testname" + $(((get-date).ToUniversalTime()).ToString("yyyyMMddThhmmssZ")) + $fileName
    $testSummary = $QueryBaseResults.Tables[2] | 
     Select-Object 'value1', 'value3', 'value2', 'value4', 'value5' -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors -Skip 1 | 
     Export-Csv -Path $extractFile -NoTypeInformation



    Thursday, August 16, 2018 9:22 PM
  • Hi,

    Thanks for your question.

    Excel com object can be a good choice.

    You can follow the example below.

    # Create Excel object $excel = new-object -comobject Excel.Application # Make Excel visible $excel.visible = $true # Create a new workbook $workbook = $excel.workbooks.add() # default workbook has three sheets, remove 2 $S2 = $workbook.sheets | where {$_.name -eq "Sheet2"} $s3 = $workbook.sheets | where {$_.name -eq "Sheet3"} $s3.delete() $s2.delete() # Get sheet and update sheet name $s1 = $workbook.sheets | where {$_.name -eq 'Sheet1'} $s1.name = "PowerShell Sample" # Update workook properties $workbook.author = "Thomas Lee - tfl@psp.co.uk" $workbook.title = "Excel and PowerShell rock!" $workbook.subject = "Demonstrating the Power of PowerShell" # Next update some cells in the worksheet 'PowerShell Sample' $workbook = $excel.Workbooks.add()
    $sheet = $workbook.worksheets.Item(1)

    $sheet.cells.item(3,1) = "value2"

    ...

    # And save it away:
    $extractFile = "C:\Users\testname + $(((get-date).ToUniversalTime()).ToString("yyyyMMddThhmmssZ")) + $filename
    $s1.saveas($extractFile)


     Refer the link below.

    click it

    Best Regards,

    Lee


    Just do it.

    Friday, August 17, 2018 2:08 AM
    Moderator
  • Excel has absolutely nothing to do with the question. CSV is not Excel although it is, by default, configured to open in Excel.  Excel can open many kinds of files that are not Excel files.  You can associate any file with Excel even ones that Excel can't open and they will show an Excel icon in File Explorer.

    Learning how all of this works is fundamental to Windows.


    \_(ツ)_/

    Friday, August 17, 2018 2:30 AM
    Moderator
  • Excel has absolutely nothing to do with the question. CSV is not Excel although it is, by default, configured to open in Excel.  Excel can open many kinds of files that are not Excel files.  You can associate any file with Excel even ones that Excel can't open and they will show an Excel icon in File Explorer.

    Learning how all of this works is fundamental to Windows.


    \_(ツ)_/

    "Learning how all of this works is fundamental to Windows."

    Stop commenting on people's post if you are going to insult and cannot help with their "POWERSHELL" question. 

    Friday, August 17, 2018 6:20 PM