i am attempting to query an SQL database using a master-csv, that has 3 columns(stuid,lastname,firstname), and then export only the rows in that mastercsv, to another csv that contains the stuid,firstname,lastname,gradelevel from the database.
$masterlist = import-csv "c:\scripts\no-stu-email\no-stu-email.csv"
# $student = $masterlist.stuid
#Export File
$FinalNoEmailList = "C:\scripts\no-stu-email\no-stu-email-final.csv"
#Connection Strings
$Database = "SASDStudents"
$Server = "HP600-WIN8-MG\sqlexpress"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
# $student = $masterlist.stuid
# if ($student -eq $true){
# Connect to SQL and query data, extract data to SQL Adapter
foreach ($thing in $masterlist){
$SqlQuery = @"
SELECT 'stuid','gradelevel','firstname','lastname' FROM dbo.StudentInfo WHERE stuid = $(thing.stuid)
"@
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$sqladapter.fill($dataset)
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Hash Table
$objTable = $DataSet.Tables[0]
}
#Export Hash Table to CSV File
$objTable | Export-CSV $FinalNoEmailList -NoTypeInformation
this is the code i have so far. i can confirm that $thing.stuid contains the Stuid from the master csv. but it doesnt seem to be getting written to the exported csv. i cant figure out how to get my final outcome.
any suggestions appreciated.
Hi Michael,
$thing.stuid would definitely be coming from the CSV as $thing is just an object from the $masterlist array (that was created from the CSV import). However, at no point do you reference the $thing variable during the foreach loop. In essence you are running the same SQL query multiple times and then exporting the results of the last iteration. You may want to update your SQL query to include a WHERE clause and reference the data found in $thing. Additionally, you’ll want to update the line where you populate your hash table. As it is now, you are overwriting the $objTable each iteration of the foreach loop and then outputting the last version of it.
$SqlQuery = @"
SELECT 'stuid','gradelevel','firstname','lastname' FROM dbo.StudentInfo WHERE stuid = $(thing.stuid)
"@
thanks for your response chris.
does this not satisfy it? this is the sql query inside the foreach loop.
I believe, you’ll want to add a set of single quotes and an additional $
$SqlQuery = @"
SELECT 'stuid','gradelevel','firstname','lastname' FROM dbo.StudentInfo WHERE stuid = '$($thing.stuid)'
"@
ok… well that seems to change things around a little. lol
i get a total of 65 zeros (0) and ones(1) as output on the screen and a csv output with nothing in the csv. the 65 1’s or 0’s is the same number of rows in the mastercsv.
I just noticed you have two calls to the $sqladapter.fill($dataset) method. The first one is outputting the 1’s and 0’s to the console. It is also creating double records in $objTable. Additionally I think you want to drop the single quotes in the Select part of your SQL query. Also, as I mentioned careful with the $objtable = command within the foreach loop. This will overwrite the $objTable variable everytime it runs (each iteration of the foreach loop) and you will only be left with the results of that last query.
Try the following:
$masterlist = import-csv "c:\scripts\no-stu-email\no-stu-email.csv"
# $student = $masterlist.stuid
#Export File
$FinalNoEmailList = "C:\scripts\no-stu-email\no-stu-email-final.csv"
#Connection Strings
$Database = "SASDStudents"
$Server = "HP600-WIN8-MG\sqlexpress"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
# $student = $masterlist.stuid
# if ($student -eq $true){
# Connect to SQL and query data, extract data to SQL Adapter
foreach ($thing in $masterlist){
$SqlQuery = @"
SELECT stuid,gradelevel,firstname,lastname FROM dbo.StudentInfo WHERE stuid = '$($thing.stuid)'
"@
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Hash Table
$objTable += $DataSet.Tables[0]
}
#Export Hash Table to CSV File
$objTable | Export-CSV $FinalNoEmailList -NoTypeInformation
i was also missing the “+=” in the $objtable += $dataset.tables[0] statement
in a foreach loop…, that += is what will add each item in the loop. am i correct in that?
thank you so much for your help chris. its much appreciated.
also im missing about 10 names in the final outcome… im looking into that issue now. im guessing its due to the fact that it isnt listed in the database, hence wont show up in final output csv
Correct.
One thing to note. $objTable is an array and using += can be a performance hit with large datasets. Dave Wyatt wrote a good blog entry awhile back on it (PowerShell Performance: The += Operator (and When to Avoid It)).
In this case, it won’t be noticeable. Sometimes I will just change the array to an arraylist which can be resized:
$masterlist = import-csv "c:\scripts\no-stu-email\no-stu-email.csv"
# $student = $masterlist.stuid
#Export File
$FinalNoEmailList = "C:\scripts\no-stu-email\no-stu-email-final.csv"
#Connection Strings
$Database = "SASDStudents"
$Server = "HP600-WIN8-MG\sqlexpress"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
# $student = $masterlist.stuid
# if ($student -eq $true){
# Connect to SQL and query data, extract data to SQL Adapter
$objTable = New-Object System.Collections.ArrayList
foreach ($thing in $masterlist){
$SqlQuery = @"
SELECT stuid,gradelevel,firstname,lastname FROM dbo.StudentInfo WHERE stuid = '$($thing.stuid)'
"@
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Arraylist
$objTable.Add($DataSet.Tables[0])
}
#Export ArrayList to CSV File
$objTable | Export-CSV $FinalNoEmailList -NoTypeInformation
i will read that article for sure. but question. when you refer to large datasets… are you referring to the database i am querying or the request of columns in the csv?
i also added some remove-varaible statements at the beginning of the script. its seems that each time i ran the script it was adding the same rows of stuid’s to the final csv even though i would delete the final outcome csv in between each test run.
In this case it would be if $masterList contained a large amount of items. The performance hit caused by += is caused by the way arrays work. Arrays can not be resized, so when you run += Powershell takes the contents of the array + the new data you are trying to add to it and creates a brand new array and assigns it to the variable ($objTable in this case). The more iterations the loop runs through, the more performance hit you will see. Like I said, in this particular case there is absolutely no problem using +=.
As far as the issue you are seeing (adding data to the CSV rather than overwriting), that’s because the contents of the $objTable variable are not emptied until you close the Powershell session (close the window). I would either use the Arraylist option I posted or add the following before the foreach loop:
# Set ObjTable as an empty array
$objTable = @()
A couple of suggestions. First, rather than looping through a bunch of student ID’s, getting individual records and then trying to create custom PSObject, you should consider changing your query to return all student records. Take a look at SQL WHERE IN. Here is an example:
$csv = @()
$csv += New-Object -TypeName PSObject -Property @{ "StuID" = 2; }
$csv += New-Object -TypeName PSObject -Property @{ "StuID" = 3; }
$csv += New-Object -TypeName PSObject -Property @{ "StuID" = 5; }
$csv += New-Object -TypeName PSObject -Property @{ "StuID" = 7; }
$csv += New-Object -TypeName PSObject -Property @{ "StuID" = 8; }
$stuIDs = $csv | select -ExpandProperty StuID
$sqlIn = $stuIDs -join "','"
$sqlCMD = @"
SELECT stuid
,gradelevel
,firstname
,lastname
FROM dbo.StudentInfo WHERE stuid IN ('$sqlIn')
"@
$sqlCMD
Output:
SELECT stuid
,gradelevel
,firstname
,lastname
FROM dbo.StudentInfo WHERE stuid IN ('2','3','5','7','8')
Next you go to $objTable. If you do $objTable.GetType(), you will see that is NOT a PSObject. To convert it from a datatable to a PSObject is extremely difficult. 
:
$results = $objTable | Select-Object stuid,gradelevel,firstname,lastname
$results | Export-CSV....
The Select object will convert it from a datatable to a PSObject and then you can export to your hearts content. Keep in mind that if you do a Select * that there are other properites of a datatable\row that you will get inadvertently, so specify the properties.