Most often when a user needs to use an Excel file for their source data in SmartConnect they choose Microsoft Excel as their Data Source and then choose the Sheet that contains the data they need. This sample pulls all the records from the selected sheet. Another option that will allow more flexibility is to connect to the Excel file with an ODBC Connection. This will allow you to write your own query and use all of the functionality of the ODBC Excel driver directly in SmartConnect. We are going to start with the following Excel file for example.
To start, in Excel we can create a Named Ranged and then query that range specifically to get only the data range we need.
Columns B through G have be defined as a named ranged called “addressinfo.” In SmartConnect we can query the named range to exclude all the additional data in the Excel sheet we do not need at this time.
When you preview this query you will only get data from the named range as it was setup in Excel. One thing you will notice is that it will also select blank rows because our named range infinitely continues on the rows. You could fix this by setting the named range to include a set of rows as well, or you can modify the query a bit so that the rows can dynamically be added and queried later. The new query would be select * from addressinfo where Name <> ”. This will eliminate all the blank rows with the current data and allow the Excel sheet to have data added to it continuously without having to change the query in SmartConnect.
Another option is setting the range directly in the query from SmartConnect. To get the same results as the named range above we can write the query as select * from [Sheet1$B:G]. Previewing the data will return the same results.
If you have data in multiple sheets in Excel you can use a union in the SmartConnect query to combine all the data from multiple sheets. When using unions you can also define the ranges for each sheet. The query does need to return the same number of columns or you will get an error on the union and I would advise that the columns are the same between the sheets otherwise you will get mismatched data.
The above examples focus on getting only the data needed from an Excel file, but using the ODBC connection with an Excel file will also let you perform joins between different sheets, ordering and grouping, expressions and any combination of the different functionality. It can do much more than simply pulling data from an Excel Sheet so give it a try next time you need to create a map with an Excel data source.
