Powershell / SQL Server / Import-CSV

Question

Working on another script for work and I'm attempting to read from a CSV containing only one column of data. And then for each item to find the corresponding ID when querying the SQL database. Then to put the result(ID1, CSVID1) in to an excel file(I have this part working fine).

Now I have run in to an issue as to how to populate the dataset within a foreach loop.

$excelAssets = Import-Csv .\test.csv -Header assetId | Foreach-Object {
    $assetId = $_.assetId
    # SQL Query Variables
    $query = "SELECT AssetId AS AssetID, BrandId AS BrandID FROM [AssetLibrary_BrandAsset] WHERE AssetId = $assetId"

    $connection = New-SqlConnection -Server $dataSource -Database $dataBase

    #Execute the SQL commands and place the results in dataset
    if ($connection.State -eq 'Open')
    {   
        $swLap = Start-Elapsed $sw "Executing SQL Query"
        Write-Verbose "$query";
        $dataSet += Invoke-SQLQuery -connection $connection -query $query -ExecutionTimeout '0'
        $i++
        $connection.Close();
        End-Elapsed $sw $swLap    
    } ELSE {
        Write-Error "$($(Format-Elapsed $swLap)) SQL Connection Not Open - Exiting...";
        exit;
    }
}

Now $dataSet += doesn't work and I have googled numerous times to try and find the answer to this problem. Any help is appreciated.

Using the $dataSet

$dataTable = new-object "System.Data.DataTable" "Results"
$dataTable = $dataSet.Tables[0]

$rowDT = $dataTable.Rows.Count;
$colDT = $dataTable.Columns.Count;
Write-Host -NoNewLine "$(Format-Elapsed $sw.Elapsed) Rows: ";
Write-Host -NoNewLine "$($rowDT+1)" -ForegroundColor "Green";
Write-Host -NoNewLine " Columns: "
Write-Host -NoNewLine "$($colDT+1)" -ForegroundColor "Green";
Write-Host -NoNewLine " Cells: "
Write-Host "$( ($colDT+1)*($rowDT+1) )" -ForegroundColor "Green";


#Create a 2D Array of the DataTable
# http://stackoverflow.com/questions/13184191/fastest-way-to-drop-a-dataset-into-a-worksheet
$tableArray = New-Object 'object[,]' $rowDT, $colDT;
$swLap = Start-Elapsed $sw "DataTable transformation"
# i = row and j = column
for ($i=0;$i -lt $rowDT; $i++) 
{
    #Write-Progress -Activity "Transforming DataTable" -status "Row $i" -percentComplete ($i / $rowDT*100)
    for ($j=0;$j -lt $colDT; $j++) 
    {
        $tableArray[$i,$j] = $dataTable.Rows[$i].Item($j).ToString();
    }    
}

End-Elapsed $sw $swLap 

$rowOffset = 1; $colOffset = 1;# 1,1 = "A1"

# Write out the header column names
for ($j=0;$j -lt $colDT; $j++) 
{
    $ActiveWorksheet.cells.item($rowOffset, $j+1) = $dataTable.Columns[$j].ColumnName;
}
$headerRange = $ActiveWorksheet.Range($ActiveWorksheet.cells.item($rowOffset, $colOffset), $ActiveWorksheet.cells.item($rowOffset, $colDT+$colOffset-1)); 
$headerRange.Font.Bold = $false
$headerRange.Interior.Color = $headingColour
$headerRange.Font.Name = $headingFont
$headerRange.Font.Color = $headingFontColour

$rowOffset++;

# Extract the data to Excel
$tableRange = $ActiveWorksheet.Range($ActiveWorksheet.cells.item($rowOffset, $colOffset), $ActiveWorksheet.cells.item($rowDT+$rowOffset-1, $colDT+$colOffset-1));
$tableRange.Cells.Value2 = $tableArray;

# Resize the columns in Excel
$swLap = Start-Elapsed $sw "Resize Excel Worksheet"
$wholeRange = $ActiveWorksheet.UsedRange
$wholeRange.EntireColumn.AutoFit() | Out-Null
End-Elapsed $sw $swLap 

# Save Excel workbook
$ActiveWorkbook.SaveAs("$OutputFile") 
$ActiveWorkbook.Close()

If i change $dataSet += to $dataSet = it will run the script but the output in the excel file is only the last object of the foreach loop. — Bradley Scott
– Bradley Scott, Commented Oct 15, 2015 at 11:02

Mathias R. Jessen · Accepted Answer · 2015-10-15 11:13:42Z

1

After assigning to $dataSet the first time, it's type is probably not array, meaning that the += operator doesn't behave exactly as you expect.

You can either initialize $dataSet as an empty array before you start assigning to it:

Import-Csv .\test.csv -Header assetId | Foreach-Object -Begin {$dataSet = @()} -Process {
    # rest of script in here
} -End {return $dataSet}

or you can cast it during assigning:

[array]$dataSet += Invoke-SQLQuery -connection $connection -query $query -ExecutionTimeout '0'

finally, an alternative solution would be to ensure that the output from Invoke-SQLQuery is treated as an array before you assign it to $dataSet:

$dataSet += @(Invoke-SQLQuery -connection $connection -query $query -ExecutionTimeout '0')

Whatever suits your style of coding.

answered Oct 15, 2015 at 11:13

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bradley Scott Over a year ago

When i use any of these it still only outputs 1 row. Log File: 00:01.26 Rows: 2 Columns: 3 Cells: 6

Mathias R. Jessen Over a year ago

@BradleyScott could you show the entire script? Nowhere in the snippet you posted do you actually do anything with the resulting $dataSet variable

Bradley Scott Over a year ago

@MathiasRJessen I have added where i use the $dataSet in the original post.

Collectives™ on Stack Overflow

Powershell / SQL Server / Import-CSV

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related