Convert XLSX to CSV in PowerShell

This article will discuss converting Excel Files (XLSX) to CSV using Windows PowerShell. We will see how to convert a single XLSX file, an Excel file with multiple worksheets, and, lastly, work through an example that shows how to convert all XLSX files into a folder to CSV.

The commands we will use in this post can be executed directly from PowerShell or inside a PowerShell script (a file with the extension .ps1). We will, however, be going with the second option in this article.

Converting a Single XLSX File into CSV

To demonstrate concepts in this Section, we will use the XLSX file saved as SalaryData2.xlsx with two sheets: Incomes and PersonalDetails.

Figure 1: A snapshot of the SalaryData2.xlsx file will be used in the examples. The file has two worksheets: Incomes and PersonalDetails.

The XLSX file can be converted into CSV in PowerShell by following these steps:

Step 0: Create a PowerShell Script to execute to perform the conversion

Save the following contents into a PowerShell script called xlsx_to_csv.ps1 (you can give it any other name).

Function ConvertExcelToCsv () {

Pass the parameters to the function.

- SourcePath - the input (A full path to XLSX file),

- DestPath - the outfile (A full path to the output CSV file)

The two parameters must be given. Mandatory = $true

Param

(

[Parameter(Mandatory = $true)] [string] $SourcePath,

[Parameter(Mandatory = $true)] [string] $DestPath

)

<# Start Excel Application that we will use to open and convert XLSX to CSV #>

$Excel = New-Object -ComObject Excel.Application

# Open the XLSX file

$workbook = $Excel.Workbooks.Open($SourcePath)

# @ In the following line, convert PowerShell Object to Array

# the line outputs an Array with all the worksheets in the Excel file

$sheets = @($workbook.Worksheets)

# Getting the first sheet in the list of all sheets at index 0

$sheet = $sheets[0]

# Save the content on DestPath - a CSV file

# Option 6 allows text formatting of the output.

$sheet.SaveAs($DestPath, 6)

$Excel.Quit()

}

# Full path to the XLSX file – input file

$FilePath = "Path\to\XLSX_file"

# Full path to the CSV file – the output file

$DestPath = "Path\to\CSV_file"

# Calling ConvertExcellToCsv function with the two paths as parameters.

ConvertExcelToCsv $FilePath $DestPath

Examples of FilePath and DestPath:

FilePath – C:\Users\kiprono\Desktop\SalaryData2.xlsx
DestPath – C:\Users\kiprono\Desktop\SalaryData2.csv

The ConvertExcelToCsv function takes two parameters: FilePath and DestPath. FilePath is the full path to the XLSX file, and DestPath is the path where the resulting CSV file is saved. The two parameters must be given.

Step 1: Execute the PowerShell Script

To execute the script created in step 0, run the following command on the PowerShell:

1	cd <path_to_directory_containing_the_script> ; .\xlsx_to_csv2.ps1

For example: cd C:\Users\<username>\Desktop ; .\xlsx_to_csv2.ps1

The line above executes two commands separated by a semi-colon (;). The first part changes the working directory to the script folder, and the second part executes the actual script.

Alternatively, if you have PowerShell 2.0, use PowerShell.exe’s -File parameter to invoke a script from another environment, like Command Prompt (cmd.exe). For example, you can run something like this on CMD.

1	Powershell.exe -File path_to_the_script

Example: Powershell.exe -File cd C:\Users\kiprono\Desktop\xlsx_to_csv.ps1

Step 2: Looking at the output

Once you have executed the script, you should be able to find the CSV file in the location defined by the DestPath parameter. In my case, the output file, SalaryData2.csv, has the following truncated content (opened on notepad editor)

ID,income,currency,duration
12182,86519.60854,USD,annually
81213,83085.86498,USD,annually
…
50767,77829.59475,USD,annually

The output shows that SalaryData2.csv is truly CSV – a Comma Separated Values file.

Converting All Excel Files in a Folder into CSV

In this Section, we want to modify the code on the script above to suit the purpose. Let’s save the modified code to xlsx_to_csv3.ps1. Here is the new code

Function ConvertExcelToCsv () {

Pass the parameters to the function.

- SourceFile - XLSX file to be converted,

- DestDir - CSV files will be written to this directory

- Filename - The basename for the XLSX file eg sales in sales.xlsx

This parameter will be used to name CSV dynamically

The two parameters must be given. Mandatory = $true

Param

(

[Parameter(Mandatory = $true)] [string] $SourceFile,

[Parameter(Mandatory = $true)] [string] $DestDir,

[Parameter(Mandatory = $true)] [string] $Filename

)

<# Start Excel Application that we will use to open and convert XLSX to CSV #>

$Excel = New-Object -ComObject Excel.Application

# Open the XLSX file

$workbook = $Excel.Workbooks.Open($SourceFile)

# @ in the following line, convert PowerShell Object to Array

# the output of the line is an Array of all sheets in the XLSX file

$sheets = @($workbook.Worksheets)

# Getting the first sheet in the list of all sheets.

$sheet = $sheets[0]

# DestFile will be concatenation of DestDir, \, basename and ".csv"

$DestFile = $DestDir +"\"+ $FileName + ".csv"

# Save the content on DestFile - a CSV file

# Option 6 allows text formatting of the output.

$sheet.SaveAs($DestFile, 6)

$Excel.Quit()

}

# Get all files with XLSX format in the directory given below

$xlsx_dir = "path_containing_XLSX_files_we_wish_to_convert"

$xlsx_files = Get-ChildItem $xlsx_dir -filter *.xlsx

$DestPath = "path_to_folder_to_hold_CSV_files"

# Loop through each XLSX file found and convert it into csv

foreach($xlsx_path in $xlsx_files)

{

# sorce_xlsx concatenates the source directory, \, and XLSX file name

$source_xlsx = $xlsx_dir +"\"+$xlsx_path

# Filename picks the basename, e.g., sales_data in sales_data.xlsx

$filename = $xlsx_path.BaseName

# Call the function with the three parameters.

ConvertExcelToCsv $source_xlsx $DestPath $filename

}

Once you have saved the above content into a PowerShell script named xlsx_to_csv3.ps1 (or anything else really), proceed to execute it as follows

1	cd "path_to_folder_containing_the_script\" ; .\xlsx_to_csv3.ps1

For example: cd C:\Users\kiprono\Desktop ; .\xlsx_to_csv3.ps1

Note: The last two sections discussed converting a single worksheet of an XLSX file into CSV. In the next Section, let’s see how to convert XLSX with multiple sheets.

Converting XLSX with Multiple Worksheets to CSV

The following Section will modify the script contents in the second Section in a new file, xlsx_to_csv2.ps1. Replace the lines between the line $workbook = $Excel.Workbooks.Open($SourceFile) and $Excel.Quit() with the following contents and execute it as we did before.

# Loop through worksheets on XLSX and save them as csv.

foreach ($sheet in $workbook.Worksheets) {

$DestPath = $DestDir +"\"+ $FileName + "_" + $sheet.Name + ".csv"

$sheet.SaveAs($DestPath, 6)

}

The above content will allow us to loop through all the worksheets in an XLSX file and convert them to CSV. The new CSV file(s) generated will contain the XLSX filename, followed by an underscore (_), then the worksheet name.

For example, consider the SalaryData2.xlsx file introduced at the beginning of the article. It has two worksheets: Incomes and PersonalDetails.

In such a case, the modifications in this Section will result in two CSV files with the following names SalaryData2_Incomes.csv and SalaryData2_ PersonalDetails.csv.

Codeigo

Just programming