Excel to CSV Conversion - Issue

Hello All,

I have an excel set that contains a bunch of columns and from that excel sheet, I have to create a CSV file with a specific set of columns (4 - Column in-consideration).
I am using the below PS script to convert excel to CSV and from CSV I am pickup 4 columns and creating another CSV but the problem is for two - column I am getting output but the other two-column are showing empty values, Please help

==========
Function ExcelToCsv ($File) {
$myDir = “C:\temp”
$excelFile = “$myDir” + $File + “.xlsx”
$Excel = New-Object -ComObject Excel.Application
$wb = $Excel.Workbooks.Open($excelFile)

foreach ($ws in $wb.Worksheets) {
    $ws.SaveAs("$myDir\" + $File + ".csv", 6)
}
$Excel.Quit()

}

$FileName = “Week plan Template”
ExcelToCsv -File $FileName
Start-Sleep -Second 30
$Headers = @(

Server Name

Server OS

Current  Version

Target  Version


# Add all other desired headers here

"$((Get-Date).ToShortDateString())" # << Header for todays date e.g. 19/06/2018

)

Import-Csv -Path “C:\temp\Week plan Template.csv” | Select $Headers | Export-Csv -Path “C:\temp\Input.csv” -Force -NoTypeInformation

To make your life easier you should take a look at the great module from Doug Finke

1 Like