[excel] How to export a CSV to Excel using Powershell

This topic really helped me, so I'd like to share my improvements. All credits go to the nixda, this is based on his answer.

For those who need to convert multiple csv's in a folder, just modify the directory. Outputfilenames will be identical to input, just with another extension.

Take care of the cleanup in the end, if you like to keep the original csv's you might not want to remove these.

Can be easily modifed to save the xlsx in another directory.

$workingdir = "C:\data\*.csv"
$csv = dir -path $workingdir
foreach($inputCSV in $csv){
$outputXLSX = $inputCSV.DirectoryName + "\" + $inputCSV.Basename + ".xlsx"
### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$excel.DisplayAlerts = $False
$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
### $Excel.Application.International(3) = ,
### $Excel.Application.International(5) = ;
$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()
}
## To exclude an item, use the '-exclude' parameter (wildcards if needed)
remove-item -path $workingdir -exclude *Crab4dq.csv

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to powershell

Why powershell does not run Angular commands? How do I install the Nuget provider for PowerShell on a unconnected machine so I can install a nuget package from the PS command line? How to print environment variables to the console in PowerShell? Check if a string is not NULL or EMPTY The term 'ng' is not recognized as the name of a cmdlet VSCode Change Default Terminal 'Connect-MsolService' is not recognized as the name of a cmdlet Powershell Invoke-WebRequest Fails with SSL/TLS Secure Channel Install-Module : The term 'Install-Module' is not recognized as the name of a cmdlet Change directory in PowerShell

Examples related to windows-7

ng is not recognized as an internal or external command Why am I getting ImportError: No module named pip ' right after installing pip? How to Delete node_modules - Deep Nested Folder in Windows Telnet is not recognized as internal or external command Multiple -and -or in PowerShell Where-Object statement How do I set ANDROID_SDK_HOME environment variable? Run Batch File On Start-up Why isn't .ico file defined when setting window's icon? How to access shared folder without giving username and password Can't start hostednetwork

Examples related to export-to-excel

Export from pandas to_excel without row names (index)? How to export dataGridView data Instantly to Excel on button click? How to export a CSV to Excel using Powershell How to create an Excel File with Nodejs? Download Excel file via AJAX MVC Export data to Excel file with ASP.NET MVC 4 C# is rendering into view Saving results with headers in Sql Server Management Studio phpexcel to download Export SQL query data to Excel How can I export tables to Excel from a webpage

Examples related to office-2013

How to correctly display .csv files within Excel 2013? How to export a CSV to Excel using Powershell UML diagram shapes missing on Visio 2013