October 7, 2025 at 9:03 am
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"
October 7, 2025 at 11:22 pm
Import-Module SqlServer
Import-Module ImportExcel
$server = "10.0.0.5,1433"
$db = "MyDB"
$sqlFile = "C:\Temp\MyScript.sql"
$output = "C:\Temp\MultiQueryReport.xlsx"
# Run the SQL script and capture all result sets
$result = Invoke-Sqlcmd -ServerInstance $server -Database $db -InputFile $sqlFile -As DataSet
# Loop through each result set and export to Excel
for ($i = 0; $i -lt $result.Tables.Count; $i++) {
$sheetName = "Sheet$($i + 1)"
$table = $result.Tables[$i]
$table | Export-Excel -Path $output -WorksheetName $sheetName -AutoSize -Append
}
October 8, 2025 at 1:26 pm
and what is your question? I see nothing but a bunch of code, calling functions that are not supplied to do something with parameters supplied
October 9, 2025 at 5:01 am
$sqlFile = "C:\Scripts\query.sql"
$excelFile = "C:\Reports\report.xlsx"
# --- Step 1: Read the SQL text properly ---
# -Encoding UTF8 → reads Unicode correctly
# -Raw → keeps newlines
$sqlText = Get-Content -Path $sqlFile -Raw -Encoding UTF8
# --- Step 2: Clean any invisible/non-ASCII characters (optional but safe) ---
$sqlText = $sqlText -replace '[^\x09\x0A\x0D\x20-\x7E]', ''
# --- Step 3: Create or reuse the sheet ---
if (-not (Get-ExcelSheetInfo -Path $excelFile | Where-Object { $_.Name -eq "SQL" })) {
Add-ExcelWorksheet -Path $excelFile -WorksheetName "SQL"
}
# --- Step 4: Write the SQL text to one cell (A1) ---
Set-ExcelRange -Path $excelFile -WorksheetName "SQL" -Address "A1" -Value $sqlText -WrapText:$true
# Optional: adjust row height and column width for readability
Set-ExcelRow -Path $excelFile -WorksheetName "SQL" -Row 1 -AutoFitHeight
Set-ExcelColumn -Path $excelFile -WorksheetName "SQL" -Column 1 -AutoFitWidth
October 9, 2025 at 5:13 am
$sqlFile = "C:\Scripts\query.sql"
$excelFile = "C:\Reports\report.xlsx"
# Step 1: Read SQL exactly as-is (UTF-8, preserving newlines)
$sqlText = Get-Content -Path $sqlFile -Raw -Encoding UTF8
# Step 2: Remove BOM, invisible or control chars (optional safety)
$sqlText = $sqlText -replace '^\xEF\xBB\xBF', '' # remove UTF-8 BOM if present
$sqlText = $sqlText -replace '[^\x09\x0A\x0D\x20-\x7E]', '' # remove invisible non-printable chars
# Step 3: Remove wrapping quotes if PowerShell accidentally added them
if ($sqlText.StartsWith('"') -and $sqlText.EndsWith('"')) {
$sqlText = $sqlText.Substring(1, $sqlText.Length - 2)
}
# Step 4: Write it to Excel in one wrapped cell
if (-not (Get-ExcelSheetInfo -Path $excelFile | Where-Object { $_.Name -eq "SQL" })) {
Add-ExcelWorksheet -Path $excelFile -WorksheetName "SQL"
}
Set-ExcelRange -Path $excelFile -WorksheetName "SQL" -Address "A1" `
-Value $sqlText -WrapText:$true
Set-ExcelRow -Path $excelFile -WorksheetName "SQL" -Row 1 -AutoFitHeight
October 10, 2025 at 2:48 am
-- 1a: Active requests (look for your ALTER TABLE statement)
SELECT r.session_id,
r.status,
r.command,
r.wait_type,
r.wait_time,
r.blocking_session_id,
DB_NAME(r.database_id) AS database_name,
r.start_time,
SUBSTRING(t.text, r.statement_start_offset/2 + 1,
(CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), t.text)) * 2
ELSE r.statement_end_offset END - r.statement_start_offset)/2 + 1) AS current_statement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50 -- ignore system sessions
ORDER BY r.start_time DESC;
October 13, 2025 at 2:06 am
SELECT
d.name AS DatabaseName,
mf.name AS LogFileName,
mf.size/128 AS CurrentSizeMB, -- Current size in MB
mf.growth/128 AS GrowthMB, -- Growth increment in MB
mf.max_size/128 AS MaxSizeMB, -- Max size in MB (-1 = unlimited)
mf.file_id AS FileID,
mf.type_desc AS FileType,
CASE
WHEN mf.size = mf.min_size THEN mf.size/128
ELSE mf.min_size/128
END AS MinimumSizeMB
FROM sys.master_files mf
JOIN sys.databases d
ON d.database_id = mf.database_id
WHERE mf.type_desc = 'LOG'
ORDER BY d.name;
October 13, 2025 at 2:09 am
SELECT
d.name AS DatabaseName,
mf.name AS LogFileName,
mf.size / 128 AS CurrentSizeMB, -- Current size in MB
mf.growth / 128 AS GrowthMB, -- Growth increment in MB
CASE
WHEN mf.max_size = -1 THEN 'Unlimited'
ELSE CAST(mf.max_size / 128 AS VARCHAR(10))
END AS MaxSizeMB,
mf.file_id AS FileID,
mf.type_desc AS FileType,
-- Safe shrink target = current size if small, or 5 MB minimum
CASE
WHEN mf.size / 128 < 5 THEN 5
ELSE mf.size / 128
END AS SafeShrinkTargetMB
FROM sys.master_files mf
JOIN sys.databases d
ON d.database_id = mf.database_id
WHERE mf.type_desc = 'LOG'
ORDER BY d.name;
October 13, 2025 at 12:58 pm
# Run SQL and keep headers
$data = sqlcmd -S MyServer -d MyDB -i "query.sql" -s "," -W -h -1 | ConvertFrom-Csv
# Ensure headers exist even if no rows
if (-not $data) { $data = [PSCustomObject]@{ ($data | Select-Object -First 1 | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name) = '' } }
# Export
$data | Export-Excel "output.xlsx" -WorksheetName "Data" -NoTypeInformation
October 13, 2025 at 1:05 pm
# Get SQL output
$output = sqlcmd -S MyServer -d MyDB -i "query.sql" -s "," -W -h -1
# Convert to objects; if empty, create empty row from headers
$data = $output | ConvertFrom-Csv
if (-not $data) { $data = [PSCustomObject]@{ foreach ($h in ($output | Select-Object -First 1 -Split ",")) { $h = '' } } }
# Export to Excel
$data | Export-Excel "output.xlsx" -WorksheetName "Data" -NoTypeInformation
October 13, 2025 at 1:11 pm
# Get SQL output
$output = sqlcmd -S MyServer -d MyDB -i "query.sql" -s "," -W -h -1
# Convert to objects; if empty, create empty row from headers
$data = $output | ConvertFrom-Csv
if (-not $data) {
$headers = ($output | Select-Object -First 1) -split ","
$row = @{}
foreach ($h in $headers) { $row[$h] = '' }
$data = [PSCustomObject]$row
}
# Export to Excel
$data | Export-Excel "output.xlsx" -WorksheetName "Data" -NoTypeInformation
October 13, 2025 at 1:18 pm
# Run SQL and convert to objects
$data = (sqlcmd -S MyServer -d MyDB -i "query.sql" -s "," -W -h -1) -join "rn" | ConvertFrom-Csv
# Fallback for empty results: create a row with headers
if (-not $data) {
$headers = ((sqlcmd -S MyServer -d MyDB -i "query.sql" -s "," -W -h -1 | Select-Object -First 1) -replace '"','') -split ","
$row = @{}
foreach ($h in $headers) { $row[$h] = '' }
$data = [PSCustomObject]$row
}
# Export to Excel
$data | Export-Excel "output.xlsx" -WorksheetName "Data" -NoTypeInformation
October 13, 2025 at 1:22 pm
# Run SQL and convert to objects
$output = sqlcmd -S MyServer -d MyDB -i "query.sql" -s "," -W -h -1
$data = $output -join "rn" | ConvertFrom-Csv
# If no rows, create a row with headers
if (-not $data) {
$headers = ($output | Select-Object -First 1) -replace '"','' -split ","
$row = @{}
foreach ($h in $headers) { $row[$h] = '' }
$data = [PSCustomObject]$row
}
# Export to Excel
$data | Export-Excel "output.xlsx" -WorksheetName "Data" -NoTypeInformation
October 13, 2025 at 1:26 pm
# Run SQL
$output = sqlcmd -S MyServer -d MyDB -i "query.sql" -s "," -W -h -1
# Convert to objects if there is data
$data = if ($output.Count -gt 1) { $output | ConvertFrom-Csv } else { $null }
# If no rows, create a row with headers
if (-not $data) {
$headers = ($output | Select-Object -First 1) -replace '"','' -split ","
$row = @{}
foreach ($h in $headers) { $row[$h] = '' }
$data = [PSCustomObject]$row
}
# Export to Excel
$data | Export-Excel "output.xlsx" -WorksheetName "Data" -NoTypeInformation
October 13, 2025 at 2:43 pm
I'll echo what Frederico asked - What is your question?
Viewing 15 posts - 1 through 15 (of 16 total)
The topic ‘Extract from sql.to multiple sheets’ is closed to new replies.