How to export a CSV to Excel using Powershell

by

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

More Related Contents:

  1. Why does Range.BorderAround emit “True” to the console?
  2. Export hashtable to CSV with the key as the column heading
  3. How to correctly display .csv files within Excel 2013?
  4. Read Excel sheet in Powershell
  5. How to create an Excel File with Nodejs?
  6. the file you are trying to open is in a different format than specified by the file extension in Asp.Net
  7. Can’t get all excel processes to stop when closing through Powershell
  8. Excel, save and close after run
  9. Combine Multiple Tables Rows Into Master Table
  10. Print function selecting wrong Type of Change option excel vba
  11. Format Cell Contents
  12. Excel formula checklist in employees
  13. How do I put double quotes in a string in vba?
  14. Stop Excel from automatically converting certain text values to dates
  15. How can I URL encode a string in Excel VBA?
  16. VBA rounding problem
  17. How to call a macro from a button and pass arguments
  18. Obtain attribute names from XML using VBA
  19. Using UNIQUE with non-adjecent columns on different sheets
  20. Paste Excel range in Outlook
  21. VBa conditional delete loop not working
  22. Export chart as image – with click of a button
  23. Excel – VLOOKUP vs. INDEX/MATCH – Which is better?
  24. Error13 in Excel VBA in data file
  25. How to output an Excel *.xls file from classic ASP
  26. Printing Unicode characters to the PowerShell prompt
  27. Importing CSV with line breaks in Excel 2007
  28. How to count cells in a range with a value less than another cell in excel?
  29. Excel cell from which a Function is called [duplicate]
  30. How to extract text within a string of text

Leave a Comment