PowerShell and Excel: Need to output query sets side by side

Question

I have 14 SQL queries, set into 14 variables. laxTransactions 1 through 7, and rochTransactions 8 through 14. Right now, I have them all going into a single variable and exported to Excel. All queries output vertically one after the other. However, I would like to place the rochTransactions directly to the right of the laxTransactions for a side-by-side comparison. I'm not understanding how to write that group of queries to a different starting column.

Here's some of the code:

$csv = $laxTransactions1 + $space + $laxTransactions2 + $space + $laxTransactions3 + $space + $laxTransactions4 + $space + $laxTransactions5 + $space + $laxTransactions6 + $space + $laxTransactions7 + $space + $space + $RochTransactions8 + $space + $RochTransactions9 + $space + $RochTransactions10 + $space + $RochTransactions11 + $space + $RochTransactions12 + $space + $RochTransactions13 + $space + $RochTransactions14

$csv | Export-Csv -NoTypeInformation -NoClobber -ErrorAction Ignore -Force -Path "C:\Auto PS Scripts\Transactions\AvgTransactions.csv"


[int]$LinesInFile = 0
$reader = New-Object IO.StreamReader "$filePath\AvgTransactions.csv"
    while($reader.ReadLine() -ne $null){ $LinesInFile++ }
[int]$lastLineInFile = [int]$LinesInFile + 1


$objExcel = New-Object -ComObject Excel.Application 
$objExcel.Visible = $False
$objExcel.DisplayAlerts = $False
$objWorkBook = $objExcel.Workbooks.Open("$filePath\AvgTransactions.csv")
$workSheet = $objWorkbook.Sheets.Item(1)

I can select and format cell ranges, but I'm not really sure how to tackle writing separate query results to different starting columns. Everything I've ever had to output until now has been straight down the spreadsheet. Not asking that someone else do my job for me, but I've been searching the web for a solution and I'm not finding any examples that are helping me. If someone had a link, or some advice, I'd appreciated it greatly! Thank you.

Does this answer your question: https://stackoverflow.com/a/68070763/1701026 ? — iRon, Apr 16 '23 at 18:19

CDP1802 · Answer 1 · 2023-04-16T22:24:36.873

No need to create a csv, write directly to the Excel sheet

$laxTransactions1 = 1 ; $rochTransactions8  = 8
$laxTransactions2 = 2 ; $rochTransactions9  = 9
$laxTransactions3 = 3 ; $rochTransactions10  = 10
$laxTransactions4 = 4 ; $rochTransactions11  = 11
$laxTransactions5 = 5 ; $rochTransactions12  = 12
$laxTransactions6 = 6 ; $rochTransactions13 = 13
$laxTransactions7 = 7 ; $rochTransactions14  = 14

$filePath = "C:\Auto PS Scripts\Transactions\"
$xlsFile = "AvgTransactions"

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $True
$objExcel.DisplayAlerts = $False
$objWorkBook = $objExcel.Workbooks.Add(1)
$workSheet = $objWorkbook.Sheets.Item(1)

# write to sheet in 2 columns of 7
for (($n=1), ($m=8); $n -le 7; $n++, $m++){
   $workSheet.Cells.Item($n,1) = Get-Variable -Name "laxTransactions$n" -ValueOnly
   $workSheet.Cells.Item($n,2) = Get-Variable -Name "rochTransactions$m" -ValueOnly
}
# save and quit
$objWorkBook.SaveAs("${filePath}${xlsfile}.xlsx")
$objWorkBook.Close($False)
$objExcel.Quit()
echo "${filePath}${xlsFile}.xlsx created"

PowerShell and Excel: Need to output query sets side by side

1 Answers1