Convert multiple sheet MS-Excel file to CSV (Powershell)

File type conversion is very easy in Windows Powershell scripting, Concept of convert the file type is quite simple in MSExcel, especially when we are talking to convert in CSV format.

In normal way it’s a very easy process

• Open file in MS-Excel

• -> File

-> Save As

-> choose csv type to save as

In general this is very easy to do process if its only one file, but in case of multiple files, it’s a long process to open each file one by one and save as csv, or if it’s a multiple sheet excel workbook then you have to select each and every sheet and save as csv.

Solution

In powershell we can automate the same sequence of process, with the capability of calling COM objects, Below is the poweshell script which will open one excel workbook , navigate through each worksheet save as csv file by using the sheet name as new csv filename, and then will close the file.

Powershell Script

##############################################

cls

$ExcelFolder = "C:\\powershell\csv_convert"

$sCSVFileType = ".csv"

$ExcelFile= $ExcelFolder+"\employee.xlsx"

$sCSVFile = "c:\powershell\csv_convert\employee"

$oExcel = New-Object -ComObject "Excel.Application" # Get COM Object

$oExcel.Visible = $true # Should Excel be visible?

# and open excel file

$oExcelDoc = $oExcel.Workbooks.Open($ExcelFile)

$Sheetcount = $oExcelDoc.worksheets.count #Count of total Worksheets

foreach($element in 1..$Sheetcount)

{

  $oWorksheet = $oExcelDoc.Worksheets.item($element)  #open  worksheet

  $worksheetname = $oWorksheet.name         #getting name of worksheet         

  $oWorksheet.Activate() #activate sheet

  $oExcelDoc.SaveAs($sCSVFile+$worksheetname+$sCSVFileType,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVWindows)

  Start-Sleep –Seconds 1

}

##################### Using Save as method to convert opened xlsx file

####### Closing Excel Document

$oExcelDoc.Close($false)

Start-Sleep 1

# Cleanup COM

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oWorksheet)|out-null

$oWorksheet=$null

Start-Sleep 1

# Cleanup COM

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcelDoc)|out-null

$oExcelDoc=$null

# Close Excel

$oExcel.Quit()

Start-Sleep 1

# Cleanup COM

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcel)|out-null

$oExcel=$null

[GC]::Collect()

[GC]::WaitForPendingFinalizers()

##############################################

We have an MS-Excel file named employee.xlsx , which have employee data for 3 different states each in different worksheets named(CT, IL, VA), when we ran the script it will create 3 different csv files named(employeeCT, employeeIL, employeeVA)

Other powershell related blogs
powershell windows service maintenance