PoshCode Logo PowerShell Code Repository

convert CSV/s to Excel by Boe Prox 17 months ago
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/2123"></script>download | new post

This script allows you to convert one or more CSVs into an Excel file with each CSV being a new worksheet in excel. The worksheet name will be the name of the file with the exception of the extension. So a file called test.csv will be named ‘test’. CSVs can be piped into the function or you can use the -inputfile parameter to accomplish this.

Get-ChildItem *.csv | ConvertCSV-ToExcel -output ‘report.xlsx’

ConvertCSV-ToExcel -inputfile ‘file.csv’ -output ‘report.xlsx’

ConvertCSV-ToExcel -inputfile @(“test1.csv”,“test2.csv”) -output ‘report.xlsx’

  1. Function Release-Ref ($ref)
  2.     {
  3.         ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
  4.         [System.__ComObject]$ref) -gt 0)
  5.         [System.GC]::Collect()
  6.         [System.GC]::WaitForPendingFinalizers()
  7.     }
  8.  
  9. Function ConvertCSV-ToExcel
  10. {
  11. <#  
  12.   .SYNOPSIS  
  13.     Converts one or more CSV files into an excel file.
  14.      
  15.   .DESCRIPTION  
  16.     Converts one or more CSV files into an excel file. Each CSV file is imported into its own worksheet with the name of the
  17.     file being the name of the worksheet.
  18.        
  19.   .PARAMETER inputfile
  20.     Name of the CSV file being converted
  21.  
  22.   .PARAMETER output
  23.     Name of the converted excel file
  24.        
  25.   .EXAMPLE  
  26.   Get-ChildItem *.csv | ConvertCSV-ToExcel -output 'report.xlsx'
  27.  
  28.   .EXAMPLE  
  29.   ConvertCSV-ToExcel -inputfile 'file.csv' -output 'report.xlsx'
  30.    
  31.   .EXAMPLE      
  32.   ConvertCSV-ToExcel -inputfile @("test1.csv","test2.csv") -output 'report.xlsx'
  33.  
  34.   .NOTES
  35.   Author: Boe Prox                                                                           
  36.   Date Created: 01SEPT210                                                                    
  37.   Last Modified:  
  38.      
  39. #>
  40.      
  41. #Requires -version 2.0  
  42. [CmdletBinding(
  43.     SupportsShouldProcess = $True,
  44.     ConfirmImpact = 'low',
  45.         DefaultParameterSetName = 'file'
  46.     )]
  47. Param (    
  48.     [Parameter(
  49.      ValueFromPipeline=$True,
  50.      Position=0,
  51.      Mandatory=$True,
  52.      HelpMessage="Name of CSV/s to import")]
  53.      [ValidateNotNullOrEmpty()]
  54.     [array]$inputfile,
  55.     [Parameter(
  56.      ValueFromPipeline=$False,
  57.      Position=1,
  58.      Mandatory=$True,
  59.      HelpMessage="Name of excel file output")]
  60.      [ValidateNotNullOrEmpty()]
  61.     [string]$output    
  62.     )
  63.  
  64. Begin {    
  65.     #Configure regular expression to match full path of each file
  66.     [regex]$regex = "^\w\:\\"
  67.    
  68.     #Find the number of CSVs being imported
  69.     $count = ($inputfile.count -1)
  70.    
  71.     #Create Excel Com Object
  72.     $excel = new-object -com excel.application
  73.    
  74.     #Disable alerts
  75.     $excel.DisplayAlerts = $False
  76.  
  77.     #Show Excel application
  78.     $excel.Visible = $False
  79.  
  80.     #Add workbook
  81.     $workbook = $excel.workbooks.Add()
  82.  
  83.     #Remove other worksheets
  84.     $workbook.worksheets.Item(2).delete()
  85.     #After the first worksheet is removed,the next one takes its place
  86.     $workbook.worksheets.Item(2).delete()  
  87.  
  88.     #Define initial worksheet number
  89.     $i = 1
  90.     }
  91.  
  92. Process {
  93.     ForEach ($input in $inputfile) {
  94.         #If more than one file, create another worksheet for each file
  95.         If ($i -gt 1) {
  96.             $workbook.worksheets.Add() | Out-Null
  97.             }
  98.         #Use the first worksheet in the workbook (also the newest created worksheet is always 1)
  99.         $worksheet = $workbook.worksheets.Item(1)
  100.         #Add name of CSV as worksheet name
  101.         $worksheet.name = "$((GCI $input).basename)"
  102.  
  103.         #Open the CSV file in Excel, must be converted into complete path if no already done
  104.         If ($regex.ismatch($input)) {
  105.             $tempcsv = $excel.Workbooks.Open($input)
  106.             }
  107.         ElseIf ($regex.ismatch("$($input.fullname)")) {
  108.             $tempcsv = $excel.Workbooks.Open("$($input.fullname)")
  109.             }    
  110.         Else {    
  111.             $tempcsv = $excel.Workbooks.Open("$($pwd)\$input")      
  112.             }
  113.         $tempsheet = $tempcsv.Worksheets.Item(1)
  114.         #Copy contents of the CSV file
  115.         $tempSheet.UsedRange.Copy() | Out-Null
  116.         #Paste contents of CSV into existing workbook
  117.         $worksheet.Paste()
  118.  
  119.         #Close temp workbook
  120.         $tempcsv.close()
  121.  
  122.         #Select all used cells
  123.         $range = $worksheet.UsedRange
  124.  
  125.         #Autofit the columns
  126.         $range.EntireColumn.Autofit() | out-null
  127.         $i++
  128.         }
  129.     }        
  130.  
  131. End {
  132.     #Save spreadsheet
  133.     $workbook.saveas("$pwd\$output")
  134.  
  135.     Write-Host -Fore Green "File saved to $pwd\$output"
  136.  
  137.     #Close Excel
  138.     $excel.quit()  
  139.  
  140.     #Release processes for Excel
  141.     $a = Release-Ref($range)
  142.     }
  143. }

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me