Automated API cURL command with SQL to upload data

Welcome Forums General PowerShell Q&A Automated API cURL command with SQL to upload data

Viewing 9 reply threads
  • Author
    Posts
    • #229669
      Participant
      Topics: 1
      Replies: 4
      Points: 3
      Rank: Member

      I’m hoping someone can help me and point me to some useful resources.

      I’ve been tasked with creating an export of data from SQL which needs to be in the json format, and then bulk upload to create records on a third parties cloud based software. The documentation requires me to upload the data via a cURL command. I have produced an SQL query which can output the data into json, and with the integration of SQL in PowerShell, I wondered if it would be the right tool for the job?

      The example command I have requires the json data to be read from a file, which can then be uploaded via the web link using an api key.

      I’ve been looking into how this could be achieved, and have found articles on using Invoke-WebRequest, but not seen much with regards to reading a json file containing the data to upload. Is this possible in PowerShell? The version installed on the machine is 5.1.

      The example command is:

      curl -F "file=@/path_to_file/filename.json" https://site-domain-name/api/enrollearners?apikey=yourkey

    • #229804
      Participant
      Topics: 12
      Replies: 1623
      Points: 2,565
      Helping Hand
      Rank: Community Hero

      The -Body would be the JSON payload, which can be retrieved with Get-Content for a file or better to get the JSON directly, but you didn’t specify how the JSON is generated. The -Uri is https://site-domain-name/api/enrollearners?apikey=yourkey. API’s normally want a Method like PUT or POST to SET content, so you should look at the documentation and provide the correct -Method.

    • #230086
      Participant
      Topics: 1
      Replies: 4
      Points: 3
      Rank: Member

      Thank you for the response I will look into Get-Content.

      In answer to you question about the JSON, I’m generating the it directly using sql and the FOR JSON clause. I’m attempting to build this into the full script, but am having difficulty so far. I’m using the bcp utility to try and run and then export the data into a JSON file, but get the error:

      Copy direction must be either ‘in’, ‘out’ or ‘format’.

      I have included the copy direction, but not sure why this error is being returned. Here is my script so far:

      $bcp = "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe"
      $db = "DB_Server_Name"
      $view = "SELECT * FROM sql_view_name FOR JSON PATH, ROOT ('data')"
      $path = "C:\temp\json_file_name.json"
      $bcpCommand = "$($view) out $path -S $db -T -E -n"
      & $bcp @bcpCommand

      Am I missing something in order for this to run correctly?

    • #230095
      Participant
      Topics: 12
      Replies: 1623
      Points: 2,565
      Helping Hand
      Rank: Community Hero

      There are Powershell commands to execute SQL. With the SQL Management studio installed, it additionally installs a Powershell module. I’ve not returned JSON from a command, but typically I return the data I need and then use ConvertTo-Json. Use Invoke-SqlCmd:

      $sqlCmd = @"
      SELECT * 
      FROM sql_view_name
      "@
      
      $results = Invoke-Sqlcmd -Query $sqlCmd -ServerInstance "MyComputer\MainInstance" 
      

      Once I have the results, which we’ll just say is a FirstName and LastName, then you can build the JSON like this:

      $results = [pscustomobject]@{
          FirstName = 'John'
          LastName = 'Smith'
      }
      
      #Add data root
      $data = [pscustomobject]@{
          data = $results
      }
      
      $body = ConvertTo-Json -InputObject $data
      

      Output:

      {
          "data":  {
                       "FirstName":  "John",
                       "LastName":  "Smith"
                   }
      }
      
      • This reply was modified 1 week, 5 days ago by Rob Simmers.
      • This reply was modified 1 week, 5 days ago by Rob Simmers.
    • #230257
      Participant
      Topics: 1
      Replies: 4
      Points: 3
      Rank: Member

      Thank you again Rob.

      I have something to generate the JSON now, however the structure I require needs to have an additional array in the JSON. The structure I need is as follows:

      {
      "data":[
      {
      "firstname":"John",
      "lastname":"Smith",
      "courses":[
      {
      "projectid":"1",
      "courseid":"1",
      "startdate":"01/09/2020"
      }
      ] }
      ] }

      In my SQL view I have setup the aliases for the array sections to courses.projectid, courses.courseid and courses.startdate. This works when using the FOR JSON, this doesn’t work using ConvertTo-Json. Can the sql aliases still get picked up using ConverTo-Json, or do I need to add something else for this to work?

    • #230374
      Participant
      Topics: 12
      Replies: 1623
      Points: 2,565
      Helping Hand
      Rank: Community Hero

      On the Aliases, I would say no, that’s an internal method of grouping in SQL. Two choices:

      • Use SQL to build the JSON – You could probably return the JSON as like a nvarchar(max), but don’t know how large this file would be with the number of records you are getting, but you could do something like this:
        $sqlCmd = @"
        SELECT JSON_RESULT 
        FROM (
            SELECT * 
            FROM sql_view_name
        )
        "@
        
        $results = Invoke-Sqlcmd -Query $sqlCmd -ServerInstance "MyComputer\MainInstance"
        $body = $results.JSON_RESULT
        
      • Use Powershell to build it the JSON – The data from SQL would be a relational structure and you are getting a flat result back, so it would be more like this:
        $data = @()
        $data += [pscustomobject]@{
            'id'                = 1
            'firstname'         = 'John'
            'lastname'          = 'Smith'
            'courses.projectid' = 1
            'courses.courseid'  = 1
            'courses.startdate' = Get-Date
        }
        $data += [pscustomobject]@{
            'id'                = 1
            'firstname'         = 'John'
            'lastname'          = 'Smith'
            'courses.projectid' = 2
            'courses.courseid'  = 2
            'courses.startdate' = Get-Date
        }
        $data += [pscustomobject]@{
            'id'                 = 2
            'firstname'         = 'Sally'
            'lastname'          = 'Franklin'
            'courses.projectid' = 3
            'courses.courseid'  = 3
            'courses.startdate' = Get-Date
        }
        $data += [pscustomobject]@{
            'id'                 = 2
            'firstname'         = 'Sally'
            'lastname'          = 'Franklin'
            'courses.projectid' = 2
            'courses.courseid'  = 2
            'courses.startdate' = Get-Date
        }
        
        
        
        $results = foreach ($grp in $data | Group-Object -Property id) {
            [pscustomobject]@{
                Data = $grp.Group[0] | Select firstname,lastname
                Courses = $grp.Group | Select courses.projectid,courses.courseid,courses.startdate
            }
        }
        
        $results
        
        $body = ConvertTo-Json -InputObject $results
        $body
        

        Generating:

        PS C:\Users\rasim> $results
        
        Data                                  Courses                                                                                                                                                                 
        ----                                  -------                                                                                                                                                                 
        @{firstname=John; lastname=Smith}     {@{courses.projectid=1; courses.courseid=1; courses.startdate=5/22/2020 4:24:41 PM}, @{courses.projectid=2; courses.courseid=2; courses.startdate=5/22/2020 4:24:41 PM}}
        @{firstname=Sally; lastname=Franklin} {@{courses.projectid=3; courses.courseid=3; courses.startdate=5/22/2020 4:24:41 PM}, @{courses.projectid=2; courses.courseid=2; courses.startdate=5/22/2020 4:24:41 PM}}
        
        
        
        PS C:\Users\rasim> $body
        [
            {
                "Data":  {
                             "firstname":  "John",
                             "lastname":  "Smith"
                         },
                "Courses":  [
                                "@{courses.projectid=1; courses.courseid=1; courses.startdate=05/22/2020 16:24:41}",
                                "@{courses.projectid=2; courses.courseid=2; courses.startdate=05/22/2020 16:24:41}"
                            ]
            },
            {
                "Data":  {
                             "firstname":  "Sally",
                             "lastname":  "Franklin"
                         },
                "Courses":  [
                                "@{courses.projectid=3; courses.courseid=3; courses.startdate=05/22/2020 16:24:41}",
                                "@{courses.projectid=2; courses.courseid=2; courses.startdate=05/22/2020 16:24:41}"
                            ]
            }
        ]
        
    • #230572
      js
      Participant
      Topics: 29
      Replies: 814
      Points: 2,456
      Helping Hand
      Rank: Community Hero

      By the way, curl.exe comes with Windows 10 now. I use it when I don’t want to send any headers.

      • This reply was modified 1 week, 2 days ago by js.
    • #232717
      Participant
      Topics: 1
      Replies: 4
      Points: 3
      Rank: Member

      Thank you for the responses, I’m almost there with it now.  But I’ve noticed that my output doesn’t include the square brackets for a json array.

      Any ideas why this might be, I’ve ran my json through an Invoke-WebRequest and and Invoke-RestMethod, but no users appear to have been created on the third party software, and I’m wondering if it is because of the square brackets issue?

      This is my code in full now:

      $sqlCmd = @"
      SELECT *
      FROM SQL_View
      "@
      
      $results = Invoke-SqlCmd -Query $sqlCmd -ServerInstance "SQL_DB"
      
      $data = foreach ($grp in $results | Group-Object -Property idnumber){
      [ordered]@{
      data = $grp.Group[0] | Select email,firstname,surname,idnumber,title,gender,nino
      courses = $grp.Group | Select courses.projectid,courses.courseid,courses.startdate
      }
      }
      
      $body = ConvertTo-Json -InputObject $data
      
      $sendInfo = Invoke-RestMethod -Uri 'https://site-domain-name/api/enrollearners?apikey=mykey' -Body $body -Method 'POST' -ContentType "application/json"

      JSON output:

      {
          "data":
              {
                  "email":"john.smith@somewhere.com"
                  "firstname":"John",
                  "lastname":"Smith",
                  "idnumber":"1234",
                  "title":"Mr",
                  "gender":"M",
                  "nino":"AB123456C"
              "courses":
              {
                  "projectid":"1",
                  "courseid":"1",
                  "startdate":"01/09/2020"
              }
          }
      }

      JSON output needed:

      {
          "data":[
              {
                  "email":"john.smith@somewhere.com"
                  "firstname":"John",
                  "lastname":"Smith",
                  "idnumber":"1234",
                  "title":"Mr",
                  "gender":"M",
                  "nino":"AB123456C"
              "courses":[
              {
                  "projectid":"1",
                  "courseid":"1",
                  "startdate":"01/09/2020"
              }
              ]
          }
          ]
      }
    • #232792
      Participant
      Topics: 12
      Replies: 1623
      Points: 2,565
      Helping Hand
      Rank: Community Hero

      Try making them an array:

      $data = foreach ($grp in $results | Group-Object -Property idnumber){
      [ordered]@{
      data = @($grp.Group[0] | Select email,firstname,surname,idnumber,title,gender,nino)
      courses = @($grp.Group | Select courses.projectid,courses.courseid,courses.startdate)
      }
      }
      
    • #232825
      Participant
      Topics: 1
      Replies: 4
      Points: 3
      Rank: Member

      Thanks Rob, I’ve just made the amendment, although it has change the output to the following:

      [
          {
          "data":[
              @{"email":"john.smith@somewhere.com";"firstname":"John";"lastname":"Smith";"idnumber":"1234";"title":"Mr";"gender":"M";"nino":"AB123456C"}
          ],
              "courses":[
              @{"projectid":"1";"courseid":"1";"startdate":"01/09/2020"}
              ]
          }
      ]
Viewing 9 reply threads
  • You must be logged in to reply to this topic.