0

I am trying to export data from a SQL server database to a CSV file using Powershell. I already have the script done and it's been tested and it works but just need a little patch done. If there is no data to export, I do not want a file created but currently it creates the file.

$ConnectionString = "Data Source=SQLInstance; Database=db; Trusted_Connection=True;";
$streamWriter = New-Object System.IO.StreamWriter "\\servername\someFolder\Testing.csv"
$sqlConn = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConn
$sqlCmd.CommandText = "SELECT * FROM db..mytable where updated = 0"
$sqlConn.Open();
$reader = $sqlCmd.ExecuteReader();

$array = @()
for ( $i = 0 ; $i -lt $reader.FieldCount; $i++ ) 
    { $array += @($i) }
$streamWriter.Write($reader.GetName(0))
for ( $i = 1; $i -lt $reader.FieldCount; $i ++) 
{ $streamWriter.Write($("," + $reader.GetName($i))) }

$streamWriter.WriteLine("")
while ($reader.Read())
{
    $fieldCount = $reader.GetValues($array);

    for ($i = 0; $i -lt $array.Length; $i++)
    {
        if ($array[$i].ToString().Contains(","))
        {
            $array[$i] = '"' + $array[$i].ToString() + '"';
        }
    }

    $newRow = [string]::Join(",", $array);

    $streamWriter.WriteLine($newRow)
}
$reader.Close();
$sqlConn.Close();
$streamWriter.Close();

I need to check if there is data before the file is created. Please help me, I am very new to PS.

0

You're using PowerShell, no need to fuss about with .NET in most cases.

$ConnectionString = "Data Source=SQLInstance; Database=db; Trusted_Connection=True;";
$outfile = @()
$sqlConn = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConn
$sqlCmd.CommandText = "SELECT * FROM db..mytable where updated = 0"
$sqlConn.Open();
$reader = $sqlCmd.ExecuteReader();
$array = @()
for ( $i = 0 ; $i -lt $reader.FieldCount; $i++ ) 
    { $array += @($i) }
$line = $reader.GetName(0)
for ( $i = 1; $i -lt $reader.FieldCount; $i ++) 
{ $line += ($("," + $reader.GetName($i))) }

$outfile += $line
while ($reader.Read())
{
    $fieldCount = $reader.GetValues($array);

    for ($i = 0; $i -lt $array.Length; $i++)
    {
        if ($array[$i].ToString().Contains(","))
        {
            $array[$i] = '"' + $array[$i].ToString() + '"';
        }
    }

    $outfile += [string]::Join(",", $array);
}

if ($outfile.Count -gt 0)
{ $outfile | Out-File "\\servername\someFolder\Testing.csv" }

Pretty straightforward, instead of writing directly to the file in each case (which requires the file to be created beforehand), save everything to an array $outfile then at the end, test if anything was added and if so write to file.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.