• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

Combine csv files into one csv file with multiple files using PSScript

Hello,

I have a PowerShell Script to get AD Information such, Domain info, Forest info, Domain Controller List, NTP info,

It extract information into CSV files respectively.

Now I would like to merge it into one csv file with multiple tabs?

Attached is my PSScript.

Thanks in advance
ADReportCSV.txt
0
BillyDy
Asked:
BillyDy
1 Solution
 
David Johnson, CD, MVPOwnerCommented:
Your question title is misleading.. you don't want to merge anything.
What you want to do is read a directory of .csv files and put them into separate tabs of an excel document.
Function Release-Ref  
{
    param
    (
        [Object] $ref
     )

        ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
        [System.__ComObject]$ref) -gt 0)
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers() 
    }

Function ConvertCSV-ToExcel
{
<#   
  .SYNOPSIS  
    Converts one or more CSV files into an excel file.
     
  .DESCRIPTION  
    Converts one or more CSV files into an excel file. Each CSV file is imported into its own worksheet with the name of the
    file being the name of the worksheet.
       
  .PARAMETER inputfile
    Name of the CSV file being converted
  
  .PARAMETER output
    Name of the converted excel file
       
  .EXAMPLE  
  Get-ChildItem *.csv | ConvertCSV-ToExcel -output 'report.xlsx'
  
  .EXAMPLE  
  ConvertCSV-ToExcel -inputfile 'file.csv' -output 'report.xlsx'
    
  .EXAMPLE      
  ConvertCSV-ToExcel -inputfile @("test1.csv","test2.csv") -output 'report.xlsx'
  
  .NOTES
  Author: Boe Prox									      
  Date Created: 01SEPT210								      
  Last Modified:  
     
#>
     
#Requires -version 2.0  
[CmdletBinding(
    SupportsShouldProcess = $True,
    ConfirmImpact = 'low',
	DefaultParameterSetName = 'file'
    )]
Param (    
    [Parameter(
     ValueFromPipeline=$True,
     Position=0,
     Mandatory=$True,
     HelpMessage='Name of CSV/s to import')]
     [ValidateNotNullOrEmpty()]
    [array]$inputfile,
    [Parameter(
     ValueFromPipeline=$False,
     Position=1,
     Mandatory=$True,
     HelpMessage='Name of excel file output')]
     [ValidateNotNullOrEmpty()]
    [string]$output    
    )

Begin {     
    #Configure regular expression to match full path of each file
    [regex]$regex = '^\w\:\\'
    
    #Find the number of CSVs being imported
    $count = ($inputfile.count -1)
   
    #Create Excel Com Object
    $excel = new-object -com excel.application
    
    #Disable alerts
    $excel.DisplayAlerts = $False
    #Show Excel application
    $excel.Visible = $False
    #Add workbook
    $workbook = $excel.workbooks.Add()
    #Remove other worksheets
    $workbook.worksheets.Item(2).delete()
    #After the first worksheet is removed,the next one takes its place
    $workbook.worksheets.Item(2).delete()   

    #Define initial worksheet number
    $i = 1
    }

Process {
    ForEach ($input in $inputfile) {
        #If more than one file, create another worksheet for each file
        If ($i -gt 1) {
            $workbook.worksheets.Add() | Out-Null
            }
        #Use the first worksheet in the workbook (also the newest created worksheet is always 1)
        $worksheet = $workbook.worksheets.Item(1)
        #Add name of CSV as worksheet name
        $worksheet.name = "$((GCI $input).basename)"
        #Open the CSV file in Excel, must be converted into complete path if no already done
        If ($regex.ismatch($input)) {
            $tempcsv = $excel.Workbooks.Open($input) 
            }
        ElseIf ($regex.ismatch("$($input.fullname)")) {
            $tempcsv = $excel.Workbooks.Open("$($input.fullname)") 
            }    
        Else {    
            $tempcsv = $excel.Workbooks.Open("$($pwd)\$input")      
            }
        $tempsheet = $tempcsv.Worksheets.Item(1)
        #Copy contents of the CSV file
        $tempSheet.UsedRange.Copy() | Out-Null
        #Paste contents of CSV into existing workbook
        $worksheet.Paste()
        #Close temp workbook
        $tempcsv.close()
        #Select all used cells
        $range = $worksheet.UsedRange
        #Autofit the columns
        $range.EntireColumn.Autofit() | out-null
        $i++
        } 
    }        

End {
    #Save spreadsheet
    $workbook.saveas($output)
    Write-Host -Fore Green "File saved to $output"
    #Close Excel
    $excel.quit()  
    #Release processes for Excel
    $a = Release-Ref($range)
    }
}        


