Extract from sql.to multiple sheets

• Sqladmin1

  SSC Enthusiast

  Points: 191

  More actions

  October 7, 2025 at 9:03 am

  #4666940

  Import-Module ImportExcel

  # Path to your .sql file

  $sqlFile = "C:\Data\MyQueries.sql"

  $excelPath = "C:\Data\SqlExtract_$(Get-Date -Format yyyyMMdd).xlsx"

  # Read the SQL text

  $sql = Get-Content $sqlFile -Raw

  # Run SQL and get multiple result sets

  $resultSets = Invoke-Sqlcmd -ServerInstance "MyServer" -Database "MyDB" -Query $sql -OutputAs DataTables

  # Loop through each result set and export to Excel

  $sheetNumber = 1

  foreach ($table in $resultSets.Tables) {

  $sheetName = "Sheet$sheetNumber"

  $table | Export-Excel -Path $excelPath -WorksheetName $sheetName -AutoSize -Append

  $sheetNumber++

  }

  Write-Host "✅ Exported $($resultSets.Tables.Count) result sets to $excelPath"