I need help with powershell and sql server for generate file base on row value

This topic contains 1 reply, has 2 voices, and was last updated by  Chris Stephens 4 years ago.

  • Author
    Posts
  • #11531

    cydata
    Participant

    Hello Dears,
    first sorry I new bee to powershell and I have to realize this script for my job.
    on a sql server database, I have a table with 3 columns ( ClientId,ReportId,Details). I have to generate for each rown of my table a file containing the value of the column Details,the file generate has to be name with the value of ReportId and store in a local folder having for name the value of ClientId.
    Exple my table ClientID,ReportId,Details
    58001 1040 {\rtf1\ans} I will on my C:\RootFolder\58001\1040.rtf for exple..
    ps: that column Details store native RTF coding, therefore i need to generate my file with rtf extension.
    and my table has over 200'000 rows..
    Please help.
    here is what i have started,, but really difficult to understand it all..
    $ServerConnectionString = "Data Source=UK138032\VASCO;Initial Catalog=CMTLite;trusted_connection=true;"
    $ServerConnection = new-object system.data.SqlClient.SqlConnection($ServerConnectionString);

    $dataSet = new-object "System.Data.DataSet" "Details"
    $query = "SELECT DISTINCT [ClientId] FROM [CompanyDelta].[dbo].[Orders] where ClientId"

    $dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $ServerConnection)
    $dataAdapter.Fill($dataSet) | Out-Null

    $ServerAConnection.Close()

    $dataTable = new-object "System.Data.DataSet" "ReportId"
    $dataTable = $dataSet.Tables[0]

    $ServerConnectionString = "Data Source=UK138032\VASCO;Initial Catalog=CMTLite;trusted_connection=true;"
    $ServerConnection = new-object system.data.SqlClient.SqlConnection($ServerConnectionString);
    $ServerConnection.Open()

    $dataTable | FOREACH-OBJECT {
    $cmd = new-object System.Data.SQLClient.SQLCommand
    $cmd.CommandText = "SELECT [Details] FROM [CompanyDelta].[dbo].[Orders] where ClientId= @ClientId"
    $cmd.Connection = $ServerConnection
    $cmd.Parameters.AddWithValue("@ClientId", $_.Clientid)
    $rowsUpdated = $cmd.ExecuteNonQuery()

    }

    $ServerBConnection.Close()

  • #11554

    Chris Stephens
    Participant

    I didn't read through the exact query, but you would most likely want to create a custom object collection (named '$report' below). Then the below should work:

    $rootDirectory = "c:\temp\"
    $report | ForEach-Object {
    if (! (Test-Path $($rootDirectory + $_.ClientId) ) { Create-Item -Path $($rootDirectory + $_.ClientId }
    if (! (Test-Path $($rootDirectory + $_.ClientId + "\" + $_.ReportId) ) { Create-Item -Path $($rootDirectory + $_.ClientId + "\" + $_.ReportId }
    $_.Details | Out-File -Path $($rootDirectory + $_.ClientId + "\" + $_.ReportId + "\" + $_.Details)
    }

You must be logged in to reply to this topic.