Convert XLS to CSV file without having Excel installed.

Convert-Excel-To-Csv.ps1

# Converter use https://code.google.com/p/excellibrary/ to read cells.
# Place .DLL in same folder as this script.
param (
	[parameter(Mandatory=$false)]
	[alias("source")]
	[alias("src")]
	[string]$ExcelFileName,

	[parameter(Mandatory=$false)]
	[alias("destination")]
	[alias("dest")]
	[string]$CsvFileName,

	[parameter(Mandatory=$false)]
	[alias("sheet")]
	[string]$WorksheetName,

	[parameter(Mandatory=$false)]
	[alias("d")]
	[char]$delimiter = ';',

	[parameter(Mandatory=$false)]
	[alias("h")]
	[switch]$help,

	[parameter(Mandatory=$false)]
	[switch]$trace
)

# Globals
$defaultCulture = New-Object System.Globalization.CultureInfo("da-DK");
$forceQuoteStringChars = ("`n`r`"" + $delimiter).ToCharArray();

if($help)
{
	Write-Output "Usage: Convert-Excel-To-Csv -SouRCe file.xls -DESTination file.csv [-sheet SheetName] [-Delimiter Char] [-Help]"
	Write-Output "-Help: Command usage"
	Write-Output "-Source: Excel source file"
	Write-Output "-Destination: Csv destination file"
	Write-Output "-Sheet: Sheet name. Default is first sheet"
	Write-Output "-Delimiter: Character used as field delimiter in csv file. Default is semicolon (;)"
	Exit
}

if ($ExcelFileName -eq "") {
    throw "Please provide path to the Excel file"
    Exit
}

if (-not (Test-Path $ExcelFileName)) {
    throw "Path '$ExcelFileName' does not exist."
    Exit
}

if ($CsvFileName -eq "") {
    throw "Please provide path to the CSV file"
    Exit
    }

function trace {
	param($message)
	if($trace) { Write-Host $message }
}

function Format-Cell-Date {
	param($cell,$format,$culture)
	
	Try
	{
		switch($cell.Value.GetType().FullName)
		{
			"System.String" { $cell.StringValue; }
			"System.Double" { $cell.DateTimeValue.ToString($format,$culture) }
	        default {
				Write-Error ("Unhandled type at cell: $rowIndex,$colIndex : " + $cell.Format.FormatString)
				exit
			}
		}
	}
	Catch
	{
		$cell.StringValue;
	}
}

$customFormats = @{};
function Get-Custom-Format {
	param([string]$format)
	
	$customFormat = $customFormats.Get_Item($format);
	
	if($customFormat -eq $null) {
		if($format -match "^(\[\$\-(?<loc>.*)\])?(?<fmt>.*)\;@$") {
			$loc = $matches.Get_Item("loc");		
			$fmt = $matches.Get_Item("fmt");		
			
			if($loc -eq $null) {
				$culture = $defaultCulture;
			} else {
				$culture = New-Object System.Globalization.CultureInfo([Convert]::ToInt32($loc, 16));
			}
			
			switch ($fmt) 
			{
				"dd\ mmmm\ yyyy" { $specifier = "D" }
				"dd\mmmm\yyyy" { $specifier = "D" }
				"dd/mm/yyyy" { $specifier = "d" }
		        default {
					return $null
				}
			}
			
			$customFormat = @{ culture = $culture; specifier = $specifier };
			$customFormats.Add($format, $customFormat);
		}
	}
	
	return $customFormat;
}


#load ExcelLibrary DLL
$scriptPath = Split-Path $MyInvocation.MyCommand.Path
trace "scriptPath: $scriptPath"
$assemblyLocation = Join-Path $scriptPath "ExcelLibrary.dll "
[Reflection.Assembly]::LoadFrom($assemblyLocation) | Out-Null

# open xls file
$book = [ExcelLibrary.SpreadSheet.WorkBook]::Load($ExcelFileName);

#select sheet
if ($WorksheetName) {
	$sheet = $book.Worksheets | ? {$_.Name -eq $WorkSheetName};
} else {
	$sheet = $book.Worksheets[0];
}

$stream = New-Object System.IO.StreamWriter($CsvFileName,$false,[System.Text.Encoding]::Default)

#Mesure cols
$cols = 0;
for ($rowIndex=$sheet.Cells.FirstRowIndex; $rowIndex -le $sheet.Cells.LastRowIndex; $rowIndex++) {
	$row = $sheet.Cells.GetRow($rowIndex);
	if($cols -lt $row.LastColIndex) { $cols = $row.LastColIndex }
}

$line = New-Object String($delimiter,$cols);
for ($rowIndex=0; $rowIndex -lt $sheet.Cells.FirstRowIndex; $rowIndex++) {
	$stream.WriteLine($line);
}

$lineStringBuilder = New-Object System.Text.StringBuilder

# traverse rows by Index
for ($rowIndex=$sheet.Cells.FirstRowIndex; $rowIndex -le $sheet.Cells.LastRowIndex; $rowIndex++) {
	$row = $sheet.Cells.GetRow($rowIndex);
	$dummy = $lineStringBuilder.Clear();
	
	for ($colIndex=0; $colIndex -le $cols; $colIndex++) {
		$value = "";
		if($colIndex -ge $row.FirstColIndex -and $colIndex -le $row.LastColIndex) {
        	$cell = $row.GetCell($colIndex);
			if(-not $cell.IsEmpty) {
			
				switch ($cell.Format.FormatType) 
    			{ 
        			"General" {$value = $cell.StringValue;} 
        			"Custom" {
							$customFormat = Get-Custom-Format $cell.Format.FormatString	
							if($customFormat -eq $null) {
								Write-Error ("Unhandled custom format string at cell: $rowIndex,$colIndex : " + $cell.Format.FormatString)
								exit
							}
							$value = Format-Cell-Date $cell $customFormat.specifier $customFormat.culture
						}
						
					"Date" {
							switch ($cell.Format.FormatString) 
			    			{
								"m/d/yy" { $value = Format-Cell-Date $cell "d" $defaultCulture }
						        default {
										Write-Error ("Unhandled date format string at cell: $rowIndex,$colIndex : " + $cell.Format.FormatString)
										exit
									}
							}
						}
			        default {
							Write-Error ("Unkbown format type at cell: $rowIndex,$colIndex : " + $cell.Format.FormatType)
							exit
						}
    			}
			}				
		}
		
		if($value.IndexOfAny($forceQuoteStringChars) -ne -1) {
			$value = "`"" + $value.Replace("`"","`"`"") + "`"";
		}
		
		$dummy = $lineStringBuilder.Append($value); 
		$dummy = $lineStringBuilder.Append($delimiter);
	}

	#remove last delimiter
	$lineStringBuilder.Length--;
	
	#write line to csv file
	$stream.WriteLine($lineStringBuilder);
}  
$stream.Close();
$stream.Dispose();

Hello Philip,

I came across your code while finding a way to convert an XLS file to CSV.
I've downloaded the DLL and put it on the same folder, but it's not working on Windows Server 2012

I'm receiving this error:

http://i.imgur.com/Cg7B6nT.png

Can you help me?

Thank you very much,

Adrià Sánchez