1. Home
  2. Docs
  3. InfoCaptor
  4. Data Sources
  5. Microsoft Excel

Microsoft Excel

Microsoft Excel Files can be used as database to store information and this data can be visualized on the dashboards.

In order to bring data from Excel to the dashboards there are some basic rules to be followed Organize your data

Rules to be followed

  • The first row in excel should be the column names
  • Make sure the column names contain only alphanumeric characters
  • It should not contain any special characters
  • The worksheet name should contain only alphanumeric characters
  • There should be no blank characters
  • Make sure the first row immediately following the column names contains actual data (this will dictate what type of data the column contains)
  • If your data is all around in Excel, then create a named range for the section

You can have  multiple sheets within excel and each worksheet is treated like a database table.

For our example, consider this simple Excel file 

It contains one sheet1, two columns (period and amt)

Create a connection the Excel File

Go to Actions >> Manage Data Sources/Connections

Define Connection Handle

Give a proper name to the connection handle

Give the exact path to the xls file on your windows machine

Select what type of windows and Excel version you have

Click on “Add new Connection Entry”

Once clicked it will show the above successful message. If you had a previous connection handle defined with the same name then it will overwrite it. 

How to Query your Excel File

 Drag a grid component from left to the right panel

Right click on the Grid and select “Data Source”

From the drop down, select the newly defined connection

NOTE: You will need to refresh the application from the browser if you don’t see your connection

Enter the Query (SQL)

NOTE: Before you execute the query make sure your excel file is closed. Excel files are locked when open and we cannot query it. So close the excel file.

Each worksheet is referenced like [sheet_name$] and you can execute quite advanced SQL against this table.

For our case the sql would be to list all the columns

select * from [sheet1$]

The [*] in the above query tells it to bring all the columns in the worksheet

Click OK to execute the SQL

As you see the grid is now populated with the data from the sheet 

How to select specific columns from the Sheet 

select period , amt from [sheet1$] 

The above will result in the same display as above as it contains only those two columns If you select only period you will get this

Notice how it automatically gives a column name. To avoid such non-functional column names we can provide a name in the query itself

Was this article helpful to you? Yes No

How can we help?