none
How to export powershell multiple output to same csv file RRS feed

  • Question

  • Hi Team,

    I am in the process of exporting the PowerShell out put to csv file.I can able to export to csv file.

    But my requirement is to export the multiple out put  in same csv file with different tabs with names like before and after.

    like below but not working. Please advice

      $info  | Export-Csv   "Before" -Path c:\users\admin\desktop\Cureentvalues.csv -NoTypeInformation -Force
      $info  | Export-Csv  "After" -path  c:\users\admin\desktop\Cureentvalues.csv -NoTypeInformation -Force

    Wednesday, January 30, 2019 2:17 PM

Answers

  • CSV file do not have "tabs".  A CSV file is not an Excel file it is a text file that can be opened in Excel.  You need to add the CSV file to an Excel workbook each on a new worksheet (tab).


    \_(ツ)_/

    Wednesday, January 30, 2019 3:09 PM
  • This was floating around my junk box.  It works as a good example of a simple way to add multiple CSV files to a workbook.

    # import multiple CSV files into separate Excel worksheets
    $inputfolder='d:\testcsv'
    $mergedwb='d:\testcsv\mytest.xlsx'
    
    $xl = New-Object -ComObject Excel.Application
    $xl.Visible=$true
    
    $wb = $xl.WorkBooks.add()
    
    Get-ChildItem $inputfolder\*.csv |
        ForEach-Object{
            Try{
                Write-Host "Moving $_" -ForegroundColor green
                $sheet = $wb.Sheets.Add()
                $sheet.Name = $_.BaseName
                $date = Get-Content $_ -Raw
                Set-Clipboard $data
                $sheet.UsedRange.PasteSpecial(
                    [Microsoft.Office.Interop.Excel.XlPasteType]::xlPasteAll,
                    [Microsoft.Office.Interop.Excel.XlPasteSpecialOperation]::xlPasteSpecialOperationAdd
                )
                $sheet.UsedRange.TextToColumns(
                    $sheet.UsedRange,
                    [Microsoft.Office.Interop.Excel.XlTextParsingType]::xlDelimited,
                    [Microsoft.Office.Interop.Excel.XlTextQualifier]::xlTextQualifierDoubleQuote,
                    $false, $false, $false, $true
                )
            }
            Catch{
                Write-Host $_ -ForegroundColor Red
            }
        }
    $wb.Sheets.Item('sheet1').Delete()
    
    $wb.SaveAs($mergedwb)
    $wb.Close()
    $xl.Quit()
    
    


    \_(ツ)_/

    Thursday, January 31, 2019 3:42 AM

All replies

  • Check out the -NoClobber parameter of Export-Csv.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Wednesday, January 30, 2019 2:26 PM
    Moderator
  • CSV file do not have "tabs".  A CSV file is not an Excel file it is a text file that can be opened in Excel.  You need to add the CSV file to an Excel workbook each on a new worksheet (tab).


    \_(ツ)_/

    Wednesday, January 30, 2019 3:09 PM
  • thank you master
    Thursday, January 31, 2019 1:51 AM
  • This was floating around my junk box.  It works as a good example of a simple way to add multiple CSV files to a workbook.

    # import multiple CSV files into separate Excel worksheets
    $inputfolder='d:\testcsv'
    $mergedwb='d:\testcsv\mytest.xlsx'
    
    $xl = New-Object -ComObject Excel.Application
    $xl.Visible=$true
    
    $wb = $xl.WorkBooks.add()
    
    Get-ChildItem $inputfolder\*.csv |
        ForEach-Object{
            Try{
                Write-Host "Moving $_" -ForegroundColor green
                $sheet = $wb.Sheets.Add()
                $sheet.Name = $_.BaseName
                $date = Get-Content $_ -Raw
                Set-Clipboard $data
                $sheet.UsedRange.PasteSpecial(
                    [Microsoft.Office.Interop.Excel.XlPasteType]::xlPasteAll,
                    [Microsoft.Office.Interop.Excel.XlPasteSpecialOperation]::xlPasteSpecialOperationAdd
                )
                $sheet.UsedRange.TextToColumns(
                    $sheet.UsedRange,
                    [Microsoft.Office.Interop.Excel.XlTextParsingType]::xlDelimited,
                    [Microsoft.Office.Interop.Excel.XlTextQualifier]::xlTextQualifierDoubleQuote,
                    $false, $false, $false, $true
                )
            }
            Catch{
                Write-Host $_ -ForegroundColor Red
            }
        }
    $wb.Sheets.Item('sheet1').Delete()
    
    $wb.SaveAs($mergedwb)
    $wb.Close()
    $xl.Quit()
    
    


    \_(ツ)_/

    Thursday, January 31, 2019 3:42 AM
  • Hi,

    I am getting the below error. Please help

    Moving X:\KM\T8XX.csv
    Unable to get the PasteSpecial property of the Range class

    Thank you

    Friday, July 19, 2019 7:44 AM
  • Topic answered and closed. You must start your own topic. Do not hijack other users topics please.

    \_(ツ)_/

    Friday, July 19, 2019 8:18 AM