Introduction

The Call Journey Databridge service permits users to import Voice Data directly into a database or into a Business Intelligence (“BI”) application such as Excel, Power BI, Tableau, Amazon QuickSight and Emite. Any database or BI application that supports importing from a PostgreSQL database can use Databridge.

Set out below are the steps required to import Voice Data into Excel using the The Call Journey Databridge service.

This is followed by some common SQL commands that can be used to manage the amount of data that is imported into Excel.

How to Import Data into Excel with ODBC

The Databridge Service connects to a PostgreSQL database.

Three steps are required to connect to the PostgreSQL database and import data into Excel:

1. Installing the ODBC Connector (Microsoft Open Database Connectivity)

2. Opening the Excel file and connecting to the database using ODBC

3. Refreshing the data in Excel

Each of the above steps are detailed below.

Installing the ODBC Connector

1. Download and install the PostgreSQL ODBC driver.

2. Check the bit version of your Excel (32 or 64 bit).

3. Click on the Windows start button and type “odbc” and choose the bit version of ODBC Data sources that matches your Excel bit version.

4. In the ODBC Data Source Administration window, click on the Add button and find the previously installed PostgreSQL Unicode.

5. Complete all the necessary information provided below using your Databridge credentials.

Give you Data Source a name such as PostgreSQL35W.

Port can be left empty.

6. Change SSL Mode from ‘disable’ to ‘require’

7. Click on the Test button and if successful, Save your configuration.

Connecting Excel to the Database

Before loading the data, you will need to connect the Excel file to the database you created above.

To connect to the Database, open Excel and select Data → Get Data → From Other Sources → From ODBC

This will bring up a window where you can edit the Data Source name. Select the drop-down box and select the name you gave your database:

Enter any SQL statements (see below for more details) in the SQL box and select OK.

Refreshing the Data in Excel

1. Select ‘Data’ and click on ‘Refresh all’.

2. Make sure to follow step 1 for every excel sheet once you refresh data.

SQL Commands

The PostgreSQL database can be very large so you may want to limit the amount of data being imported.

Below are some of the more common methods to load all data from a table or to limit the data imported.

SQL command to load all data

Change the SQL command to:

SELECT * FROM public.generated_analytics_data 

Limiting the data to a set number of rows

In order to load only certain rows of data you can add LIMIT followed by the number of rows. For example, SQL command to load only 2000 rows:

SELECT * FROM public.generated_analytics_data LIMIT 2000

Loading selected columns

To load specific columns, you can write the column names instead of *

For example:

SELECT scorecard, filename, insertid, agentid, duration, url, datetime, folder FROM public.generated_analytics_data  LIMIT 2000

Loading calls for particular dates

For loading calls on a particular date, add “ where CAST(datetime AS DATE) = 'yyyy-mm-dd' ”. For example, loading 2000 calls for 1 February 2022:

SELECT scorecard, filename, insertid, agentid, duration, url, datetime, folder FROM public.generated_analytics_data where  CAST(datetime AS DATE) = '2022-02-01'
LIMIT 2000

To load calls for a date range, add “ where CAST(datetime AS DATE) between 'yyyy-mm-dd' and 'yyyy-mm-dd' ”

For example, loading 2000 calls from 31 January 2022 to 2 February 2022:

SELECT scorecard, filename, insertid, agentid, duration, url, datetime, folder FROM public.generated_analytics_data where CAST(datetime AS DATE) between '2022-01-31' and '2022-02-02'
LIMIT 2000

NOTE: If your Limit exceeds the number of calls then you may receive an error.