If you work with data, chances are you’ve encountered CSV files. While they’re great for storing and exchanging data, they can be a bit time consuming to work with in certain situations. If you’ve ever needed to analyze or present CSV data in a more user-friendly way, you might have found yourself wishing for an easier way to convert it to Excel format. Luckily, PowerShell provides a simple solution to this problem. In this blog post, we’ll walk through a PowerShell script that you can use to quickly and easily convert your CSV files to Excel format, so you can spend more time analyzing your data and less time fussing with file formats.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
# CSV Bulk Converter Script # This PowerShell script converts all CSV files in a specific location to XLSX format. # It uses the Microsoft Excel COM object to open each CSV file, convert it to a table, and save it as an XLSX file. # The converted files are saved in the same location as the original CSV files. # NOTE: This script will convert all CSV files in the specified location, so use with caution. # - Specify the delimiter used in the CSV file. $csvDelimiter = "," # - Path to CSV files. (WARNING: ALL CSV FILES WILL BE CONVERTED ) $csvLocation = "C:\temp\CSV Converter" # - Code foreach ($csvFile in Get-Childitem -Path $csvLocation -Filter "*.csv") { Write-Host -ForegroundColor Green "Converting file '$($csvFile.BaseName)' to XLSX format." $excel = New-Object -ComObject excel.application $xlsxFilepath = "$csvLocation\$($csvFile.BaseName).xlsx" $workbook = $excel.Workbooks.Add(1) $worksheet = $workbook.worksheets.Item(1) $csvFilepath = ("TEXT;" + $csvFile.FullName) $csvConnector = $worksheet.QueryTables.add($csvFilepath, $worksheet.Range("A1")) $query = $worksheet.QueryTables.item($csvConnector.name) $query.TextFileOtherDelimiter = $csvDelimiter $query.TextFileParseType = 1 $query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count $query.AdjustColumnWidth = 1 $query.TextFilePlatform = 65001 # UTF8 fix $query.Refresh()| Out-Null $query.Delete() Add-Type -AssemblyName "Microsoft.Office.Interop.Excel" $worksheet.Columns.AutoFit() | Out-Null $table = $excel.ActiveSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $excel.ActiveCell.CurrentRegion, $null ,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes) $table.Name = "TableData" $table.TableStyle = "TableStyleMedium20" $workbook.SaveAs($xlsxFilepath,51) $excel.Quit() # - Comment out the section below if you don't want to remove the CSV file after converting. if (Test-Path "$csvLocation\$($csvFile.BaseName).xlsx") { Remove-Item -Path $($csvFile).FullName -Force} } |
Copy the code above to your PowerShell ISE session and modify the variables as needed.



The final output of the file after being converted will look like the example below.

If you have any questions / feedback or would like to correct me on any of the stuff above, please use the comment section or contact me directly using the blue button in the bottom right corner.
Hello Lucas,
Thank you for the script and it’s working as per my requirement, But I need some help, I wanted to create a folder where user can input folder name (e.g. BD20230612) and all converted xlsx files should move to the user created folder which is BD20230612.
I have tried to modified your script and I am able to create folder and all converted xlsx files are getting created but with name extension like this (reportname2023-05-24.CSV.BaseName.xlsx) I want the report to be in this way (reportname2023-05-24.xlsx)
Could you please help me. Thanks in advance
Hello Anand, you can achieve that with the script shared in the comment section by Boulavogue at the following link: https://code.adonline.id.au/csv-to-xlsx-powershell/
Best regards
Hello Anand,
You can try the following Powershell script to convert CSV files to XLSX in a folder of your choice.
gci “c:/path/to/find/files/*.csv” | %{
$Path = $_.DirectoryName
$filename = $_.BaseName
#Define locations and delimiter
$csv = $_.FullName #Location of the source file
#$xlsx = “$Path/$filename.xlsx” # Names & saves Excel file same name/location as CSV
$xlsx = “c:/path/to/save/files/$filename.xlsx” # Names Excel file same name as CSV
$delimiter = “;” #Specify the delimiter used in the file
# 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 and reformat the data
$TxtConnector = (“TEXT;” + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range(“A1”))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType = 1
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1
# Execute & delete the import query
$query.Refresh()
$query.Delete()
# Save & close the Workbook as XLSX.
$Workbook.SaveAs($xlsx,51)
$excel.Quit()
}
Hello Lucas,
Many thanks for the perfect script. It works just fine.
I did some modification regard to the output folder. Nothing special.
Another thing I did not manage to achieve is to avoid first row in .csv to be header with applied filter in Excel. Can you help me to get plain Excel without header, just rows with data as is in .csv?
TIA
Mladen
Hi Lucas,
It seems to work, however it does not retain values the way I want. E.g. I have a value of “000000” in a field in my csv file. After conversion to excel its stored as a “0”. Could you suggest the approach to facilitate this requirement? Sry, kind of new to PowerShell…
Kind regards,
Gerrit
Hello Lucas,
Thank you for this really cool script.
I have a trouble when I use it. Each time a file is converted, I have a the “Save as” Excel window that opens and ask me which name I would like to give to the xlsx file.
Have you an idea what is the problem ?
Best regards.