First, I have the data in an Excel sheet that I want to dashboard. It has a couple of text columns (Email, Organization and Designation), and a date column (Registered At).
Before we start, there are a couple of things to note.
-
You need a relay agent on the server where the file is stored.
-
In the excel itself there must be 3 specific columns – “sourceId”, “name”, and “type” – in THAT order. If your sheet doesn’t have any data pertaining to these columns, you can enter any random distinct values.
-
Columns names must not contain any spaces in it. For example, “Registered At” becomes “registeredAt”, “Display Name” becomes “displayName” and so on.
Let’s go ahead and deploy the ODBC data source. Log in your SquaredUp console and look for ODBC in data sources.
Select the right Agent group corresponding to the agent where you’ve stored your excel sheet.
Next up is to create a Connection String to that excel sheet. Most likely, the connection string you’ll need will be in the following format.
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=[path to your file];
So for me it becomes
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\SqupAgent\ExcelFile\Registrations.xlsx;
Next we move on to Object Query. Do note that while it does not list it as a mandatory field here, I’ve noticed that it does not work if you keep it blank.
So the query here is in the following format:
Select sourceId, name, type, , from [<name of the sheet$>]
Note that you’ll enter the name of the sheet here, not the name of the excel file. So for me, this becomes
Select sourceId, name, type, emailAddress, registeredAt,Organisation,Designation from [Sheet1$]
You can leave the “Relationship Query” field blank. Now go ahead and save it.
In a minute, it should list the number of all the records you have as the number of objects it has discovered.
Now you can shape this data as you want treat it as any other dataset in SquaredUp.