Solved

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

Posted on 2016-07-27
8
27 Views
Last Modified: 2016-07-29
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
Comment
Question by:BillyDy
8 Comments
 
LVL 78

Accepted Solution

by:
David Johnson, CD, MVP earned 500 total points
ID: 41732271
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
 

Author Comment

by:BillyDy
ID: 41732296
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
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
ID: 41732300
check the provided script
0
 

Author Comment

by:BillyDy
ID: 41732318
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
ID: 41732375
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
 
LVL 52

Expert Comment

by:Bill Prew
ID: 41732889
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
 
LVL 5

Expert Comment

by:Jambon316
ID: 41732978
csv files do not have tabs as stated.
0
 

Author Comment

by:BillyDy
ID: 41734253
Big thanks gents I have opted to used the script to consolidate cav files to one excel file with multiple tabs.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
Disabling the Directory Sync Service Account in Office 365 will stop directory synchronization from working.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now