Function Merge-CSVFiles
    {
    Param(
    $CSVPath = 'C:\temp', ## Source CSV Folder
    $XLOutput='c:\temp\temp.xlsx' ## Output file name

    )
    remove-item $XLOutput
    $files = Get-ChildItem $CSVPath\* -Include *.csv
    convertCSV-ToExcel -output $XLOutput -inputfile $files
    #start-process $xloutput    
}

Open in new window

  https://gallery.technet.microsoft.com/ScriptCenter/7c56c444-2476-4625-b1d9-821f30280e44/
1
 
BillyDyAuthor Commented:
Hi, David.

Big thanks for pointing that out. But is there a a script/code to read a directory of *.csv files and put them into separate tabs of an CSV document as well?
0
 
David Johnson, CD, MVPOwnerCommented:
check the provided script
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
BillyDyAuthor Commented:
Apology but im not expert on PS either.

Tried dotting to add function and execute the command below:

Get-ChildItem C:\scripts\csv\*.csv | ConvertCSV-ToExcel -output c:\scripts\csv\report.csv

and true also for command Get-ChildItem C:\scripts\csv\*.csv | ConvertCSV-ToExcel -output c:\scripts\csv\report.xlsx

and I get this error:
==================
You cannot call a method on a null-valued expression.
At C:\scripts\csv\convertcsv-toexcel.ps1:86 char:5
+     $workbook.worksheets.Item(2).delete()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 
You cannot call a method on a null-valued expression.
At C:\scripts\csv\convertcsv-toexcel.ps1:88 char:5
+     $workbook.worksheets.Item(2).delete()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 
You cannot call a method on a null-valued expression.
At C:\scripts\csv\convertcsv-toexcel.ps1:101 char:9
+         $worksheet = $workbook.worksheets.Item(1)
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 
The property 'name' cannot be found on this object. Verify that the property exists and can be set.
At C:\scripts\csv\convertcsv-toexcel.ps1:103 char:30
+         $worksheet.name = "$((GCI $input).basename)"
+                              ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound
 
Method invocation failed because [System.__ComObject] does not contain a method named 'Open'.
At C:\scripts\csv\convertcsv-toexcel.ps1:106 char:13
+             $tempcsv = $excel.Workbooks.Open($input)
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound
 
You cannot call a method on a null-valued expression.
At C:\scripts\csv\convertcsv-toexcel.ps1:114 char:9
+         $tempsheet = $tempcsv.Worksheets.Item(1)
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 
You cannot call a method on a null-valued expression.
At C:\scripts\csv\convertcsv-toexcel.ps1:116 char:9
+         $tempSheet.UsedRange.Copy() | Out-Null
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 
You cannot call a method on a null-valued expression.
At C:\scripts\csv\convertcsv-toexcel.ps1:118 char:9
+         $worksheet.Paste()
+         ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 
You cannot call a method on a null-valued expression.
At C:\scripts\csv\convertcsv-toexcel.ps1:120 char:9
+         $tempcsv.close()
+         ~~~~~~~~~~~~~~~~
===============
0
 
David Johnson, CD, MVPOwnerCommented:
if you check lines 141 to 153 all you need to do is execute merge-csvfiles -csvpath c:\scripts\csv\*.csv -XLOutput c:\scripts\csv\report.xlsx

merge-csvfiles being your a modified version of your original script
0
 
Bill PrewCommented:
I may be misreading this, but just to clarify a point.  I see the following a couple of times in this question:  "put them into separate tabs of an CSV document".  There is no way to do that as stated.  CSV documents cannot by their nature store multiple "tabs" of data, it isn't supported in the format of a CSV file.

Yes, CSV files can be opened in Excel, but there is no way to open a single CSV file and get multiple tabs in Excel.  Are you really looking for something that merges several CSV files into an Excel XLS file and keeps the different CSV files in separate tabs in the XLS document?

~bp
0
 
Jambon316Commented:
csv files do not have tabs as stated.
0
 
BillyDyAuthor Commented:
Big thanks gents I have opted to used the script to consolidate cav files to one excel file with multiple tabs.
0

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now