Table of Contents
The xlsx file can be easily converted into csv using the following methods:
Before converting a file, please ensure that your PowerShell is opened in the administrative mode, and the execution policy should not be set to Restricted.
|
1 2 3 |
Get-ExecutionPolicy |
|
1 2 3 |
Set-ExecutionPolicy Restricted |
In the above code, we used the Get-ExecutionPolicy command to check if the execution policy is set to Restricted and found that it is set to restricted mode, as shown in the output. So now, our job is to set it to RemoteSigned. So for this purpose, we run the following commands to set it to RemoteSigned and recheck it.
|
1 2 3 4 |
Set-ExecutionPolicy RemoteSigned Get-ExecutionPolicy |
|
1 2 3 |
RemoteSigned |
As the execution policy is now changed to RemoteSigned, the xlsx file can be easily converted into a csv file using the following methods.
Using ImportExcel Module
To convert XLSX to CSV in PowerShell:
- Import ImportExcel module
- Use
Import-excelcmd to read xlsx and convert it to table like custom objects. - Pipe table like custom objects to Export-Csv to convert it to CSV File.
|
1 2 3 4 5 |
Import-Module -Name ImportExcel $data = Import-Excel -Path "C:\path\to\file.xlsx" $data | Export-Csv -Path "C:\path\to\output.csv" -NoTypeInformation |
The Import-Module was used to import the ImportExcel module. The Import-Excel cmdlet was used to import the data from the specified Excel file, while the Export-Csv cmdlet exported the data to a given CSV file.
We imported the ImportExcel module and loaded the data of the Excel file into a PowerShell variable named data. Then assign the correct path to the location of the xlsx file; after that, export the data to a csv file without including type information and also give the file name along with the path where the file should be located. Then open the specified location and get the new csv file as output.
Using Excel.Application COM Object
To convert xlsx to csv in PowerShell:
- Create object of type
Excel.Application - Use
open()method to read excel file into workbook. - Use
SaveAs()method to save file ascsvby passing 6 as it represents csv format.
|
1 2 3 4 5 6 7 |
$excel = New-Object -ComObject Excel.Application $workbook = $excel.Workbooks.Open("C:\path\to\file.xlsx") $workbook.SaveAs("C:\path\to\output.csv", 6) # 6 is the file format for CSV $workbook.Close() $excel.Quit() |
First, using the COM Object a new instance of Excel.Application is created. The new instance is then assigned to a Powershell variable named excel.
Then by using excel open the workbook from the specific action. After that, the workbook is saved as csv by giving two arguments to the SaveAs function. The first argument is the file’s location, while the second argument is set to 6, which indicates the file type that is CSV. Then closed the workbook and quieted the Excel instance of Excel.Application.
Further reading:
Using OleDbDataAdapter Class
Use the OleDbDataAdapter class to convert xlsx to csv in PowerShell.
|
1 2 3 4 5 6 7 8 9 10 11 |
$connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\file.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'" $conn = New-Object System.Data.OleDb.OleDbConnection($connStr) $conn.Open() $cmd = New-Object System.Data.OleDb.OleDbCommand("SELECT * FROM [Sheet1$]", $conn) $adapter = New-Object System.Data.OleDb.OleDbDataAdapter($cmd) $dataset = New-Object System.Data.DataSet $adapter.Fill($dataset) $dataset.Tables[0] | Export-Csv -Path "C:\path\to\output.csv" -NoTypeInformation $conn.Close() |
This method converts the xlsx file into a csv file using the OleDb data provider. First, the connection is established by creating a connection string $connStr that contains the information like source file, properties like excel format, and the header value set to YES in this case.
Now, by using the $connStr string, a new OleDbconnetion object is created. After creating a connection object, open the connection to start the conversion. Next, the new object named $cmd is created to select the data from the Excel sheet using the command SELECT * FROM [Sheet1$].
After that, the $OleDbDataAdapteradapter is created, which is used to fill the data extracted from the file in the previous command. The extracted data is stored in the object $dataset using the Fill() function; the dataset is filled with the data extracted from the Excel file. Finally, the data is exported to the csv file using the Export command. The csv file is saved to the specified location, and finally, the connection is closed using the Close() function.
That’s all about how to convert xlsx to csv in PowerShell.
