none
Insert value from SQL Server to Excel Spreadsheet - Powershell

    Question

  • I have everything finished in my script except for one last part, and maybe a few error handling functions, but for the most part its done. I have the data from SQL Server and I'm able to find the cell in excel that the data goes to I just need to correlate the data with the spreadsheet. I've posted in other forums and haven't found an answer and I'm bangin my head with this one. What the script does is it get the data from SQL Server based on a user supplied date and then checks a file path for a file name, if the file doesn't exist it creates it. Then based on that same date that the user supplied, it finds the date in the spreadsheet and drops down one cell and should input the data from SQL Server. It will also put in some data in specified cells from the data that is parsed out. So where I'm getting stuck is the part that the data from SQL Server goes to the cell. Ok, here's my script so far:

    set-variable date -scope script
    $date = Read-InputBox -message "Enter a date that the diversion record was observed." -title "Daily Diversion Date"

    Function Get-TodayMeas{
        $SRVR = "$ENV:COMPUTERNAME\XMAP7"
        $c = new-connection -server $srvr

        $DB = get-sqldatabase -sqlserver $SRVR -dbname TESTING1

    #$TABLE = get-sqltable -database $DB -name "CHANGES_2"

        $GetspCFS =get-sqlstoredprocedure -database $DB -name spGETCFS
        $CFSname = $GetspCFS.Name

        $GETCFS = invoke-storedprocedure -connection $c -storedprocname $CFSName -parameters @{reqdate = $date}

        $Structure_Nam1 = @(
            foreach($Structure_Nam in $GETCFS)
            {
                $Structure_Nam[2]
            }
        )
       
        $CFS1 = @(
            foreach($CFS in $GETCFS) #|ft -auto
            {
                $CFS[0]
            }
        )

        $WDID1 = @(
            foreach($WDID in $GETCFS)
            {
                $WDID[1]
            }
        )

    foreach($WDID2 in $WDID1){
        $WDID2
        $wdidpath = "C:\DiversionRecs\$WDID2.xlsm"
        $testpath = Test-path $WDIDpath

        if($testpath -eq $FALSE){
            $filemsg = Read-MsgBox -message "$WDID2.xlsm does not exist! Would like to create it?" -icon Question -buttons YesNo
       
            if($filemsg = "YES"){
                $xl2 = new-object -com Excel.Application
                $wb2 = $xl2.Workbooks.Open("C:\DiversionRecs\TemplateWDID.xltm")
                $wb2.SaveAs($WDIDPath, 52)
                $xl2.Quit()
                }
            else{
                Read-MsgBox -message "Hit Ok to Close..." -Icon Warning -Title "Close" -buttons OKOnly
                }
           
            Update-Record $reqdate
            }

        else{
            Update-Record $reqdate
            }
    #>
    }

    }

    function Update-Record($reqdate){
    #$reqdate = Read-Inputbox -message "Enter date of diversion record. Please enter date in this format: mm/d/yyyy" -title "Date of Observation"
        $reqdate = $date
        $sheetname = Read-Inputbox -message "Enter current irrigation year. For example, irrigation year runs from 11/2010-10/2011. So if observed date were in Nov. or Dec. of 2010, you would enter 2011" -title "Water Year"
        $xl = new-object -comobject excel.application
        $xl.Visible = $true
        $wb = $xl.Workbooks.Open($WDIDPath)
        $ws = $wb.WorkSheets.item($Sheetname)
        $ws.Activate()
       
        $reqdate1 = $reqdate.Split("/")
        $imon = $reqdate1[0]
        $mon =
            Switch($imon){
            11 {"NOV"}
            12 {"DEC"}
            1 {"JAN"}
            2 {"FEB"}
            3 {"MAR"}
            4 {"APR"}
            5 {"MAY"}
            6 {"JUN"}
            7 {"JUL"}
            8 {"AUG"}
            9 {"SEP"}
            10 {"OCT"}
            }

        set-variable ro -scope script
        $Column = @(14..44)

    #function Get-DateCol{

    foreach ($ro in $row){

        $Found1 = $False
        do{
            $ro++
            $col = 13
            $Range = $ws.Cells.Item($ro,$col)
        if($range.value2 -eq $mon){
            $FOUND1 = $TRUE
            BREAK
            }
        }
        while($ro -le 147)
            If($FOUND1 = $TRUE){
                BREAK
                }

    }
    #write-host "R:$ro C:$col"
    #}
    #write-host "R: $ro"
    #function Get-SpecDate{
    # Clear-Variable $Row
    # Clear-Variable $Col
    #Get-DateCol
    foreach($col in $column){
        $FOUND2 = $FALSE
    #$Row = 5

        do {
            $col++
        #Write-Host $ws.Cells.Item($Ro, $Col).Text
        #write-host "C:$Col R:$Ro"
            if($ws.Cells.Item($Ro, $Col).Text -eq $reqdate){
                $FOUND2 = $True
                break
                }
        }
     
    }    
                
    #write-host "Found cell? " $FOUND2
    #write-host "C:$Col R:$RO"
        $RO+=1

        #This is where I'm getting stuck. Putting in $WD, $ID, $CFS1 for each spreadsheet. Thanks.

        $WDIDPath1 = $WDIDPath.Split("\")
        $WDIDPath2 = $WDIDPath1[2]
        $ws.Cells.Item($RO,$COL).Value2 = $CFS1
        $WD = $WDID2.substring(0,2)
        $ID = $WDID2.substring(4,3)
        $ws.Cells.Item(2,1).Value2 = $WD
        $ws.Cells.Item(2,2).Value2 = $ID
        $ws.Range("Ditch_Name").Value2 = $Structure_Nam1

       

    $xl.quit()
    }

    Get-TodayMeas


    D
    Wednesday, October 20, 2010 8:47 PM

Answers

  • Not to open another can of worms, but I understand its an Excel automation problem and not a Windows OS problem. But I have yet to read anything in this forum about only posting Windows OS scripting issues and there are a bunch of non-Windows OS posts that are answered daily.

    But I did end up figuring out this damn script, so I'm marking this post as the answer.

    If anyone is interested here's the code for the issue.

    set-variable date -scope script
    $date = Read-InputBox -message "Enter a date that the diversion record was observed." -title "Daily Diversion Date"

    Function Get-TodayMeas{
        $SRVR = "$ENV:COMPUTERNAME\XMAP7"
        $c = new-connection -server $srvr

        $DB = get-sqldatabase -sqlserver $SRVR -dbname TESTING1

    #$TABLE = get-sqltable -database $DB -name "CHANGES_2"

        $GetspCFS =get-sqlstoredprocedure -database $DB|?{$_.Name -eq 'spGETCFS'}
       
        $CFSname = $GetspCFS.Name
       
        $GETCFS = invoke-storedprocedure -connection $c -storedprocname $CFSName -parameters @{reqdate = $date}
       
        $WDID1 = @(
            foreach($WDID in $GETCFS)
            {
                $WDID[1]
            }
        )

    foreach($WDID2 in $WDID1){
        $WDID2
       
        $GetspWDID = get-sqlstoredprocedure -database $DB|?{$_.Name -eq 'spGETCFSWDID'}
        $WDIDName = $GETspWDID.Name
        $GetspWDID1 = invoke-storedprocedure -connection $c -storedprocname $WDIDName -parameters @{reqdate = $date;XmapWDID = $WDID2}
       
         $Structure_Nam1 = @(
            foreach($Structure_Nam in $GETspWDID1)
            {
                $Structure_Nam[2]
            }
        )
       
        $CFS1 = @(
            foreach($CFS in $GETspWDID1)
            {
                $CFS[0]
            }
        )
        $CFS1
        $Structure_Nam1
        $wdidpath = "C:\DiversionRecs\$WDID2.xlsm"
        $testpath = Test-path $WDIDpath

        if($testpath -eq $FALSE){
            $filemsg = Read-MsgBox -message "$WDID2.xlsm does not exist! Would like to create it?" -icon Question -buttons YesNo
       
            if($filemsg = "YES"){
                $xl2 = new-object -com Excel.Application
                $wb2 = $xl2.Workbooks.Open("C:\DiversionRecs\TemplateWDID.xltm")
               
                $WDIDPath1 = $WDIDPath.Split("\")
                $WDIDPath2 = $WDIDPath1[2]
                $WDIDPath3 = $WDIDPath2.Substring(0,7)
                           
                $WD = $WDID2.substring(0,2)
                $ID = $WDID2.substring(4,3)
               
                $wb2.SaveAs($WDIDPath, 52)
                $xl2.Quit()
               
               Update-Record $reqdate
                }
               
            elseif ($filemsg -eq "NO"){
                Read-MsgBox -message "Hit Ok to Close..." -Icon Warning -Title "Close" -buttons OKOnly
                }
           
            }

        else{
            Update-Record $reqdate
           
            }
           
       }
       }
         
    function Update-Record($reqdate){

        $reqdate = $date
       
        $reqdate1 = $reqdate.Split("/")
        $imon = $reqdate1[0]
        $iyr = $reqdate1[2]
        $mon =
            Switch($imon){
            11 {"NOV"}
            12 {"DEC"}
            1 {"JAN"}
            2 {"FEB"}
            3 {"MAR"}
            4 {"APR"}
            5 {"MAY"}
            6 {"JUN"}
            7 {"JUL"}
            8 {"AUG"}
            9 {"SEP"}
            10 {"OCT"}
            }
       
        $xl = new-object -comobject excel.application
        $xl.Visible = $true
        $wb = $xl.Workbooks.Open($WDIDPath)
       
        if(($mon -eq 11) -or ($mon -eq 12)){
        $sheetname = {
        Switch($iyr){
        2010{"2011"}
        2011{"2012"}
        2012{"2013"}
        2013{"2014"}
        2014{"2015"}
        2015{"2016"}
        2016{"2017"}
        }
        }
        }
        else{
        $sheetname = $iyr
        }
          
        $ws = $wb.WorkSheets.item($Sheetname)
        $ws.Activate()
       
       
       
        set-variable ro -scope script
        $Column = @(14..44)

    foreach ($ro in $row){

        $Found1 = $False
        do{
            $ro++
            $col = 13
            $Range = $ws.Cells.Item($ro,$col)
        if($range.value2 -eq $mon){
            $FOUND1 = $TRUE
            BREAK
            }
        }
        while($ro -le 147)
            If($FOUND1 = $TRUE){
                BREAK
                }

    }

    foreach($col in $column){
        $FOUND2 = $FALSE

        do {
            $col++
       
            if($ws.Cells.Item($Ro, $Col).Text -eq $reqdate){
                $FOUND2 = $True
                break
                }
        }
        while($col -le 147)
            if($FOUND2 -eq "TRUE"){
            BREAK
            }
    }    
                
        $RO+=1
       
     foreach($WDID2 in $WDID1){
        if($WDID2 -eq $WDIDPath3){
        $ws.Cells.Item(2,1).Value2 = $WD
        $ws.Cells.Item(2,2).Value2 = $ID
        $ws.Cells.Item($RO,$COL).Value2 = $CFS1
        $ws.Range("Ditch_Name").Value2 = $Structure_Nam1
     
     }

    }


    $xl.quit()
    }
    Get-TodayMeas
    Read-MsgBox -message "Data Transfer from Xmap to Excel is complete!" -title "Finished" -icon Information -buttons OKOnly

    I had to create another stored procedure to hit each individual WDID record and use WDID as a parameter in my SP.

    There's still some features that I'm going to this script and probably turn into an actual form with all the bells and whistles.

    Cheers,


    DJV
    • Marked as answer by DJV Wednesday, October 27, 2010 10:14 PM
    Wednesday, October 27, 2010 10:13 PM

All replies

  • Can this not be done? Is there no solution?

    DJV
    Thursday, October 21, 2010 4:58 PM
  • Hi,

    For quicker help, you might try posting a more concise problem description. For example: A very, very short script that illustrates just the specific problem, and any/all error messages you're getting.

    HTH,

    Bill

    Thursday, October 21, 2010 6:37 PM
  • Thanks Bill for a reply. I've been waiting for something.

     

    Yeah I'm not getting any error messages.  The bold lettering in the script above is where the data is inserted and it just does the first value in each spreadsheet. And what i want is the different values from the stored procedure. So when I run the stored procedure it gives an output like this:

    WDID               CFS        structure_name
    -------              ------       ------------------
    1400501           1.45        145 ditch
    1500500           0.625      234 ditch
    1500501           0.387      678 ditch

    I'm parsing out the WDID for the WD(first 2 of the WDID) to go into cell A2 and ID (last 3) to go to B2 then structure name to go to C2 then for the CFS it matches the date because the sp is based on the date. So, for each spreadsheet created(if it doesn't exist) or opened the values for CFS should go to the row below the date in the spreadsheet. Each spreadsheet is named WDID.xlsm, so in this instance, obviously, we'll have 1400501.xlsm and inserted into A2 should be 14, B2 will be 501 and so on. What happens when I run the script as is, it will open up each spreadsheet but it puts the same value in the specified cells. So it'll put 14 in A2 and 501 for B2 in each spreadsheet when the next spreadsheet(1500500.xlsm) should have 15 in A2 and 500 in B2 and so on. Let me know if you need more clarification on this.

    Thanks,


    DJV
    Thursday, October 21, 2010 6:51 PM
  • It looks like you're wanting to automate Excel in some fashion. I can't help with that as I don't use Excel. You might have better results if you ask your question in an Office/VBA forum.

    HTH,

    Bill

    Thursday, October 21, 2010 9:28 PM
  • No, that does not help. Passing the buck does not help. Why respond if you don't even use Excel? If you don't know how to help don't tell me to go somewhere else. If I were to post this in a Office/VBA forum they would tell me to go to a Windows Powershell forum, thus the run around.

    This project must be finished and I'm almost there. If anyone else can help out (other than telling me to go somewhere else) please do.

    Thanks,
    DJV


    DJV
    Monday, October 25, 2010 2:39 PM
  • Hi,

    I can see that you're unhappy with the answer, but please remember that discussion forums do not come with a service-level agreement.

    Bill
    Monday, October 25, 2010 3:32 PM
  • Really?
    DJV
    Monday, October 25, 2010 3:36 PM
  • Hi,

    If that is sarcasm, I am sorry you are disappointed. However, keep in mind that the people that answer questions in these forums are volunteers and try to help others out for free. You might consider that people might be dissuaded from helping if you treat them rudely.

    Bill

    Monday, October 25, 2010 3:48 PM
  • Simply responding back to your sarcasm about SLAs. C'mon man I understand that these forums are not SLAs in any way shape or form. But if you're willing to help that's fine, but don't give an answer of going somewhere else. I've read some of your other posts for other issues, and it seems that you give an answer of going somewhere else when you don't know the answer. Now whether those are legitimate answers or not is not for me to decide but in this case it's definitely not. And I really do appreciate yourself and others trying to help the community figure out a variety of scripts. I'm not trying to turn this into mud slinging posts but I just want to figure this script out and move on.

    I've already started this script in Powershell and I know it works, it's just this last step that I need help on and I'm not just going to scrap this and start all over in VBA. I know it can be done, and the logic just needs to be ironed out, which I will if nobody else can help.

    Thanks,


    DJV
    Monday, October 25, 2010 3:58 PM
  • Hi,

    You are right, I don't know the answer to your specific question (I already posted that I don't use Microsoft Excel). That's why I suggested you might get some help from people that use Excel automation. Sorry you are frustrated, but from the lack of responses here (for whatever reasons) suggests that your questions are not targeted in the right forum. As I have mentioned before, this forum focuses mainly on Windows OS admin scripting, and you are really asking a SQL/Excel question.

    Bill

    Monday, October 25, 2010 4:05 PM
  • DJV, I agree with AbqBill that you might get more help in an Office forum.  Your topic straddles two subject areas.  I would definitely try some Office forum too.  But I think the regulations here prohibit cross-posting (which I thought would be ideal for a problem such as yours).  It would be best to ask a moderator to move your post once you have identified the other forum.
    Tuesday, October 26, 2010 12:00 AM
  • I've already started this script in Powershell and I know it works, it's just this last step that I need help on and I'm not just going to scrap this and start all over in VBA. I know it can be done, and the logic just needs to be ironed out, which I will if nobody else can help.
    DJV
    Tuesday, October 26, 2010 2:29 PM
  • Hi DJV,

    Your question is really about connecting to a SQL database and getting data from it into an Excel spreadsheet. Granted, you're doing it with Excel automation in PowerShell, but it's really not a PowerShell question. My advice to you is to come up with something in Excel VBA that illustrates your specific problem, post it to a VBA or SQL server forum, and then translate it back into PowerShell once you've found out what the specific problem is.

    HTH,

    Bill

    Tuesday, October 26, 2010 2:42 PM
  • Thanks Bill. The thing is, is that I can already get the data from SQL Server into my spreadsheet, so I'm still not convinced that its an Excel or SQL Server issue. I just need to get the data in each row of my stored procedure in each spreadsheet that is created or exists. The name of each spreadsheet is the WDID that is in my stored procedure so I'm thinking maybe matching the name of the spreadsheet to the WDID in the stored procedure and then filling in the cell with the data.
    DJV
    Tuesday, October 26, 2010 3:24 PM
  • The name of each spreadsheet is the WDID that is in my stored procedure so I'm thinking maybe matching the name of the spreadsheet to the WDID in the stored procedure and then filling in the cell with the data.

    ...which is an Excel automation question, not a Windows OS admin scripting question.

    Bill

    Tuesday, October 26, 2010 3:26 PM
  • Not to open another can of worms, but I understand its an Excel automation problem and not a Windows OS problem. But I have yet to read anything in this forum about only posting Windows OS scripting issues and there are a bunch of non-Windows OS posts that are answered daily.

    But I did end up figuring out this damn script, so I'm marking this post as the answer.

    If anyone is interested here's the code for the issue.

    set-variable date -scope script
    $date = Read-InputBox -message "Enter a date that the diversion record was observed." -title "Daily Diversion Date"

    Function Get-TodayMeas{
        $SRVR = "$ENV:COMPUTERNAME\XMAP7"
        $c = new-connection -server $srvr

        $DB = get-sqldatabase -sqlserver $SRVR -dbname TESTING1

    #$TABLE = get-sqltable -database $DB -name "CHANGES_2"

        $GetspCFS =get-sqlstoredprocedure -database $DB|?{$_.Name -eq 'spGETCFS'}
       
        $CFSname = $GetspCFS.Name
       
        $GETCFS = invoke-storedprocedure -connection $c -storedprocname $CFSName -parameters @{reqdate = $date}
       
        $WDID1 = @(
            foreach($WDID in $GETCFS)
            {
                $WDID[1]
            }
        )

    foreach($WDID2 in $WDID1){
        $WDID2
       
        $GetspWDID = get-sqlstoredprocedure -database $DB|?{$_.Name -eq 'spGETCFSWDID'}
        $WDIDName = $GETspWDID.Name
        $GetspWDID1 = invoke-storedprocedure -connection $c -storedprocname $WDIDName -parameters @{reqdate = $date;XmapWDID = $WDID2}
       
         $Structure_Nam1 = @(
            foreach($Structure_Nam in $GETspWDID1)
            {
                $Structure_Nam[2]
            }
        )
       
        $CFS1 = @(
            foreach($CFS in $GETspWDID1)
            {
                $CFS[0]
            }
        )
        $CFS1
        $Structure_Nam1
        $wdidpath = "C:\DiversionRecs\$WDID2.xlsm"
        $testpath = Test-path $WDIDpath

        if($testpath -eq $FALSE){
            $filemsg = Read-MsgBox -message "$WDID2.xlsm does not exist! Would like to create it?" -icon Question -buttons YesNo
       
            if($filemsg = "YES"){
                $xl2 = new-object -com Excel.Application
                $wb2 = $xl2.Workbooks.Open("C:\DiversionRecs\TemplateWDID.xltm")
               
                $WDIDPath1 = $WDIDPath.Split("\")
                $WDIDPath2 = $WDIDPath1[2]
                $WDIDPath3 = $WDIDPath2.Substring(0,7)
                           
                $WD = $WDID2.substring(0,2)
                $ID = $WDID2.substring(4,3)
               
                $wb2.SaveAs($WDIDPath, 52)
                $xl2.Quit()
               
               Update-Record $reqdate
                }
               
            elseif ($filemsg -eq "NO"){
                Read-MsgBox -message "Hit Ok to Close..." -Icon Warning -Title "Close" -buttons OKOnly
                }
           
            }

        else{
            Update-Record $reqdate
           
            }
           
       }
       }
         
    function Update-Record($reqdate){

        $reqdate = $date
       
        $reqdate1 = $reqdate.Split("/")
        $imon = $reqdate1[0]
        $iyr = $reqdate1[2]
        $mon =
            Switch($imon){
            11 {"NOV"}
            12 {"DEC"}
            1 {"JAN"}
            2 {"FEB"}
            3 {"MAR"}
            4 {"APR"}
            5 {"MAY"}
            6 {"JUN"}
            7 {"JUL"}
            8 {"AUG"}
            9 {"SEP"}
            10 {"OCT"}
            }
       
        $xl = new-object -comobject excel.application
        $xl.Visible = $true
        $wb = $xl.Workbooks.Open($WDIDPath)
       
        if(($mon -eq 11) -or ($mon -eq 12)){
        $sheetname = {
        Switch($iyr){
        2010{"2011"}
        2011{"2012"}
        2012{"2013"}
        2013{"2014"}
        2014{"2015"}
        2015{"2016"}
        2016{"2017"}
        }
        }
        }
        else{
        $sheetname = $iyr
        }
          
        $ws = $wb.WorkSheets.item($Sheetname)
        $ws.Activate()
       
       
       
        set-variable ro -scope script
        $Column = @(14..44)

    foreach ($ro in $row){

        $Found1 = $False
        do{
            $ro++
            $col = 13
            $Range = $ws.Cells.Item($ro,$col)
        if($range.value2 -eq $mon){
            $FOUND1 = $TRUE
            BREAK
            }
        }
        while($ro -le 147)
            If($FOUND1 = $TRUE){
                BREAK
                }

    }

    foreach($col in $column){
        $FOUND2 = $FALSE

        do {
            $col++
       
            if($ws.Cells.Item($Ro, $Col).Text -eq $reqdate){
                $FOUND2 = $True
                break
                }
        }
        while($col -le 147)
            if($FOUND2 -eq "TRUE"){
            BREAK
            }
    }    
                
        $RO+=1
       
     foreach($WDID2 in $WDID1){
        if($WDID2 -eq $WDIDPath3){
        $ws.Cells.Item(2,1).Value2 = $WD
        $ws.Cells.Item(2,2).Value2 = $ID
        $ws.Cells.Item($RO,$COL).Value2 = $CFS1
        $ws.Range("Ditch_Name").Value2 = $Structure_Nam1
     
     }

    }


    $xl.quit()
    }
    Get-TodayMeas
    Read-MsgBox -message "Data Transfer from Xmap to Excel is complete!" -title "Finished" -icon Information -buttons OKOnly

    I had to create another stored procedure to hit each individual WDID record and use WDID as a parameter in my SP.

    There's still some features that I'm going to this script and probably turn into an actual form with all the bells and whistles.

    Cheers,


    DJV
    • Marked as answer by DJV Wednesday, October 27, 2010 10:14 PM
    Wednesday, October 27, 2010 10:13 PM