How to retrieve data from Veezi thru Excel

• Internal Links:
  • From Web dialog box: Basic Advanced | URL parts | Veezi Token to the Header | XML to the Header
  • the Navigator dialog box | Display Options | API data in Navigator | API data in Spreadsheet | Data for One Record | Endpoints
• External Links:
  • Veezi Help Pages: Intro | Auth | Session | Web Session | Films | Film Pack | Screen | Site | Attribute
  • Veezi | Web Site | Support and Resources | Getting Started Guide, the Veezi-er Way (PDF File) | reserve
  • SOAP UI | Web Site | SoapUI Home Page || Blake's Web Site: Veezi to Excel | Veezi API PHP | reserve
  • Blake's Web Site | VeeziHelpDoc || SoapUI Help Doc | Part 1 | Part 2 | Part 3 || SoapUI email from Joe | reserve
  • Source of Information: 40 second Microsoft Excel Video, played within Excel titled: "Import data from a Web page"

Instructions

• Select "Data" from the Excel Menu Tool Bar
• Select "Get Data", the very first icon on the Ribbon
  • a submenu is displayed
  • select the "From Other Sources" menu item
    • another submenu is displayed
    • select the "From Web" menu item

The "From Web" dialog box - Basic

• Go to Top
• The "From Web" dialog box is displayed
• Click the "Advanced" Option button

The "From Web" dialog box - Advanced

• Go to Top
• After clicking the Advanced Option button

URL parts - two text boxes

• Go to Top
• the endpoint we want to use is https://api.us.veezi.com/v1/film
• however we need to break it up into two parts

• place the first part into the first "URL parts" text box
• place the second part into the second "URL parts" text box

• as your entering in the two "URL parts" text boxes,
  • The "URL preview" text box displays the entire URL

• skip the "Command timeout in minutes" text box

HTTP request header parameters - adding the VeeziAccessToken to the Header

• Go to Top
• look at the next section titled: "HTTP request header parameters"
• we want to use two headers, one for the VeeziToken and another for XML
• in the first text box type in, or copy and paste: VeeziAccessToken
• in the second text box type in or copy and paste: 19m3sgh48vcax1p9mmhp4dd87w

• Click the "Add header" button so we can add the XML header info

Token is taken care of, now for the XML

• Go to Top
• two new text boxes are displayed under our token header parameters

HTTP request header parameters - adding the Accept and application/xml to the Header

• Go to Top
• in the first text box type in, or copy and paste: Accept
  • you can also select "Accept" from the drop-down menu for that text box
• in the second text box type in or copy and paste: application/xml
• it should look like the screen shot below

• Go to Top
• now the header parameters are complete:

• We can now click the "OK" button from the "From Web" dialog box.

the "Navigator" dialog box

• Go to Top
• The "Navigator" dialog box is displayed after clicking the "OK" button from the "From Web" dialog box.
• The first screen shot was while I was waiting for something to happen.
  • The "Display Options" has no data underneith it,
  • and the icon to it's right has a little red x in it.
• The second screen shot is what I received.

There's Something in the Display Options

• Go to Top
• In the second screen shot, look at the "Display Options".

• Go to Top
• the "Display Options" are displaying a down button, a yellow folder, and the text: https://api.us.veezi.com/v1/film [1]
• and under that a little icon and the word "Film"
• Note the text towards the right:
  • No item selected for preview
• I'm going to hover my mouse cursor over "Film" and see what happens.

• when you hoover your mouse cusor over either the line with "Film" or the line above it "https://api.us.veezi.com/v1/film [1]"
• the background color of those two lines turns to a very light green
• Now I'll click the "Film" line

Data - displayed thru the Navigator dialog box, not yet in a spreadsheet.

• Go to Top
• the pane to the right that originally displayed: "No item selected for preview"
  • is now filled with data from that API call
• I have three screen shots of me scrolling thru the data
• I am looking for my film's id of "ST00000144"
  • it shows up in the third screen shot

• Go to Top
• There it is on the second row under Id
• It looks like there are 20 films or movies in this list.

Next, and this is big, we're ganna move this data into a table in Excel

• Look for the "Load" button towards the lower right hand bottom of the window.
• I'm going to click "Load" and see if it loads everything to a table in my new spreadsheet

• Go to Top
• It placed it into excel for me, here's a screen shot

Veezi data from an API call to the Spreadsheet

My Little Friend

• Go to Top
• and there in the second row is my little friend
  • ST00000144 our Id that prooved we can do this with one record, which we did not do just now.
• I'll have to try it again and see if the ID thing works with Excel.

Screenshots for One record = ST00000144

URL parts

• Go to Top
• In the screenshot below, look at the second text box for "URL parts".
  • /v1/film/ST00000144
• When we use the second part, without the Id of ST00000144,
  • /v1/film
  • we receive 34 records.

• When adding the Id as ST00000144 to the second part of the URL, we should only get one record.
• we must include a forward slash "/" in front of the ST00000144
  • so you actually type in /v1/film/ST00000144 to that second text box.

HTTP request header parameters

• Go to Top
• We enter the HTTP request header parameters just like we did before.

• Here's a screenshot of the "From Web" dialog box, after all the fields are entered, and just before you would click the "OK" button.

• Go to Top
• This screen shot below was taken after clicking the "OK" button was clicked.
• The retrieved data is displayed in this dialog box.
  • Note that the header of this screen now displays the full URL or Endpoint
    • http://api.us.veezi.com/v1/film/ST00000144
  • Also note that we have one row for headers and a second row for our film with the Id of ST00000144.

• Go to Top
• After scrolling to the right, I see the film's Id as ST00000144.

• Go to Top
• Below is a screen shot of the "Load" button which when clicked, will place the data into an Excel Spreadsheet

• Go to Top
• The screen shot below was taken after using the "Load" button to place the record into an Excel Spreadsheet

• Go to Top
• I just realized that this process automatically renames the Excel tab for worksheets.
• Notice it renamed the tab to ST00000144.

• When I did this exercise previously, and that second text box was loaded with /v1/film, without the Id.
  • That spreadsheet's Excel tab was automatically renamed to Film, as you can see in the above screen shot.

• Look at the panel to the right labeled "Queries & Connections", in that Excel spreadsheet.
  • That panel displays "2 queries" under the "Queries" heading.
  • The panel displays that "Film" has 34 rows.
  • Also in that panel, for "ST00000144", it displays 1 row.

Good Luck

Testing Endpoints

Header Information

• Go to Top

Endpoint Information

1. https://api.us.veezi.com/v1/session
2. https://api.us.veezi.com/v1/session{id}
3. https://api.us.veezi.com/v1/websession
4. https://api.us.veezi.com/v1/film
5. https://api.us.veezi.com/v1/film/{id}
6. https://api.us.veezi.com/v1/filmpackage
7. https://api.us.veezi.com/v1/filmpackage{id}
8. https://api.us.veezi.com/v1/screen
9. https://api.us.veezi.com/v1/screen{id}
10. https://api.us.veezi.com/v1/site
11. https://api.us.veezi.com/v1/attribute
12. https://api.us.veezi.com/v1/attribute{id}

Reserve

• Go to Top
• reserve
• reserve
• reserve
• reserve