nixda - 1 year ago 237
PowerShell Question

How to export a CSV to Excel using Powershell

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

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()