Bulk Convert CSV Files to Excel Files Using Powershell

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.

Copy the code above to your PowerShell ISE session and modify the variables as needed.

Example output from script.
Before running the script.
After running the script.

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.

5 thoughts on “Bulk Convert CSV Files to Excel Files Using Powershell”

  1. 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

    Reply
  2. 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()

    }

    Reply
  3. 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

    Reply
  4. 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

    Reply

Leave a Comment