Skip to content

Convert CSV to XLSX via PowerShell

A nifty PowerShell script that will take a delimited CSV file and convert it to a XLSX file.

Having extracted data from a substantial database, I was left with a massive delimited CSV file that required conversion to Microsoft Excel XLSX format.

Screenshot of a CSV file in Microsoft Excel with delimited data.

Screenshot of a CSV file in Microsoft Excel with delimited data.

I wanted to save the file as an XLSX and perform a text-to-columns function, thus combining two steps into one.

Thankfully this is possible using PowerShell.

Screenshot of a XLSX file in Microsoft Excel with data arranged in cells.

Screenshot of a XLSX file in Microsoft Excel with data arranged in cells.

There are several pieces of code online that purport to do this but don’t work well (Example 1, Example 2, Example 3). Online tools exist too (Example 1, Example 2).

The following code is substantially based on the work of Nixta but I have made several changes including the manual specification of the delimiting character. The original code was unable to determine the delimiting character within the CSV and so whilst I was successful in obtaining a XLSX file, it was still semi-colon separated.

The following script will open the specified CSV, save it as an XLSX file to a specified location and perform a text-to-columns based on the delimiter being a semicolon (;). Cells will be in the “General” format.

#Define locations and delimiter
$csv = "c:/path/to/file/whatever.csv" #Location of the source file
$xlsx = "c:/path/to/file/whatever.xlsx" #Desired location of output
$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()
   

Comments:

24 responses to “Convert CSV to XLSX via PowerShell”

  • Written by Milan on 23 February 2017:

    Good work!

    Very useful, it works exactly as described, thanks!

  • Written by Axel Öhman on 3 April 2017:

    How would i go about changing the encoding in the output of this script? I have csv’s containing ÅÄÖ, which turns to gibberish when i run this otherwise excellent script.

    • Written by c3p0 on 9 July 2018:

      Just add
      $query.TextFilePlatform = 65001
      worked for me

  • Written by Harun on 9 June 2017:

    You are gorgeous, million Thanks

  • Written by Boulavogue on 21 June 2017:

    Thanks for this, while my files are converting here’s an addition to the above script (for converting all CSV files in a folder)


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

    }

  • Written by Menno Bakker on 8 August 2017:

    Very nice and usefull but can you explain to me what the next three lines of code should be doing?

    $query.TextFileParseType = 1
    $query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
    $query.AdjustColumnWidth = 1

    When i rule them out the created xlsx is still the same.

    Greatings..

  • Written by Jean on 22 February 2018:

    In this sample code, the Cells are in the “General” format. How do I change it to “Text” format?

    • Written by Jan on 16 April 2018:

      Change TextFileColumnDataTypes from 1 to 2

  • Written by Jean-Francois on 16 March 2018:

    I would like to delete the file in the the folder where the csv is locate at the end of the convertion. Can you help me

    • Written by c3p0 on 9 July 2018:

      wow google for two minutes should have brought you an answer.
      Just add these lines at the end of the code:

      if($?)
      {
      Remove-Item $csv
      }

  • Written by warren on 16 April 2018:

    the last 2 lines

    # Save & close the Workbook as XLSX.
    $Workbook.SaveAs($xlsx,51)
    $excel.Quit()

    save the file in excel format. if for example the file exists and you want to overwrite it how do you overwrite it?

    • Written by c3p0 on 9 July 2018:

      You can overwrite by adding this:

      $excel.DisplayAlerts = $False

  • Written by Rahul Udayabhanu on 14 May 2018:

    Hi, I tried to using this csv to Excel convert in one of storage Capacity reporting script. In script the storage cmd will run and generate a CSV data and I tried to put it as source for this script. It is working successfully when I manually running it. But the xlsx file is not generating when I putting this in a Windows task scheduler and running it , but storage cmd and csv file generation is happening. Could you someone help me on where is wrong. I am confused.

    Below is the script:-

    $csv = "D:\Temp\UnityScript\Logs\FS_UTIL" +".csv" #Location of the source file
    $xlsx = "D:\Temp\UnityScript\Logs\FS_UTIL1" +".xlsx" #Desired location of output
    
    $delimiter = "," #Specify the delimiter used in the file
    
    Remove-Item $csv
    Remove-Item $xlsx 
    
    #EMC Storage cmd which give CSV output
    uemcli -d X.X.X.X -u XXX -p XXX  /stor/prov/fs show -output csv -filter "Name,Health details" >> $csv
    
    # 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()
  • Written by Olaf on 30 May 2018:

    Hi guys,

    the script is working great but I don’t understand what means the $Workbook.SaveAs($xlsx,51) 51?

    What is this for? and why is the script gcreating an xlsx in Documents?

    Any help appriciated

    Olaf

  • Written by igb528 on 30 May 2018:

    It worked! Ty.

  • Written by Julie on 24 October 2018:

    Any idea how i could treat consecutive delimiters (in my case, a space) as one in the header?

  • Written by Mel on 5 December 2018:

    thank you for the above script, it really helped alot as i was struggling, but now i need to add in a filter in the script for a specific field and delete all other irrelevant columns…. any ideas? i need to trip 200 columns down to 25..

  • Written by Oldiki on 17 December 2018:

    I’ve tried a lot of commands but without success. That’s was the only one that worked to me on Powershell.

    Thanks a lot.

  • Written by Jason on 16 January 2019:

    Could this be edited to take in the data from a SQL Query directly? Where the data from the query was returned to a variable, $ReportTable?

    $SQLUser = "username"
    $SQLPassword = "password"
    
    $SQLServer = "SQLServer\SQLDB"
    $SQLDBName = "Database"
    
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $SQLUser; Password = $SQLPassword;"
    
    $SqlQuery="select Field01, Field02, Field03
        from SQLTable
        where datevalue between '$beginreportdate' and '$endreportdate'"
    
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    
    $DataSet = New-Object System.Data.DataSet
    $rowCount = $SqlAdapter.Fill($DataSet)
    
    $SqlConnection.Close()
    
    $ReportTable = $DataSet.Tables[0]
  • Written by Tim on 30 January 2019:

    Hi,
    I use the script and it works fine except it destroys the German letters: “ä”, “ö”, “ü”, “ß”.
    In the csv-files, they are visible. When I manually open the csv-files to excel, it also works.

    The word “Ausführungsdatum” from csv looks like “Ausführungsdatum” in Excel.

    I could not find the place to put -Encode utf8.

    Is there anyone who can assist?

    Thanks in advance,

    Tim

  • Written by Nuri on 1 February 2019:

    I just want to do text to columns function in my xlsx.I dont want to convert csv to xlsx.How can i do that ? Can you help me ?

  • Written by David on 22 March 2019:

    Fantastic thank you! It’s very fast. I had issues getting the comma delimiter to work, but found that by changing the following it worked nicely (Excel 2016 version (Office Pro Plus))

    From:
    $delimiter=”,”
    $query.TextFileOtherDelimiter = $delimiter

    To:
    $query.TextFileCommaDelimiter = $true

Have your say:

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.