Solved

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

Posted on 2016-07-27
8
36 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 54

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

740 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