I have a PS script (called by a SQL job) that queries a SQL DB and writes the result to an Excel file using the Export-Excel function of the PS module ImportExcel. It works great for mid-sized datasets, but it is very slow for larger ones. I suspect this has a lot to do with having to first load query results into a System.Data.DataTable, so I modified Export-Excel to accept a System.Data.IDataReader. This was a huge performance improvement, but I can only get it to work when I explicitly pass a System.Data.IDataReader using the InputObject parameter of the Export-Excel function. Whenever I attempt to pass it via the pipeline, it writes an empty Excel file. To make sure that the pipeline section of the code was working correctly, I moved it to the begin block of the function that runs when an InputObject is passed in explicitly, and it worked without issue.
This works:
Export-Excel -InputObject $reader -Path $filePath -NoNumberConversion * -FreezeTopRow -AutoFilter -MoveToStart
This Fails:
$reader | Export-Excel -Path $filePath -NoNumberConversion * -FreezeTopRow -AutoFilter -MoveToStart
I would like to include the code for the entire function, but unfortunately, my question would exceed the 30,000 character limit. That said, below is the offending code from within the process block:
if ($null -ne $InputObject -and ($null -ne $InputObject.GetType().GetInterface("IDataReader")) ) {
# get field names the first time around
if ($firstTimeThru) {
$firstTimeThru = $false
$script:Header = @()
for ($j = 0; $j -lt $InputObject.FieldCount; $j++) {
$script:Header += $InputObject.GetName($j)
}
# region Write Headers to worksheet
if ($DisplayPropertySet -and ($null -ne $ExcludeProperty) -and ($ExcludeProperty.Length -gt 0) ) {
$script:Header = $script:Header | Where-Object { $_ -notin $ExcludeProperty }
}
if ( ($null -ne $ExcludeProperty) -and ($ExcludeProperty.Length -gt 0)) {
foreach ($exclusion in $ExcludeProperty) {
$script:Header = $script:Header -notlike $exclusion
}
}
if ($NoHeader) {
# Don't push the headers to the spreadsheet
$row -= 1
}
else {
$ColumnIndex = $StartColumn
foreach ($Name in $script:Header) {
$ws.Cells[$row, $ColumnIndex].Value = $Name
Write-Verbose "Cell '$row`:$ColumnIndex' add header '$Name'"
$ColumnIndex += 1
}
}
#endregion
}
#region Add databody values
$row++
$ColumnIndex = $StartColumn
foreach ($Name in $script:Header) {
$j = $InputObject.GetOrdinal($Name)
$fieldType = $InputObject.GetFieldType($j)
$fieldValue = $null
try {
switch ($fieldType) {
{ $_ -is [Int32] } {
$fieldValue = $InputObject.GetInt32($j); break
}
{ $_ -is [String] } {
$fieldValue = $InputObject.GetString($j); break
}
{ $_ -is [Boolean] } {
$fieldValue = $InputObject.GetBoolean($j); break
}
{ $_ -is [DateTime] } {
$fieldValue = $InputObject.GetDateTime($j); break
}
{ $_ -is [TimeSpan] } {
$fieldValue = [TimeSpan]$InputObject.GetValue($j); break
}
{ $_ -is [Decimal] } {
$fieldValue = $InputObject.GetDecimal($j); break
}
{ $_ -is [Double] } {
$fieldValue = $InputObject.GetDouble($j); break
}
{ $_ -is [Boolean] } {
$fieldValue = $InputObject.GetBoolean($j); break
}
{ $_ -is [Float] } {
$fieldValue = $InputObject.GetFloat($j); break
}
{ $_ -is [Single] } {
$fieldValue = $InputObject.GetFloat($j); break
}
{ $_ -is [Int64] } {
$fieldValue = $InputObject.GetInt64($j); break
}
{ $_ -is [Int16] } {
$fieldValue = $InputObject.GetInt16($j); break
}
{ $_ -is [Byte] } {
$fieldValue = [int]$InputObject.GetByte($j); break
}
{ $_ -is [Char] } {
$fieldValue = $InputObject.GetChar($j).ToString(); break
}
{ $_ -is [Guid] } {
$fieldValue = $InputObject.GetGuid($j).ToString(); break
}
{ $_ -is [Object] } {
$fieldValue = $InputObject.GetValue($j); break
}
default {
throw "Unsupported field type: $($fieldType.FullName)"
}
}
if ($InputObject.IsDBNull($j)) { $fieldValue = $null }
if ($fieldType -is [DateTime]) {
$ws.Cells[$row, $ColumnIndex].Value = $fieldValue
$ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = 'm/d/yy h:mm' # This is not a custom format, but a preset recognized as date and localized.
}
elseif ($fieldType -is [TimeSpan]) {
$ws.Cells[$row, $ColumnIndex].Value = $fieldValue
$ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = '[h]:mm:ss'
}
elseif ($fieldType -is [System.ValueType]) {
$ws.Cells[$row, $ColumnIndex].Value = $fieldValue
if ($setNumformat) { $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
}
elseif ($fieldType -isnot [String] -or $null -eq $fieldValue ) {
#Other objects or null.
if ($null -ne $fieldValue ) { $ws.Cells[$row, $ColumnIndex].Value = $fieldValue.ToString() }
}
elseif ($fieldValue[0] -eq '=') {
$ws.Cells[$row, $ColumnIndex].Formula = ($fieldValue -replace '^=', '')
if ($setNumformat) { $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
}
else {
if ( $NoHyperLinkConversion -ne '*' -and # Put the check for 'NoHyperLinkConversion is null' first to skip checking for wellformedstring
$NoHyperLinkConversion -notcontains $Name -and
[System.Uri]::IsWellFormedUriString($fieldValue, [System.UriKind]::Absolute)
) {
if ($fieldValue -match "^xl://internal/") {
$referenceAddress = $fieldValue -replace "^xl://internal/" , ""
$display = $referenceAddress -replace "!A1$" , ""
$h = New-Object -TypeName OfficeOpenXml.ExcelHyperLink -ArgumentList $referenceAddress , $display
$ws.Cells[$row, $ColumnIndex].HyperLink = $h
}
else { $ws.Cells[$row, $ColumnIndex].HyperLink = $fieldValue }
$ws.Cells[$row, $ColumnIndex].Style.Font.Color.SetColor([System.Drawing.Color]::Blue)
$ws.Cells[$row, $ColumnIndex].Style.Font.UnderLine = $true
}
else {
$number = $null
if ( $NoNumberConversion -ne '*' -and # Check if NoNumberConversion isn't specified. Put this first as it's going to stop the if clause. Quicker than putting regex check first
$numberRegex.IsMatch($fieldValue) -and # and if it contains digit(s) - this syntax is quicker than -match for many items and cuts out slow checks for non numbers
$NoNumberConversion -notcontains $Name -and
[Double]::TryParse($fieldValue, [System.Globalization.NumberStyles]::Any, [System.Globalization.NumberFormatInfo]::CurrentInfo, [Ref]$number)
) {
$ws.Cells[$row, $ColumnIndex].Value = $number
if ($setNumformat) { $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
}
else {
$ws.Cells[$row, $ColumnIndex].Value = $fieldValue
}
}
}
}
catch { Write-Warning -Message "Could not insert the '$Name' property at Row $row, Column $ColumnIndex" }
$ColumnIndex += 1
}
#endregion
}
What am I missing here?
As mentioned in the comments, when PowerShell's pipeline processor enumerates an IDataReader, the enumerated contents are all of type IDataRecord, not IDataReader.
Change the condition to test for IDataRecord and then change behavior based on whether the input object is also an IDataReader:
if ($PSBoundParameters.ContainsKey('InputObject') -and $InputObject -is [System.Data.IDataRecord]) {
if ($firstTimeThru) {
$firstTimeThru = $false
# do the header parsing and preparation like before
if ($InputObject -is [System.Data.IDataReader]) {
# special case - someone passed a reader by name, we need to manually enumerate
# place a `while($InputObject.Read()){...}` here, or recurse
}
}
}
You'll probably want to split some of the subsequent code into separate functions so you can reuse it regardless of whether you're processing 1 record or a whole set at a time
I should add that your type-switch in the middle of the sample code you've provided probably isn't going to work as you expect - the value returned by IDataRecord.GetFieldType() is already of type [type], so the -is operator won't work here - you need to either use -eq for an explicit comparison, or wrap the target type literals used as case labels in $(...) to prevent them being parsed as string literals:
$fieldValue = switch ($fieldType) {
$([int]) { $InputObject.GetInt32($j); break }
# or with `-eq`
{ $_ -eq [string] } { $InputObject.GetString($j); break }
}