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.

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.

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

51 responses to “Convert CSV to XLSX via PowerShell”

On 23 February 2017, Milan wrote:

Good work!

Very useful, it works exactly as described, thanks!

On 3 April 2017, Axel Öhman wrote:

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.

On 9 July 2018, c3p0 wrote:

Just add
$query.TextFilePlatform = 65001
worked for me

On 9 June 2017, Harun wrote:

You are gorgeous, million Thanks

On 21 June 2017, Boulavogue wrote:

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

}

On 8 August 2017, Menno Bakker wrote:

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

On 22 February 2018, Jean wrote:

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

On 16 April 2018, Jan wrote:

Change TextFileColumnDataTypes from 1 to 2

On 16 March 2018, Jean-Francois wrote:

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

On 9 July 2018, c3p0 wrote:

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 }

On 16 April 2018, warren wrote:

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?

On 9 July 2018, c3p0 wrote:

You can overwrite by adding this:

$excel.DisplayAlerts = $False

On 14 May 2018, Rahul Udayabhanu wrote:

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

On 9 July 2019, Brady wrote:

Excel needs to be registered as an interactive user in DCOM for automation to work. The following articles helped me with this problem:

https://stackoverflow.com/questions/22062284/server-execution-failed-exception-from-hresult-0x80080005-co-e-server-exec-fa/39725040#39725040

https://blogs.technet.microsoft.com/the_microsoft_excel_support_team_blog/2012/11/12/microsoft-excel-or-microsoft-word-does-not-appear-in-dcom-configuration-snap-in/

On 30 May 2018, Olaf wrote:

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

On 9 July 2018, c3p0 wrote:

It is for the filetype. Just look at these articles from msdn:

https://msdn.microsoft.com/de-de/vba/excel-vba/articles/xlfileformat-enumeration-excel
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-saveas-method-excel

51 is the code for open xml *.xlsx format

On 30 May 2018, igb528 wrote:

It worked! Ty.

On 24 October 2018, Julie wrote:

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

On 5 December 2018, Mel wrote:

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

On 17 December 2018, Oldiki wrote:

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.

On 16 January 2019, Jason wrote:

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]

On 30 January 2019, Tim wrote:

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

On 1 February 2019, Nuri wrote:

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 ?

On 22 March 2019, David wrote:

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

On 24 April 2019, Swati wrote:

Hello, I am very new to powershell scripting..Above mentioned code works for copying csv to xls file. But entire data in csv which is in comma separated format gets saved in xls single column. How can I split the column into multiple columns pls?

On 6 June 2019, Sibonelo Duma wrote:

i want to extract specific rows ( say 5 row in the middle) from the csv to xlsx, how can i do it? everything works well but can’t get the required rows only

On 2 July 2019, Daniel wrote:

Hello,

work great for me. But… :)
I thought It will hepl me to workaround issue that excel can show only one bilion entries. Because of that I couldn’t convers my csv file (4 bilions). Script worked, but in xlsx file is only onie bilion entries. Is there any way to break down this limitation ?

Thank you in advance, and great job!
Daniel.

On 16 August 2019, robert wrote:

Can you please edit the script to concert ALL csvs in a folder to ONE single xlsx where each csv is one worksheet ?

On 9 September 2019, Sush wrote:

If the same thing I want for single or 2 columns then what should be the changes.

On 20 September 2019, Lacerda wrote:

Can you please edit the script to make a coffe after csv-> xlsx convert?
lmao Some people have no concept of ridicule.

great script!

On 5 October 2019, Vlad the Installer wrote:

Fantastic! Saved my bacon! thank you!

On 17 October 2019, Stewart Grainger wrote:

Does this require Excel to be installed, I wish to run on a server as a scheduled task?

On 11 November 2019, John wrote:

Great work everyone! The original script works like a charm and the batch script/overwrite function/delete function work great as well.

Appreciate everyones input here! you are all super stars :)

On 6 February 2020, Drew wrote:

Works great. Just what I needed. I appreciate you.

On 7 April 2020, Bruce wrote:

Excellent article. This helped me understaand how to convert csv to xlsx. I’ve parsed csv’s before but the converting was giving me fits. No more fits. Thank-You! :-)

On 10 December 2020, Robin wrote:

