How to export a CSV to Excel using Powershell

How to export a CSV to Excel using 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 dont 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:somefolderinput.csv
$outputXLSX = C:somefolderoutput.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()

I am using excelcnv.exe to convert csv into xlsx and that seemed to work properly.
You will have to change the directory to where your excelcnv is. If 32 bit, it goes to Program Files (x86)

Start-Process -FilePath C:Program FilesMicrosoft OfficerootOffice16excelcnv.exe -ArgumentList -nme -oice $xlsFilePath $xlsToxlsxPath

How to export a CSV to Excel using Powershell

Why would you bother? Load your CSV into Excel like this:

$csv = Join-Path $env:TEMP process.csv
$xls = Join-Path $env:TEMP process.xlsx

$xl = New-Object -COM Excel.Application
$xl.Visible = $true

$wb = $xl.Workbooks.OpenText($csv)

$wb.SaveAs($xls, 51)

You just need to make sure that the CSV export uses the delimiter defined in your regional settings. Override with -Delimiter if need be.


Edit: A more general solution that should preserve the values from the CSV as plain text. Code for iterating over the CSV columns taken from here.

$csv = Join-Path $env:TEMP input.csv
$xls = Join-Path $env:TEMP output.xlsx

$xl = New-Object -COM Excel.Application
$xl.Visible = $true

$wb = $xl.Workbooks.Add()
$ws = $wb.Sheets.Item(1)

$ws.Cells.NumberFormat = @

$i = 1
Import-Csv $csv | ForEach-Object {
  $j = 1
  foreach ($prop in $_.PSObject.Properties) {
    if ($i -eq 1) {
      $ws.Cells.Item($i, $j++).Value = $prop.Name
    } else {
      $ws.Cells.Item($i, $j++).Value = $prop.Value
    }
  }
  $i++
}

$wb.SaveAs($xls, 51)
$wb.Close()

$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

Obviously this second approach wont perform too well, because its processing each cell individually.

Leave a Reply

Your email address will not be published. Required fields are marked *