How to export a CSV to Excel using Powershell

Question

I'm trying to export a complete CSV to Excel by using Powershell. I stuck at a point where static column names are used. But this doesn't work if my CSV has generic unknown header names.

Steps to reproduce

Open your PowerShell ISE and copy & paste the following standalone code. Run it with F5
^{"C:\Windows\system32\WindowsPowerShell\v1.0\powershell_ise.exe"}

Get-Process | Export-Csv -Path $env:temp\process.csv -NoTypeInformation

$processes = Import-Csv -Path $env:temp\process.csv 
$Excel = New-Object -ComObject excel.application 
$workbook = $Excel.workbooks.add() 

$i = 1 
foreach($process in $processes) 
{ 
 $excel.cells.item($i,1) = $process.name
 $excel.cells.item($i,2) = $process.vm
 $i++ 
} 
Remove-Item $env:temp\process.csv
$Excel.visible = $true

What it does

1. The script will export a list of all active processes as a CSV to your temp folder. This file is only for our example. It could be any CSV with any data
2. It reads in the newly created CSV and saves it under the $processes variable
3. It creates a new and empty Excel workbook where we can write data
4. It iterates through all rows (?) and writes all values from the name and vm column to Excel

My questions

• What if I don't know the column headers? (In our example name and vm). How do I address values where I don't know their header names?
• How do I count how many columns a CSV has? (after reading it with Import-Csv)

I just want to write an entire CSV to Excel with Powershell

Answer 1

Ups, I entirely forgot this question. In the meantime I got a solution.
This Powershell script converts a CSV to XLSX in the background

Gimmicks are

• Preserves all CSV values as plain text like =B1+B2 or 0000001.
  You don't see #Name or anything like that. No autoformating is done.
• Automatically chooses the right delimiter (comma or semicolon) according to your regional setting
• Autofit columns

PowerShell Code

### Set input and output path
$inputCSV = "C:\somefolder\input.csv"
$outputXLSX = "C:\somefolder\output.xlsx"

### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()