Powershell - Progress of CSV Export from SQL.

Posted on 2014-08-05
Last Modified: 2014-09-02

I have the following code below.

How can I change the last line so I can use the SQL - Write-Progress cmdlet to update the display as CSV records
are being exported.

Something like a percentage complete etc.



USE [TestDB]

/****** Object:  Table [dbo].[Computers]    Script Date: 30/07/2014 8:45:12 PM ******/


CREATE TABLE [dbo].[Computers](
	[IndexNumber] [int] NULL,
	[ComputerName] [nvarchar](50) NULL,
	[InvDate] [datetime] NULL


function fnInsert_Test($number_of_records)

    $connection_string = "Data Source=localhost;Initial Catalog=TestDB;Integrated Security=SSPI;" 
    $sqlconnection = new-object$connection_string);
    $sqlcmd = new-object
    $sqlcmd.Connection = $sqlconnection

    $sql = "Insert into dbo.Computers (IndexNumber,ComputerName,InvDate) Values (@IndexNumber,@ComputerName,@InvDate);"

    $sqlcmd.CommandText = $sql    

    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@IndexNumber",[Data.SQLDBType]::Int))) | Out-Null
    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ComputerName",[Data.SQLDBType]::NVarChar, 50))) | Out-Null
    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@InvDate",[Data.SQLDBType]::DateTime))) | Out-Null

    for ($i = 1; $i -le $number_of_records ; $i++)
        $sqlcmd.Parameters[0].Value = $i
        $sqlcmd.Parameters[1].Value = "PC" + $i
        $sqlcmd.Parameters[2].Value = [DateTime]::Now
        $sqlcmd.ExecuteNonQuery() | Out-Null



function Get-ScriptDirectory
  $Invocation = (Get-Variable MyInvocation -Scope 1).Value
  Split-Path $Invocation.MyCommand.Path

# -------------------------------------------------------------------------------------------------
# fnConnect_To_SQL: Connect to SQL database
# -------------------------------------------------------------------------------------------------
function fnConnect_To_SQL($server,$db)
    $ServerInstance = $server
    $Database = $db
    $ConnectionTimeout = 500

    $QueryTimeout = 500
    $QueryTimeout = 50000

    $conn=new-object System.Data.SqlClient.SQLConnection
    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout

    return $conn

# -------------------------------------------------------------------------------------------------
# fnRun_SQL_Dataset: Return SQL dataset.
# -------------------------------------------------------------------------------------------------
function fnRun_SQL_Dataset($conn,$sql)
    $cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
    $cmd.CommandTimeout = 500
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    $data = $ds.Tables 

    return $data

$script_directory = Get-ScriptDirectory 
$csv_file = $script_directory + "\test.csv"

fnInsert_Test 10000

$localdb = fnConnect_To_SQL "localhost" "TestDB"
$sql_data = fnRun_SQL_Dataset $localdb "select * from Computers"

$sql_data | Export-Csv -NoTypeInformation -Force -Path $csv_file -Encoding Unicode

Open in new window

Question by:whorsfall
    1 Comment
    LVL 67

    Accepted Solution

    As in, the time consuming part is the processing by fnRun_SQL_Dataset, not the export of data. In particular, you don't do
    $x = a_function_doing_a_lot_of_things()
    $x | export-csv ...

    Open in new window

    in PowerShell. Rather you write
    a_function_doing_a_lot_of_things() | export-csv

    Open in new window


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
    Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now