KiwiPaul KiwiPaul - 28 days ago 13
SQL Question

Powershell import csv to SQL queries, export multiple csvs

I'm struggling to get my head around this one, I've only just begun looking at scripting in SQL, and my powershell is very limited. The requirments are basically this:

Utilisng Powershell, import a csv file which contains one column that needs to feed into multiple SQL queries via a loop, exporting a seperate csv file for each different query.

example import of csv:
Project (heading)
1000
1001
1002

Powershell:

$importProjectsCSV = e:\Projects.csv
$server = servername
$database = database

import-csv $importProjectsCSV | ForEach-Object {
$query = "
Select ProjectLeader, ProjectTitle
FROM dbo.PROJECTS
Where Project = $_.Project;

Select ProjectClient, Name
FROM dbo.CLIENTS
Where Project = $_.Project;

$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()


$dataset.Table[0] | Export-csv "E:\" + $_.ProjectName + ".csv"
$dataset.Table[1] | Export-csv "E:\" + $_.ProjectName + ".csv"





The problem is that the variable isn't coming into the SQL query.
Is there a better way to handle this type of example?

Appreciate any pointers

Paul.

Answer

I would do something like this:

I must admit i havent been able to test it, and personally i usually use c# to query sql servers. So i might have gone a bit wrong somewhere.

$importProjectsCSV = e:\Projects.csv
$server = servername
$database = database

$Projects =  Import-Csv -Path $importProjectsCSV | % {$_.Project}
$DS_Projects = New-Object System.Data.DataSet
$DS_Clients = New-Object System.Data.DataSet

$query_pro = "Select ProjectLeader, ProjectTitle, Project FROM dbo.PROJECTS";
$query_Clients = "Select ProjectClient, Name, Project FROM dbo.CLIENTS";

$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()
$command = $connection.CreateCommand();
$command.CommandText = $query_pro;
$sqlAdap = New-Object System.Data.SqlClient.SqlDataAdapter($command)
$sqlAdap.Fill($DS_Projects)
$command2 = $connection.CreateCommand();
$command2.CommandText = $query_Clients;
$sqlAdap2 = New-Object System.Data.SqlClient.SqlDataAdapter($command2)
$sqlAdap2.Fill($DS_Clients)
$connection.Close();

foreach($project in $Projects)
{
    $DS_Projects.Tables[0].Select("Project=$project") | Export-Csv "E:\$project.csv"
    $DS_Clients.Tables[0].Select("Project=$project") | Export-Csv "E:\$project.csv"
}