Importing Data To Spire

The post is going to show how you can use Excel to import data into Spire. Although Spire has extensive importing ability, there are a few reasons why you might want to do it yourself.

  • Spire may not support the import you need.
  • Spire is an “all or nothing” import.
  • You may want to just add one or two records at a time.
  • You may want to use a computer that does not have Spire.

The first step is to create a new spreadsheet with two columns for your territory code and territory name.

Next, define the cells as a ‘named range’ called ‘Territories’.

  • highlight the cells you want to import, plus the extra empty cells
  • right-click on your selection and choose ‘Define Name’
  • call the range “Territories”

Now we need the code. The following code needs to be copied and pasted into a module inside the spreadsheet.

  • copy the entire block of code below to the clipboard
  • hit Alt-F11 in Excel
  • click on “VBAProject (Book1) on the left
  • from menu Insert, select Module
  • In the blank module that appears, paste the code.

For this code to run, you need to add a reference to your Excel project. While in the Excel code window, select the menu Tools… References. Scroll down to find “Microsoft XML, v6.0” (or the closest match) and click Ok.

The Code

This code needs to be configured to your environment. Scroll until you see the green section in the function “AddTerritory”. The instructions are in the code.

Public Function POSTSpire(SpireDB As String, Endpoint As String, SendString As String, HostName As String, UserName As String, Password As String) As String

Dim strServiceURL As String

strServiceURL = HostName & ":10880/api/v2/companies" & "/" & SpireDB & "/" & Endpoint & "/"
'  Make the API Call
Dim httpReq As New MSXML2.ServerXMLHTTP60

httpReq.SetOption 2, httpReq.GetOption(2) - SXH_SERVER_CERT_IGNORE_CERT_DATE_INVALID

httpReq.Open "POST", strServiceURL, False, UserName, Password
httpReq.setRequestHeader "Content-Type", "application/json; charset=utf-8"
httpReq.setRequestHeader "Accept", "application/json"

httpReq.send SendString

' gather HTTP response info

Debug.Print httpReq.responseText
Debug.Print httpReq.Status
Debug.Print httpReq.statusText
Debug.Print httpReq.getAllResponseHeaders

' return the status information from the API call back to the calling routine
POSTSpire = httpReq.Status & "-" & httpReq.statusText & "-" & httpReq.responseText

End Function

Public Function AddTerritory(code As String, name As String) As String

Dim strJson As String

' sample Json to add a territory
strJson = "{""code"":""!code!"",""!name!"":""my territory""}"

' replace the elements with the code and name we are adding
strJson = Replace(strJson, "!code!", code)
strJson = Replace(strJson, "!name!", name)

' call the function to post to spire
' passing:  "INSPIRE33" - the spire db name for the company
'           "territories" - the endpoint we are POSTing to
'           strJson - the json string containing the data for the territory
'           "http://localhost" - the host name of the spire server (use IP address or name)
'           "user" - the spire user ID
'           "SpireAssists" - the users password

AddTerritory = POSTSpire("INSPIRE33", "territories", strJson, "http://localhost", "user", "SpireAssists")

End Function

Public Sub AddRangeOfTerritories()

Dim rng As Range
Dim row As Range

' loop through the elements in the range called "Territories"
For Each row In Range("Territories").Rows
    Debug.Print "Code: " & row.Cells(1, 1)
    Debug.Print "Name: " & row.Cells(1, 2)
    ' add the territory to spire and update the cell adjacent to show the result
    row.Cells(1, 3) = AddTerritory(row.Cells(1, 1), row.Cells(1, 2))
Next row

End Sub

Running the Code

The code is now a macro. You can find the macro under View… Macros…View Macros.

The macro is called “AddRangeOfTerritories”. Select it and click Run.

The code updates the third cell in the range with the results from the API. This territory was previously assigned so an error is shown. We are hoping to find a new candidate for this territory very soon.