DataRails exposes the data extracted from Excel files in a standard called Open Data Protocol (OData). This document describes the syntax to access this data using a REST API.
The basic URL is always: https://app.datarails.com/api/templates/OData.svc
Calling this URL for the first time will require authentication using the same username (email) and password used to connect to DataRails. The available tables will be determined by the permissions of each specific user and according to the data that was shared with them.
The server response is always in JSON format.
The first response after authentication contains the list of tables available for the user. The table name is constructed from the FileBox name + the name of the extracted table + a numeric ID, such as testingodata_testing_7004. This is referred to as <table name> in the following paragraph.
Each basic table will appear in the list in four different variations:
- <table name> returns the latest version including extracted data + special metadata columns added by DataRails
- <table name> (Cleared) returns the latest version without the special metadata columns
- <table name> (Full) returns data from all the versions submitted to the FileBox and designed as “included in dashboard.”
- <table name> (Full)(Cleared) should be disregarded
Each variation of a table has a unique ID that can be used to query the data in the table.
The output may look like this:
<service xmlns="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xml:base="https://app.datarails.com/api/templates/OData.svc/">
<workspace>
<atom:title>Default</atom:title>
<collection href="MGY4Y2MyZDItOWU2OC00OWM3LTg4MWItNmQ3NTQ4YmNkY2NjTXgkUDcwNg==">
<atom:title>Financial report_Consolidated pnl_5628</atom:title>
</collection>
<collection href="MWU3ZDlhODUtM2NmZi00OWZlLThlM2UtOWZhZTk1ODk2ZmMyTXgkUDcwNg==">
<atom:title>tiny_tiny_5721</atom:title>
</collection>
<collection href="MWU5Y2JhZWQtMjQ4ZC00NjcwLTgyMTUtNzk5ZjIzM2UwMGFmTXgkUDcwNg==">
<atom:title>short_short_5722</atom:title>
</collection>
<collection href="OWEyYjMzZmMtYWI1NS00YzRmLWFkMWMtYmU4MjVjMjU4NjdmTXgkUDcwNg==">
<atom:title>Monthly Reporting Package-1_YTD Financials_5949</atom:title>
</collection>
<collection href="N2NkOWM0YjYtZWJiNi00ODdiLWExOTktMDJiMTQyYWMzNTI1TXgkUDcwNg==">
<atom:title>Monthly Reporting Package-1_Bookings Monthly_5950</atom:title>
</collection>
<collection href="MDI0YWZhYmQtNWViOS00MmU4LWJhNzMtYTUwMDQ0ZDhkZTYwTXgkUDcwNg==">
<atom:title>Forecast_Revenue_6252</atom:title>
</collection>
<collection href="NWMyZjEwZmItODgxOS00MjMwLTlhZDItN2IxMGUwODhkMTk2TXgkUDcwNg==">
<atom:title>Companies Evaluations_Future Value_6475</atom:title>
</collection>
<collection href="NDRlNzg1MDgtMzRmZS00OTRiLTg1ZWEtM2JlMmNjZDRkMDVkTXgkUDcwNg==">
<atom:title>Companies Evaluations_Company Valuation_6474</atom:title>
</collection>
<collection href="MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg==">
<atom:title>Budget_Budget Summary_6482</atom:title>
</collection>
…
The green colored value of the href property is the one used to query the data.
To see the list of all columns and their types, visit:
https://app.datarails.com/api/templates/OData.svc/$metadata
The response might be something such as:
<edmx:Edmx xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx" Version="1.0">
<edmx:DataServices xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" m:DataServiceVersion="2.0">
<Schema xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://schemas.microsoft.com/ado/2007/05/edm" Namespace="ODataDR">
<EntityType Name="Financial report_Consolidated pnl_5628">
<Property Name="Doc_ID" Type="Edm.String" Nullable="true"/>
<Property Name="Doc_version" Type="Edm.String" Nullable="true"/>
<Property Name="Tab_name" Type="Edm.String" Nullable="true"/>
<Property Name="Label" Type="Edm.String" Nullable="true"/>
<Property Name="Submission_Date" Type="Edm.String" Nullable="true"/>
<Property Name="User" Type="Edm.String" Nullable="true"/>
<Property Name="Category" Type="Edm.String" Nullable="true"/>
<Property Name="Item" Type="Edm.String" Nullable="true"/>
<Property Name="Periods" Type="Edm.String" Nullable="true"/>
<Property Name="value" Type="Edm.String" Nullable="true"/>
<Property Name="table_id" Type="Edm.String" Nullable="true"/>
</EntityType>
...
As can be seen in the example, all columns have the identical type of string. The reason is that the source of the data is Excel, which is not typed at all. In columns that should contain numbers or dates, values like Q2 or “No Data” or #REF are often seen. These values will not work in a numeric or a date type, and if declared as such, the query will fail.
Basic Queries
After deciding on the desired table, users can request its content by using a URI like:
The value after the last slash (/) is the href of a specific table.
Building on top of this basic syntax, users can add parameters that will modify the result. For example,
will return the 10 first rows in the table;
will skip the first 10 rows; and
will skip 10 rows and return the first 10 rows after skipping.
Selecting Columns
To view specific columns in the table, use the syntax:
Filtering Data
Users can filter data with a syntax such as:
The above query filters on two columns: Line_Item=“Admin Allocation” and (Period=“APR” or Period=“AUG”)
In the above case, the column Line_Item should contain the substring “Sales.”
Sorting the Results:
To request the data sorted by the Period column ascending, use the following URI:
In addition,
will return the rows on descending order of Period; and
will return the data ordered by Period descending and then Line_Item ascending.
Special Characters in Column Names
The OData protocol doesn’t allow for certain special characters to appear in column names. These special characters are replaced by other strings, and thus the columns can appear awkward.
Examples are:
“-” is replaced by “_minus_“
“(“ is replaced by “_obrak_“
“)” is replaced by “_cbrak_”
To avoid these replacements, refrain from using special characters in column headers or simply use different names in the report.