none
SQL query result to Excel using Powershell

    Question

  • Hope someone can help me out. I have a text file with a list of name that I like to query against my SQL server. I created a stored procedure which gets it value from the file. The problem I have is the output. I want to output it to an Excel file. The following is my script.     

    cls
    $erroractionpreference = "SilentlyContinue"
    $a = New-Object -comobject Excel.Application
    $a.visible = $True 

    $b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item(1)

    $d = $c.UsedRange
    $d.Interior.ColorIndex = 19
    $d.Font.ColorIndex = 11
    $d.Font.Bold = $True
    $d.EntireColumn.AutoFit($True)

    $intRow = 2

    $dbExistingInstallation = 'testdb'

    $Server = '.'
    #$grdcde = 'hw_agbm_and_site_focals'

    $grdcde=''
    foreach ($grdcde in Get-Content “c:\sql\code.txt”)
    {

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnectionString = "Server=" + $Server + ";Database=" + $dbExistingInstallation + ";Integrated Security=True"
    $SqlConnection.ConnectionString = $SqlConnectionString
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "
    EXEC FindName @Groupname = " + $grdcde  

    $SqlCmd.Connection = $SqlConnection 
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
    $SqlAdapter.SelectCommand = $SqlCmd 
    $DataSet = New-Object System.Data.DataSet 
    $SqlAdapter.Fill($DataSet) | Out-Null 
    $c.Cells.Item(1,$intRow) = $grdcde
    $outdata=$DataSet[0]

    $intRow = $intRow + 1
    $SqlConnection.Close();
    }

    I've experimenting with different code but couldn't figured out how to get the output correct. I could be doing it wrong. If someone can provide some direction. 

    I'm looking for output like the following:

    Col_name Col_name
    john Mike
    Seth Bob
    mary Frank


    Thanks for looking



    • Edited by OCChuster Friday, September 27, 2013 4:22 PM
    • Moved by Bill_Stewart Tuesday, December 31, 2013 10:59 PM Off-topic post
    Thursday, September 26, 2013 11:02 PM

All replies

  • $outdata | Export-Csv file.csv

    Then open it in Excel.


    ¯\_(ツ)_/¯

    Friday, September 27, 2013 12:09 AM
  • Thanks for looking,

    My export is empty. It doesn't export any data. I see the data in the console.

    John

    Friday, September 27, 2013 4:27 PM
  • Hi John,

    You're overwriting $outdata each time you run through the loop. Try adding to it instead.


    Don't retire TechNet! - (Maybe there's still a chance for hope, over 12,000+ strong and growing)

    Friday, September 27, 2013 5:21 PM
  • There is really not enough information to discover exactly what you are trying to do.

    If you have a dataset then it can be exported.  You are not checking to see if your query actually worked.

    This line:

    $SqlAdapter.Fill($DataSet) | Out-Null 

    needs to be checked for a bad return.  (0 or less).  You are dumping the resuls in the bit bucket.

    Write-Host "Results of fill:$($SqlAdapter.Fill($DataSet))" -fore green

    The result will be the number of rows returned.  If it is zero then you will have no data,

    Your overall SQL is incorrect too.


    ¯\_(ツ)_/¯

    Friday, September 27, 2013 8:19 PM
  • The following will get you closer to what you are trying to do.

     
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnectionString = "Server=" + $Server + ";Database=" + $dbExistingInstallation + ";Integrated Security=True"
    $SqlConnection.ConnectionString = $SqlConnectionString
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection 
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
    $SqlAdapter.SelectCommand = $SqlCmd 
    
    $outdata=@()
    foreach ($grdcde in Get-Content “c:\sql\code.txt”){
        $SqlCmd.CommandText="EXEC FindName @Groupname='$grdcde'"  
        $DataSet = New-Object System.Data.DataSet 
        If($n=$SqlAdapter.Fill($DataSet) -gt 0){
            $outdata+=$DataSet[0]
            Write-Host "Returned $n records from $grpcde" -ForegroundColor green
        }else{
            Write-Host "No records return from $grdcde" -ForegroundColor red
        }
    }
    $SqlConnection.Close();
    
    # here is where you get teh records to output to Exccl
    foreach($DataSet in $outdata){
        #Process dataset
    }
    


    ¯\_(ツ)_/¯

    Friday, September 27, 2013 8:47 PM
  • Thanks Mike and JRV for the help.  

    I thought this "$SqlAdapter.Fill($DataSet) | Out-Null " was adding but I guess not.

    Basically, I was trying to read the file for the code and query SQL for the users with the code. It should return the first and last name.

    John 

    Tuesday, October 22, 2013 11:43 PM
  • Again - the return value of that tells you how many rows were processed.  It not greater than zero something is wrong.


    ¯\_(ツ)_/¯

    Tuesday, October 22, 2013 11:56 PM