First thing coming in everyone’s mind à why this is required when I have already shared in the past few methods to achieve the same:
PowerShell TIP – CSV to EXCEL | Tech Wizard
Convert any CSV to Formatted Excel file | Tech Wizard
PowerShell System Admin Module | Tech Wizard
- How is this one different from above?
- The problem it resolves is à it does not require Excel to be present on the machine while all the above require Excel Installation.
-
It can work in background running from task scheduler.
- Why don’t we can just use ImportExcel powershell module?
We can definitely use that, in fact this function which I am including in newer version of VSADMIN also requires ImportExcel module as prerequisite.
I was dealing with huge amount of data and when I was using | export-excel from CSV file, it was running slow and in the end data was altered that forced me to utilize
Save-CSV2Excel but scheduled task was having issues with it when running in background as it uses Excel.
This made me write the below function:
########################################################################
function Convert-CSV2Excel {
[CmdletBinding()]
param
(
[Parameter(Mandatory = $true)]
[ValidateScript({
if(-Not ($_ | Test-Path) ){throw “File or folder does not exist”}
if(-Not ($_ | Test-Path -PathType Leaf) ){throw “The Path argument must be a file. Folder paths are not allowed.”}
if($_ -notmatch “(\.CSV)”){throw “The file specified in the path argument must be either of type CSV”}
return $true
})]
$CSVFile,
[Parameter(Mandatory = $true)]
[ValidateScript({
if($_ -notmatch “(\.XLSX)”){throw “The file specified in the path argument must be either of type XLSX”}
return $true
})]
$ExcelFile,
$Delimiter=”,”,
$TextQualifier='”‘
)
# Load EPPlus from import-excel module
$importexcel = (Get-Module -ListAvailable importexcel).path
if($importexcel.count -gt 1){
$rootpath = split-path -path (Get-Module -ListAvailable importexcel).path[0] -Parent
$DLLPath = $rootpath + “\EPPlus.dll”
}else{
$rootpath = split-path -path (Get-Module -ListAvailable importexcel).path -Parent
$DLLPath = $rootpath + “\EPPlus.dll”
}
[Reflection.Assembly]::LoadFile($DLLPath) | Out-Null
# Set CSV Format
$Format = New-object -TypeName OfficeOpenXml.ExcelTextFormat
$Format.Delimiter = $Delimiter
# use Text Qualifier if your CSV entries are quoted, e.g. “Cell1″,”Cell2”
$Format.TextQualifier = $TextQualifier
$Format.Encoding = [System.Text.Encoding]::UTF8
$Format.SkipLinesBeginning = ‘0’
$Format.SkipLinesEnd = ‘1’
# Set Preferred Table Style
$TableStyle = [OfficeOpenXml.Table.TableStyles]::Medium1
# Create Excel File
$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage
$ExcelPackage.Encryption.IsEncrypted = $false
$Worksheet = $ExcelPackage.Workbook.Worksheets.Add(“FromCSV”)
# Load CSV File with first row as heads using a table style
#$null=$Worksheet.Cells.LoadFromText((Get-Item $CSVFile),$Format,$TableStyle,$true)
# Load CSV File without table style
$null=$Worksheet.Cells.LoadFromText((Get-Item $CSVFile),$format)
# Fit Column Size to Size of Content
$Worksheet.Cells[$Worksheet.Dimension.Address].AutoFitColumns()
# Save Excel File
$ExcelPackage.SaveAs($ExcelFile)
Write-Host “CSV File $CSVFile converted to Excel file $ExcelFile”
}#Convert-CSV2Excel
##########################################################################
This function will use the EPPLUS.dll from ImportExcel module and will convert CSV to Excel.
- It is fast in conversion
- It will run in the background as well.
Below is the Syntax:
Convert-CSV2Excel -CSVFile $tempcsv1 -ExcelFile $Report1
I hope if you are having the same issues, this function or vsadmin module will assist you.
Thanks for reading…..
Tech Wizard
Tech Wizard 