Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: Using ODBC Connection to Query Data from an Excel File


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.

1 Comment

  1. Dominic Beland on March 30, 2021 at 1:42 pm

    How should we script it in order to add a header when the excel file has no header?

    I can do Select * from [Sheet1$A:G]

    but I have no clue how to change the “*” to proper column aliases

Leave a Comment





RECENT POSTS


Tech Tuesday: Building My First Matrix Report
Overcoming the Top Challenges of Zendesk Integration
Accessing Historical Dynamics GP Data in NetSuite: Using Popdock
Popdock's Top 10 New Features
New Webinar: Getting Started with Matrix Reporting in Popdock

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.