Convert-CSV2Excel without Excel Installation

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

https://techwizard.cloud

https://syscloudpro.com/

PowerShell Fast Track

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s