Solved

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

Posted on 2016-07-27
8
32 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 80

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 80

Expert Comment

by:David Johnson, CD, MVP
ID: 41732300
check the provided script
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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
 
LVL 80

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 53

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In-place Upgrading Dirsync to Azure AD Connect
This article explains the steps required to use the default Photos screensaver to display branding/corporate images
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

789 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