PowerShell execute SQL Query, Formatting output to excel. Losing some data from the output

Question

I have a sql query I am running within Powershell, that exports the results to an excel workbook with multiple spreadsheets. I have most of my issues figured out now, however I have one rather large problem.

The result should return values that fill 3 columns. The query works outside of PowerShell and returns the expected result, but I am losing the results of the third column. It may be something obvious to the more seasoned, but I'm having a hard time.

I've been looking for a way to echo or output the result of my query to the screen from PowerShell to help me in determining where I am losing the data, but no dice yet.

Here is the script:

$DSN='mydsn'
$DirectoryToSave = 'c:\report\'

$SQL1=@"

My Working Query

"@

if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing 
  { 
  New-Item "$DirectoryToSave" -type directory | out-null 
  } 

$excel = New-Object -ComObject excel.application
$excel.visible = $True
$excel.DisplayAlerts = $False
$xl=$excel.Workbooks.Add()
$xl.Worksheets.Add()
$xl.Worksheets.Add()
$s1=$xl.sheets.Item(1)
$ws1 = $xl.sheets | where {$_.name -eq 'Sheet1'}
$ws1.name = $Project


$qt = $ws1.QueryTables.Add("ODBC;DSN=$DSN", $ws1.Range("A1"), $SQL1)  


if ($qt.Refresh()){
    $ws1.Activate()
    $ws1.Select()
    $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
    $excel.Rows.Item(1).VerticalAlignment = $xlTop
    $excel.Rows.Item("1:1").Font.Name = "Calibri" 
    $excel.Rows.Item("1:1").Font.Size = 11 
    $excel.Rows.Item("1:1").Font.Bold = $true
    $excel.Columns.item("A:C").EntireColumn.AutoFit()
    $excel.Columns.item("B").NumberFormat=("$#,##0.00")

}

The values in the third colum should be numeric, and are normally returned as a 4 digit values separated by commas. each single cell can have an wide range of values returned. This is why I autofit with "$excel.Columns.item("A:C").EntireColumn.AutoFit()".

Anyone have any idea where I am going wrong?

Thanks In advance!

Answer 1

For now, wrap a CAST to VARCHAR around the STUFF part of the query from your previous question, i.e.:

SELECT 
      t.CUSTOMER, 
      SUM(t.REVENUE) as REVENUE,
      CAST(
          STUFF(
                  (
                     SELECT ',' + Cast(s.work_order as varchar)
                     FROM tblname s
                     WHERE s.CUSTOMER = t.CUSTOMER
                     FOR XML PATH('')
                  ),1,1,''
          ) AS VARCHAR
      ) AS [WORK Order]
FROM tblname t
Group BY CUSTOMER

I can't explain why this works at the moment, but I suspect it's down to some string/character type or encoding funkyness going on in the ODBC driver. I'll try and delve deeper when I have more time.