3

I have a CSV file that is used as a resource by other files. It is expected that this CSV file will always have a header row at the very minimum which specifies the expected columns. Not having this header row causes errors in the other files.

In my situation I will have some occasions where there are no results but I still need the header row. Does anyone know how to force this header row to be written to the CSV file even if there are no results?

Here is the gist of the command.

Set-Location c:

Invoke-Sqlcmd -Query "SELECT *
FROM MY_TABLE my;" -ServerInstance "myserver.fake" -Database "db" -Username "user" -Password "password" -Querytimeout 65535 | Export-Csv -Path "\\MyDirectory\MyResults.csv" -NoTypeInformation
Zoe
  • 27,060
  • 21
  • 118
  • 148
Jonathan Otter
  • 41
  • 6
  • Does this answer your question? [Data extraction using powershell, CSV file extract to have column header when 0 rows in sql server table.](https://stackoverflow.com/questions/43053469/data-extraction-using-powershell-csv-file-extract-to-have-column-header-when-0) – iRon Sep 02 '21 at 16:18

1 Answers1

1

Tip of the hat to Bacon Bits and AdminOfThings for their help.

Create the CSV header row manually, using plain-text processing.

The following assumes that the output CSV file path is stored in variable $outFile:

if ((Get-Item -LiteralPath $outFile).Length -eq 0) { # Nothing was exported.

  # Define the array of column names.
  # Ideally, these should come from a different SQL query
  # that returns the column names of MY_TABLE.
  $columnNames = 'Foo', 'Bar', 'Baz'

  # Transform the to a CSV header line and write to the target file.
  $columnNames.ForEach({ "`"$_`"" }) -join "," |
    Set-Content -LiteralPath $outFile

}

Note:

  • If the query targets only a single table / view, as in your example, you can obtain its column names via Read-SqlTableData / Read-SqlViewData, as shown in the next section.

The above stores the following in the output file, if it was found to be empty after exporting (due to the query returning no rows):

"Foo","Bar","Baz"

A solution limited to reading data from a single table (unconditionally, though the count of rows can be limited), using Read-SqlTableData (or, analogously, using Read-SqlViewData for a single view):[1]

If you pass -OutputAs DataTable (or -As DataTable) to a Read-SqlTableData call, it will return a System.Data.DataTable instance, which provides access to not just the table's data (rows, via .Rows), but also the table structure (column names, via .Columns.ColumnName).

  • Note: Regrettably, even though Invoke-SqlCmd also supports -OutputAs DataTables (note the plural s, though), it returns nothing rather than a DataTable instance if the query happens to return no rows.
# Get table MY_TABLE as a DataTable instance.
$dataTable = 
  Read-SqlTableData -OutputAs DataTable -TableName MY_TABLE -ServerInstance "myserver.fake" -Database "db" -Username "user" -Password "password" -Querytimeout 65535

if ($dataTable.Rows.Count -gt 0) {  # At least 1 row found, export it.

  $dataTable.Rows | Export-Csv -Path "\\MyDirectory\MyResults.csv" -NoTypeInformation

} else { # No query results - write the header row (only).

  # Create the header row manually, using plain-text processing.
  $dataTable.Columns.ColumnName.ForEach({ "`"$_`"" }) -join "," |
    Set-Content -LiteralPath $outFile

}

[1] As Bacon Bits points out, there's really no good reason for separate cmdlets to support tables vs. views.

mklement0
  • 382,024
  • 64
  • 607
  • 775