Posted on: July 23, 2020 Posted by: ndconsultingllc Comments: 0

Excel – Multiple Tabs to CSV

Using PowerShell, it’s very easy to separate an Excel workbook with multiple tabs into separate CSV files or separate Excel files.

In the below example, I have an Example workbook that has about 50 tabs. I want each tab to be a separate CSV file.

By running this PowerShell script you can easily separate each tab into it’s own file.

                                  

PowerShell Script

Function ExportWSToCSV ($excelFileName, $csvLoc)
{
	$excelFile = "C:\Temp\" + $excelFileName + ".xlsx"
	
	$E = New-Object -ComObject Excel.Application
	
	$E.Visible = $false
	$E.DisplayAlerts = $false
	$wb = $E.Workbooks.Open($excelFile)
	
	foreach ($ws in $wb.Worksheets)
	{
		$var = $excelFileName
		$result = $var.SubString(0,3)
		$n = $ws.Name
		$ws.SaveAs($csvLoc + $result + "_" + $n + ".csv", 6)
		$newFilename = $csvLoc+$result+"_"+$n+".csv"
		$newOutFilename = $csvLoc+"load"+$result+"_"+$n+".csv"
		
		Import-CSV $newFilename | ConvertTo-CSV -NoTypeInformation -Delimiter "|" | Out-File $newOutFilename
	}
}

$ens = Get-ChildItem "C:\Temp\" -filter *.xlsx

foreach($e in $ens)
{
	ExportWSToCSV -excelFileName $e.BaseName -csvLoc "C:\Temp\"
	stop-process -processname EXCEL
}
Excel – Multiple Tabs to CSV was last modified: July 23rd, 2020 by ndconsultingllc
Categories:Uncategorized

Leave a Comment