Extract sql data in psh

• Sqladmin1

  SSC Enthusiast

  Points: 176

  More actions

  October 2, 2025 at 10:52 am

  #4663755

  # Load ImportExcel module

  Import-Module ImportExcel -ErrorAction Stop

  # File paths

  $outFile = "C:\Reports\SQLReport.xlsx"

  # SQL Server info

  $server = "MyServer\SQL2019"

  $db = "MyDB"

  # SQL query

  $sql = @"

  SELECT TOP 10 *

  FROM Sales

  WHERE OrderDate >= '2025-01-01';

  "@

  # Run the SQL query and convert to objects

  $data = sqlcmd -S $server -d $db -E -W -s"," -Q $sql | ConvertFrom-Csv

  # --- Sheet1: Query results ---

  $data | Export-Excel $outFile `

  -WorksheetName "Results" `

  -TableName "ResultsTable" `

  -TableStyle Medium5 `

  -AutoSize `

  -BoldTopRow

  # --- Sheet2: SQL code in single cell A1 ---

  # Convert SQL to single string with Excel line breaks

  $sqlWrapped = $sql -replace "r?n", "`n"

  # Export SQL code to Excel in A1

  @([PSCustomObject]@{ SQL = $sqlWrapped }) |

  Export-Excel $outFile `

  -WorksheetName "SQLCode" `

  -AutoSize `

  -Append `

  -NoHeader `

  -FreezeTopRow:$false

  # Optional: small pause to ensure file is fully written before copying

  Start-Sleep -Seconds 2

  # If you need to copy elsewhere after creation:

  # Copy-Item $outFile "D:\Reports\SQLReport.xlsx" -Force

• Johan Bijnens

  SSC Guru

  Points: 135583

  More actions

  October 2, 2025 at 11:17 am

  #4663764

  do you have a question?

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data and code to get the best help
  

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me