It is almost exactly what I need, the only hiccup is it does not take into account text qualifiers from the csv. I have files that have part numbers, some of which have leading 0s. e.g. “00073” is converted to 73. Changing the format to text (Change TextFileColumnDataTypes from 1 to 2) as posted by Jan does mean that the leading 0s are treated correctly, but does mean all other numeric fields values are treated as text. Is there a way to use text qualifiers, or to specify that only first column is treated as text?

On 28 November 2021, Tom wrote:

You’re an absolute legend, this has really saved me from the issue i was plagued with.
i was exporting a csv from using powershell sqlcmd module. as soon as i opened the file in excel, leading zeroes were being trimmed off cells.
your fix allows me to set the columns that contain these cells to a TextFileColumnDataType of 2, which maintains leading zeroes.
all of this massively helps with my automation effort

On 6 December 2021, Alwani wrote:

Can someone share me how to export XLS to CSV with specific data collection from certain rows

On 3 January 2022, Vittor de Castro wrote:

I’d use the import excel module, it has a lot of functionalities that are really polished and well thought https://www.powershellgallery.com/packages/ImportExcel/7.1.0

On 14 December 2021, Stefan Zuban wrote:

Excellent post.Thank you. My issue is that when I set the script as a task with the task scheduler on a Widows server 2019, it won’t work. I inserted a message msg … in the script so I could actually check the task is running in the test period, I see the message so I get visual confirmation the task is running the script, but there is no conversion. Of course, the script works fine from PowerShell. What could the issue be ?

On 3 January 2022, Vittor de Castro wrote:

Hey Adam, first of all thanks for the great content, your site as insta favorite on my browser as soon as I got here. Do you happen to have problems with this code you posted while having another instance of excel open on your desktop? Sometimes when the excel application is oppened this code simply will not work without giving any error messages

On 11 March 2022, Amanda wrote:

You are awesome!!! Beautiful work! Thank you for sharing this script!

On 6 August 2022, Nishu wrote:

Hi Adam, first of all thanks for the useful content. I am new to Perl scripting.

I have a .csv file and need to convert into .xlsx. I am referring you script.

But getting below error. It would be really helpful If you helped me too resolve the errors.

I have placed the file “Test_perl.pl” at below location in Unix server but getting below error.

(/eis/EIS_INFA_PCS_UATC/SrcFiles/IFRS17/ManData/Intact) $ perl Test_perl.pl
Unquoted string "application" may clash with future reserved word at Test_perl.pl line 26.
Unquoted string "worksheets" may clash with future reserved word at Test_perl.pl line 28.
Unquoted string "name" may clash with future reserved word at Test_perl.pl line 33.
Can't modify concatenation (.) or string in scalar assignment at Test_perl.pl line 34, near "$delimiter;"
syntax error at Test_perl.pl line 36, near "= ,"
Execution of Test_perl.pl aborted due to compilation errors.
rpwqs494@stha7n0gx(/eis/EIS_INFA_PCS_UATC/SrcFiles/IFRS17/ManData/Intact) $

Using below script

#Define locations and delimiter
$csv = "/eis/EIS_INFA_PCS_UATC/SrcFiles/IFRS17/ManData/Intact/input.csv"; #Location of the source file

$xlsx = "/eis/EIS_INFA_PCS_UATC/SrcFiles/IFRS17/ManData/Intact/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()

On 6 August 2022, Nishu wrote:

continue to previous comment
I tried to debug the errors and able to resolve few error but still getting below error.

I am using , delimiter in the csv file

Error:
/eis/EIS_INFA_PCS_UATC/SrcFiles/IFRS17/ManData/Intact) $ perl Test_perl.pl
Can’t modify concatenation (.) or string in scalar assignment at Test_perl.pl line 34, near “$delimiter;”
Execution of Test_perl.pl aborted due to compilation errors.
rpwqs494@stha7n0gx(/eis/EIS_INFA_PCS_UATC/SrcFiles/IFRS17/ManData/Intact) $

Using below script

#Define locations and delimiter

$csv = "/eis/EIS_INFA_PCS_UATC/SrcFiles/IFRS17/ManData/Intact/input.csv"; #Location of the source file
print "Hello, ${csv}";

$xlsx = "/eis/EIS_INFA_PCS_UATC/SrcFiles/IFRS17/ManData/Intact/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();

On 11 August 2022, Luke wrote:

Hi, thx for the great article. Is there a way to also change the decimal seperator and Thousands separator?

On 25 May 2023, Herooftheday wrote:

tragic not working with greek language characters and i see no columns

Convert CSV to XLSX via PowerShell

Comments

Have Your Say