Solved

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

Posted on 2016-07-27
8
26 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
Comment Utility
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
Comment Utility
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
Comment Utility
check the provided script
0
 

Author Comment

by:BillyDy
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
Comment Utility
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 51

Expert Comment

by:Bill Prew
Comment Utility
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
Comment Utility
csv files do not have tabs as stated.
0
 

Author Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
Set OWA language and time zone in Exchange for individuals, all users or per database.
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 transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

771 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

7 Experts available now in Live!

Get 1:1 Help Now