I have the following code that takes in an excel file, and converts it to a csv file. its working perfectly, except, i’d like to add an enhancement: dont export empty lines/rows, because some exported csv files, i have to go in manually and delete the blank/empty rows/lines and it would be a lot easier if this is done automatically as part of the export. How can i specify that?
function Convert-ExcelSheetsToCsv {
param(
[Parameter(Mandatory, ValueFromPipelineByPropertyName, Position=1)]
[ValidateNotNullOrEmpty()]
[Alias('FullName')]
[string]$Path,
[Parameter(Mandatory = $false, Position=0)]
[bool]$AppendFileName,
[Parameter(Mandatory = $false, Position=2)]
[bool]$ExcludeHiddenSheets,
[Parameter(Mandatory = $false, Position=3)]
[bool]$ExcludeHiddenColumns,
[Parameter(Mandatory = $false, Position=4)]
$ExcludeWorkSheets,
[Parameter(Mandatory = $false, Position=5)]
[bool]$AppendToColumnName,
[Parameter(Mandatory = $false, Position=6)]
$columnNameSuffix
)
Begin {
"<code>r</code>nTerminating Excel process (if any) to prevent 'Can't access' (file in use) exception..."
Get-Process 'exce[l]' | Stop-Process -Force #this is to avoid "Can't access" exception if file is opened or in use
#Only 'excel' will be matched, but because a wildcard [] is used, not finding a match will not generate an error.
$excel = New-Object -ComObject Excel.Application -Property @{
Visible = $false
DisplayAlerts = $false
}
}
Process {
#$root = Split-Path -Path $Path
$filename = [System.IO.Path]::GetFileNameWithoutExtension($Path)
"<code>r</code>nGenerating CSV files from '$Path' Worksheet(s)..."
$workbook = $excel.Workbooks.Open($Path)
foreach ($worksheet in ($workbook.Worksheets | Where-Object { <# $_.Visible -eq -1 #> !($_.Name -in (($ExcludeWorkSheets).Split('(.+?)(?:,|$)'))) })) {
if($ExcludeHiddenColumns) {
$ColumnsCount = $worksheet.UsedRange.Columns.Count
for ($i=1; $i -le $ColumnsCount; $i++)
{
$column = $worksheet.Columns.Item($i).EntireColumn #$worksheet.sheets.columns.entirecolumn.hidden=$true
if ($column.hidden -eq $true)
{
$columnname = $column.cells.item(1,$i).value2
if ($worksheet.Visible -eq 0) #worksheet hidden
{
"<code>r</code>nHidden column [{0}] found in hidden [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
}
else {
"<code>r</code>nHidden column [{0}] found in [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
}
try {
$column.Delete() | out-null
"<code>r</code>nHidden column [{0}] was Deleted! Proceeding with Export to CSV operation...<code>r</code>n" -f $columnname
}
catch {
"<code>r</code>nFailed to Delete hidden column [$columnname] from [$($worksheet.name)] worksheet! $($error[0])<code>r</code>n$($error[0].InvocationInfo.PositionMessage)<code>r</code>n$($error[0].ScriptStackTrace)"
#$_ | Select *
}
#$i = $i - 1
}
}
}
if ($ExcludeHiddenSheets) {
if ($worksheet.Visible -eq -1) #worksheet visible
{
if($AppendToColumnName) {
Rename-WorkSheetColumns $worksheet $columnNameSuffix
}
$ws = $worksheet
}
}
else {
if($AppendToColumnName) {
Rename-WorkSheetColumns $worksheet $columnNameSuffix
}
$ws = $worksheet
}
if ($AppendFileName) {
$name = Join-Path -Path $csvPATH <# $root #> -ChildPath "${filename}_$($ws.Name).csv"
}
else {
$name = Join-Path -Path $csvPATH <# $root #> -ChildPath "$($ws.Name).csv"
}
try {
$ws.SaveAs($name, 6) #6 to ignore formatting and convert to pure text, otherwise, file could end up containing rubbish
#"<code>r</code>n'$name' generated successfully!"
}
catch {
"<code>r</code>nFailed to save csv! Path: '$name'. $($error[0])<code>r</code>n$($error[0].InvocationInfo.PositionMessage)<code>r</code>n$($error[0].ScriptStackTrace)"
}
}
}
End {
$excel.Quit()
$null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
}
}
i am thinking it would be done here, but i dont know the value to specify not to export empty lines
$ws.SaveAs($name, 6)