Configuring ESQL/C and ODBC drivers for SSO. The steps for preparing the SQLHOSTS information and the Generic Security Services (GSS) CSM configuration file for ESQL/C and ODBC and a client computer are similar to the corresponding server-side setup procedures. Complete the tasks outlined in Configuring the IBM Informix instance for SSO before. Odbc Performance Benchmarks. The odbc package provides a DBI-compliant interface to Open Database Connectivity (ODBC) drivers. It allows for an efficient, easy way to setup connection to any database using an ODBC driver, including SQL Server, Oracle, MySQL, PostgreSQL, SQLite and others. The implementation builds on the nanodbc C library.
Microsoft ODBC Driver for SQL Server is a single dynamic-link library (DLL) containing run-time support for applications using native-code APIs to connect to SQL Server. Use Microsoft ODBC Driver 17 for SQL Server to create new applications or enhance existing applications that need to take advantage of newer SQL Server features.
Last updated: 02/05/2019The Smartsheet Live Data Connector (also known as the Smartsheet ODBC Driver) provides industry-standard connectivity between Smartsheet and third-party analytics tools. It enables business users to interact with and visualize data stored in Smartsheet using business analytics and data visualization software such as Tableau, Spotfire, QlikView / Qlik Sense, Excel and others.
Note: The Smartsheet Live Data Connector now supports Microsoft Access 2013 and 2016, Spotfire Server 7.0.1, and Spotfire Web Player as of version 1.2Note: The Smartsheet Live Data Connector provides a read-only experience and is not able to write data into Smartsheet.Benefits of using the Live Data Connector include, but not limited to, the following:
- Industry-standard ODBC-based connectivity to Smartsheet. The Connector is compatible with version 3.8 of the ODBC specification.
- Smartsheet-wide access control (ACL) is enforced and data access and retrieval via ODBC is subject to all Smartsheet’s existing security mechanisms.
- Interact with Smartsheet data dynamically, using Smartsheet as a live data source.
- Create charts, graphs and other visualizations with data stored in one or more Smartsheet sheets or reports.
This section provides a fast track to getting you up and running with the Smartsheet Live Data Connector as quickly as possible.
| Download the installer | Run the installer | Configure your software |
In order to use the Live Data Connector you will need:
- Live Data Connector license (included in some plans - see pricing)
- A Smartsheet license
- Microsoft Windows 7, Windows 8, or Windows Server 2012
Additionally, to use the driver you will need access to at least one of these products:
- Tableau Desktop Professional Edition 8.2/8.3/9.x or Tableau Server 8.3.3/9.x
- QlikView 11.2, Qlik Sense 1.1
- Tibco Spotfire Desktop, Server, Web Player 7.0.1+
- Microsoft Excel 2010, 2013, 2016 - Data Connection Wizard, MSFT Query, Power Query
- Microsoft Access 2013 and 2016
If you experience any difficulties, have questions or feature requests, please visit the Smartsheet Help Center.
If you have a technical issue, please provide detailed information on the issue to our Support team at our Support Contact page. To best assist you with the issue, we will likely need the following information:
- Screenshot of the error message
- Exact steps to reproduce the issue
- Attach a copy of logs generated by the Live Data Connector (follow the steps in this section to enable logging.)
Download the most recent installer:
If you are planning to use the Connector with Excel, get the one that matches your version of Microsoft Office (32 bit or 64 bit).This section covers a number of advanced features and additional capabilties included with the Smartsheet Live Data Connector.Some organizations use a proxy server to access web content. If you are using a proxy to access web content, the Smartsheet Live Data Connector will use your proxy server as well. You can check your proxy settings from Internet Options in your Control Panel.
By default the Smartsheet Live Data Connector will use the Windows / Internet Explorer’s proxy settings for the currently logged-in user. You can find more information on overriding proxy settings in the Advanced Options Menu section.
Note: It is rare to need to manually override proxy settings. In most cases, the correct proxy settings will be determined using IE settings.To check your settings, select the Connections tab, and then click on LAN settings at the bottom of the window:
… and if you have a proxy configured, you will see it in your LAN settings (example):
The Smartsheet Live Data Connector will use either the automatic configuration script in the Automatic configuration group box or the proxy server address in the Proxy server group box as valid proxy server configurations.
Note: The Smartsheet Live Data Connector will not use the DHCP or DNS auto-discovery method due to poor performance in some network configurations (the Automatically detect settings checkbox).If the Smartsheet Live Data Connector is running on a Windows Server where it is not impersonating a logged-in user (for example when it is running under the Tableau Server service) only the system default proxy settings will be used. Ask your Network Administrator for more information on configuring Windows Server for proxy access.
If you run into any issues with the Connector, we may ask you to send us your log file to help identify and address the problem. Enabling logging simply creates a text file on your computer and logs the Connector activity.
Note: because the log files will capture the details of what exactly is getting retrieved from Smartsheet, some potentially confidential data - e.g. sheet names, column names, etc. - may be logged.To enable logging, please locate the logging utility by navigating to:
- Start > Programs > Smartsheet Live Data Connector
- Click on Live Data Connector Settings
- Click on Driver Properties
- Check Enable logging
- Click on Folder… to specify the log destination:
Pick a destination folder and click OK:
Note: The easiest approach is to select your Desktop folder so you can easily find the log file laterOnce the destination is selected, click OK to finish:
If you need to set up a Data Source:
- Navigate to Start > Programs > Smartsheet Live Data Connector
- Run ODBC Data Source Admin
Click Add… to add a data source:
Select Smartsheet Live Data Connector and click Finish:
…and then fill in the details. If your Smartsheet account does not permit using email and password for login, you can use the Use API Token for SSO login option instead.
Windows 10
This section only applies to Windows 10
users. If you are using another version of Windows, you may skip this section.If you are using Windows 10, you may have noticed that the option for the ODBC Data Source Admin in your Start Menu is missing. Please follow the steps below to add the Smartsheet DSN on Windows 10.
On your taskbar, click on the search box and type in Control Panel:
Once the Control Panel opens, click on System and Security:
Scroll down and click on Administrative Tools:
Double click on the appropriate shortcut that matches the driver version you installed (32bit or 64bit):
After that, you’ll see the ODBC Data Source Admin window appear, and you can head back to the previous section to create the Smartsheet DSN.
The Advanced Options menu provides controls to include additional row metadata, manage data conversion modes, enable driver logging, and manually override proxy settings.
The menu can be accessed either at the time of using the Connector (e.g., from within Tableau):
…when setting up an ODBC Data Source:
…or by navigating to Start > Smartsheet Live Data Connector > Live Data Connector Settings:
The Advanced Options menu (called Live Data Connector Settings in the Start Menu) has options for including extended metadata (such as Gantt attributes, outline level, and discussions text) as additional columns. It also provides controls for advanced features and logging.
The following tabs and options are available in this menu:
- Connection Properties
- Optional Content - extended metadata as additional columns
- Data retrieval mode
- SQL table names
- Driver Properties
- Enabling logging
- Report cache - auto refresh settings
- Manually Override Proxy Settings
Data retrieval mode has 3 options:
The Data retrieval mode section only applies to Text/Number columns- Always return text - returns all results as text
- Use majority rule - returns numeric results only if more than half the column inputs are numeric, discarding all non-numeric input
- Use entirety rule - returns numeric results only if 100% of the column inputs are numeric, otherwise returns all results as text
The SQL table names section is available to shorten names. By default the full path is appended to the sheet name. Unchecking this option will cause only the sheet/report name to be returned.
In the Advanced Options menu, scroll down to the bottom of the Optional content list and you will see an Enable custom SQL functions checkbox:
Checking this option will enable the following SQL functions:
| Function | Tableau Example |
|---|---|
| attachments_count() - returns integer | RAWSQL_INT(‘attachments_count(%1)’,[_Rowlocator]) |
| discussions_count() - returns integer | RAWSQL_INT('discussions_count(%1)’,[_Rowlocator]) |
| discussions_text() - returns text | RAWSQL_STR('discussions_text(%1)’,[_Rowlocator]) |
| parent_id() - returns text | RAWSQL_STR('parent_id(%1)’,[_Rowlocator]) |
| child_ids() - returns text (comma separated values) | RAWSQL_STR('child_ids(%1)’,[_Rowlocator]) |
| outline_level() - returns integer | RAWSQL_INT('outline_levle(%1)’,[_Rowlocator]) |
| sheet_id() - returns text | RAWSQL_STR('sheet_id(%1)’,[_Rowlocator]) |
| sheet_name() - returns text | RAWSQL_STR('sheet_name(%1)’,[_Rowlocator]) |
| predecessor_ids() - returns text (comma separated values) | RAWSQL_STR('predecessor_ids(%1)’,[_Rowlocator]) |
| predecessor_labels() - returns text | RAWSQL_STR('predecessor_labels(%1)’,[_Rowlocator]) |
| duration_seconds() - returns double | RAWSQL_REAL('duration_seconds(%1)’,[_Rowlocator]) |
| workspace_id() - returns text | RAWSQL_STR('workspace_id(%1)’,[_Rowlocator]) |
| workspace_name() - returns text | RAWSQL_STR('workspace_name(%1)’,[_Rowlocator]) |
The connector uses the following logic to process and convert Smartsheet data:
Text/Number: The Text/Number column defaults to the majority rule. This means that numeric values are returned if more than half of the column inputs are numeric. All other values are discarded. This rule can be changed in the Advanced Options section during login.
Date, time, and datetime: Date, time, and datetime columns have a storage class of TEXT, which means they are not converted to a date until a query is ran.
Picklists: Picklists probe for types but they use a precise rule (100%). So, one text string in a list of integers would cause the picklist to display text. All of the state picklists resolve to text (5-State Signal, 6-State Hearts, etc..).
Checkbox: Checkbox (or any on/off column) values are stored as a Boolean and uses an integer storage class. Any text data in a checkbox column would be lost. An exception to this rule would be a list to what equates to Boolean data (on/off, true/false, 0/1) i.e. Stars and Flags. These would be stored in a Boolean and the same rules apply.
The Usage section provides resources and instructions for connecting some of the most popular pieces of software with Smartsheet.This walkthrough uses Tableau Desktop version 9, the menus and options for Tableau Desktop version 8 are slightly different.Connect to the Smartsheet Data Source by choosing the To a server > Other Databases (ODBC) option. If you do not see the ODBC option listed here, click on the More Servers… and choose it from the list from additional options:
Under Connect Using pick Driver, and then select Smartsheet Live Data Connector from the Driver drop-down menu:
… and click Connect.
Connect using Email Address and Password
Connect to Smartsheet by entering your Smartsheet credentials into the Smartsheet Login dialog box:
Connect using API Access Token (for SSO users)
If your Smartsheet account requires single sign-on (e.g., via SAML, Google or MSFT Azure AD) and does not allow logins using email and password, you can use a Smartsheet API Access Token instead.First, you need to create a token - it’s easy with just a couple of clicks.
In Smartsheet, navigate to Account > Personal Settings:
In Personal Settings, select API Access and click on Generate new access token:
Give the new token an arbitrary, descriptive name and click OK:
Now go back to your Smartsheet Login window and click on the Use API token for SSO login option:
… and paste your access token:
Upon successful login, confirm connection information and click OK:
Creating Workbooks and Reports
Browse or search Sheets. You can search by Sheet or Report name, or by any portion of their path (name of parent Workspace or Folder).
Note: The search feature is case-sensitiveThen drag and drop each Sheet or Report you need to the upper right pane:
If you are using multiple sheets or reports, and they have columns in common, Tableau will try automatically figure out how to map the selected Sheets and will create a default relationship between them. You can modify the type of relationship (JOIN) at this time.
Once you are done, click Go to Worksheet at the bottom of the application to proceed to the visualization design step:
The next step is to design and configure your report:
Finally, use Tableau Dashboard to create a compelling visual representation of the data:
NoteOdbc Driver Numbers California
: If you have any questions relating to using Tableau, please contact Tableau directly.Re-opening Saved Workbooks
Tableau Desktop does not save login credentials by design (for security reasons), so the next time you launch Tableau Desktop and re-open a previously saved workbook, in Tableau 8, you will see an error message:
Click Yes and re-enter your Smartsheet password. Or, if you are using an API Access token to connect, paste the token into the password field instead:
In Tableau 9 you will see a different message, re-enter your login credentials then click OK. Or, if you are using an API Access token to connect, paste the token into the password field instead.
Installation
The process for installing the Smartsheet Live Data Connector is the same for both server and desktop. You can visit the Download section to find the latest installer packages.
Note: If you have multiple server nodes, the Smartsheet Live Data Connector will need to be installed on each one. We also recommend installing both the 32 and 64 bit versions as they do not conflict with one another.Publishing Workbooks to Tableau Server
A typical Tableau Server workflow starts with creating a workbook / report in Tableau Desktop. Once you create a workbook in Tableau Desktop, you can publish it to a Tableau Server for others to use.
Open the Server menu option and select Publish Workbook…:
If you are not already logged in to your Tableau Server, you will be prompted to do so.
Enter your server:
For information on how to access your Tableau Server, please contact your organization’s IT / Tableau administrator.Then enter your login credentials:
Once logged in, at the next step you will have an opportunity to name the workbook, specify where to save the new workbook on the server, and manage the permissions and access for the published workbook. In terms of access to workbook data, you have two options:
Have users provide their own credentials (which requires they have a Smartsheet login and have permissions in Smartsheet to access the data you included in your workbook).
Access the data you have published without having to provide Smartsheet credentials.
The second option is likely to be the most common one. Tableau Server makes it possible to store your login credentials with your published workbook. To choose this option, click the Authentication… button:
At the next step, you may have to scroll to the right to see the Authentication options:
Finally, in the Authentication dropdown, select Embedded password:
To save and finish, click OK.
Tibco Spotfire is supported as of version 1.1 of the Live Data Connector.Note: to use Tibco Spotfire with the Live Data Connector, you must first:- Install the same version of the Live Data Connector as the version of Spotfire you are using (i.e. if you are using the 64 bit version of Spotfire, you must install the 64 bit version of the Live Data Connector). You can find download links here.
- Uninstall the current Live Data Connector driver.
- Reinstall the correct version of the driver.
- Configure a Data Source.
If you have the wrong version of the driver installed, you will need to:
Spotfire installation instructions can be found here. Additional resources for using Spotfire can be found here.
Launch Spotfire and then navigate to Add Data > Add Data Connection > Other:
Click on Add then select Database… at the bottom of the list:
Choose Odbc Data Provider then click Configure…:
Select the DSN you configured earlier. Fill in your Smartsheet credentials to log in. Or, if you are using an API Access token to connect, leave the email field empty and paste the token into the password field instead:
Select a Table and all columns needed, set a Data Source Name, then click OK:
Select the Data table you just set up and click OK to import the data (the Name will be the same as the Data source name you entered in the last step):
Finally, use Spotfire to create a nice visualization of the data:
For any questions related to using Spotfire, you can visit their support page here.Connect using Email Address and Password
To interact with Spotfire Server, you must install Tibco Spotfire Analyst on your client. Once installed, you will be able to save to and access data on your Tibco Spotfire Server.Note: to use Tibco Server with the Live Data Connector, you must first:- Install the same bit version of the Live Data Connector as the version of Spotfire you are using (i.e. if you are using the 64 bit version of Spotfire, you must install the 64 bit version of the Live Data Connector). You can find download links here.
- Uninstall the current Live Data Connector driver.
- Reinstall the correct version of the driver.
- Configure a Data Source.
If you have the wrong version of the driver installed, you will need to:
When you open Tibco Spotfire Analyst, you’ll be prompted to log in and select a Server. If you haven’t configured it already, click on Manage Servers…:
On the Manage Servers window, click on Add:
On the next screen, enter the server address for your Tibco Spotfire server:
Note: If you do not know your Tibco Spotfire server address, please consult with your Spotfire server administrator for assistance.Opening from Spotfire Library
If you’re interested in creating data visualizations from scratch, you can follow the steps outlined in the Tibco Spotfire section.Click on the Add Data tab, then click on Open from Library:
Select a file from the list, then click on Open:
Saving to Spotfire Library
You can save any changes back to the Spotfire library by clicking on File > Save As > Library Item…:
To access your content on the Spotfire Web Player, you’ll first need to log in:
After logging in, you can view current files uploaded to your Spotfire server. You can also upload files from your computer by clicking on Open analysis:
Then browse for the file on your computer:
Open up Qlik then click on Create a new app on the dialog that appears
Enter a name for your app and click Create:
Click on Open app:
Click on Add data:
Then click on Connect my data, then click ODBC:
Create a new connection by selecting the DSN you configured earlier. Fill in your Smartsheet credentials to log in. Or, if you are using an API Access token to connect, paste the token into the password field instead:
Select the data you want, then click Load and finish:
After the data is loaded, click on Close:
Then click on the new data sheet you just created:
Finally, use the data to create your dashboard in Qlik Sense:
Open up ClickView and navigate to File > New:
After the Getting Started Wizard: dialog starts, click the X in the upper right-hand corner:
After the dialog is closed, click on File again, then click on Edit Script…:
On the next screen, select ODBC in the Database dropdown, then click on Connect… (you can click on Select… as well, they open the same dialog box):
On the next dialog, select the Data Source you set up (If it doesn’t show, click the Show User DSNs checkbox). Fill in your Smartsheet credentials to log in. Or, if you are using an API Access token to connect, paste the token into the password field instead:
Click on Select:
Select the Tables and Columns you would like then click OK:
After you’re finishing adding tables and your script is ready, click OK:
Your data is now loaded, but before you can begin making charts and graphs you’ll need to save your project:
Then you’ll need to start the Chart Wizard:
Click on File > Reload:
This section covers the various ways to use the Smartsheet Live Data Connector with Excel.
Note: Excel does not provide a seamless way to save credentials within a workbook and can be unreliable. If you are looking to share data and analytics with others, Excel is not recommended.If you choose to use Excel and need to share your workbooks, it is recommended that you use Microsoft Query. Because of the way data is created and stored, the Data Connection Wizard is not supported for saving credentials.Excel - Data Connection Wizard
To use the Data Connection Wizard, you must configure a Data Source first.Open the Data Connection Wizard by navigating to Data > Get External Data > From Other Sources:
Select ODBC DSN and click Next:
Select the name of the DSN you created earlier, and click Next:
When prompted, fill in your Smartsheet login credentials. If your Smartsheet account requires a single sign-on and you are unable to use email and password for login, you can use the Use API Token for SSO login option instead and paste your API token into the password field:
Once connected to Smartsheet, select your sheet or report and click Finish:
Finally, specify the destination for the imported data:
Excel - Microsoft Query
IMPORTANT: Make sure that the type of Smartsheet ODBC Driver (32-bit or 64-bit) installed on your computer matches the type of Microsoft Office installed (32-bit or 64-bit). Click here to download the installer packages.It is possible to create new data sources right in Microsoft Query. We recommend, however, that you Configure a Data Source prior to using Microsoft Query.Once a DSN has been configured, navigate to Microsoft Query by navigating to Data > From Other Sources > From Microsoft Query:
Select the Data Source you created earlier, then click OK:
When prompted, fill in your Smartsheet login credentials. If your Smartsheet account requires a single sign-on and you are unable to use email and password for login, you can use the Use API Token for SSO login option instead and paste your API token into the password field:
Once connected, select your desired sheets or reports:
Specify the destination for your data:
Select how you want to view the data:
Saving Credentials
Note: This method is provided as a potential solution for saving credentials in an Excel workbook. While we have provided this method, saving credentials and sharing workbooks in Excel is not recommended or supported by Smartsheet.To save credentials, click on the DATA tab, then click on Connections:
Choose the Connection for your query, then click on Properties:
Click on the Definition tab, then check the Save password option.
After you’ve checked the option to save your password, you’ll want to copy the following string to the end of the Connection String (Replacing API_TOKEN with the API access token you generated):
PWD=API_TOKEN
Finally, click on OK:
Excel - Power Query
Power Query does not provide a user-friendly interface to select your sheets and build your query. So, first you need to use Microsoft Query to build your query.Follow the earlier instructions for Microsoft Query, but instead of returning data to Excel, select View data or edit data query in Microsoft Query:
Once in query editor, click on SQL and copy the SQL statement in the window that opens:
Now, launch Power Query from the main Excel menu and select From other sources > From ODBC:
At the next step, paste the string below into Connection string, and paste the query you copied / saved from an earlier step into SQL Statement:
Driver={Smartsheet Live Data Connector}
Finally, enter your Smartsheet credentials when prompted:
The following questions are answered in this section:
- I am getting an error message when attempting to open a saved workbook - “Invalid username or password for data source”
Why am I seeing this error - “Unexpected error”, when expanded says that “ODBC is not enabled for your account”?
This is because ODBC access has not been enabled for your Smartsheet account. Please see the Requirements section to ensure your account is eligible to use the Live Data Connector.
If you meet all the requirements and still receive this error please visit the Support section for information on reaching our support team.Back to FAQ
My date columns contain mixed milestone names (text) and dates, but on import into Tableau all text data is lost. How can I see this information?
Date and Time columns have a storage class of text in the Live Data Connector. This means that they are plain text strings until imported into Tableau or other ODBC applications. If you wish to see the non-date/time content of these columns, you must modify the Tableau column type prior to import. You can do this by a right clicking on the calendar icon in the Tableau table selection screen and selecting the String type. All cell contents will now be displayed as raw, unformatted text.
Back to FAQ
Does my Smartsheet data need to be formatted in a certain way to be compatible with Tableau?
Spreadsheet data may require some formatting to work with Tableau. Tableau has created a guide for Excel users, and much of it apply to Smartsheet as well because of similarities in how data may be structured in Excel and Smartsheet. See Preparing your Excel data for Tableau knowledge base article.
Back to FAQ
When I use Tableau Desktop, why do I have to re-enter my Smartsheet credentials every time I re-open Tableau?
Tableau Desktop does not save user login credentials by design, as a security precaution. It is a feature of Tableau Desktop, not a feature of the Smartsheet Live Data Connector. To re-open previously saved Tableau Desktop workbooks, see this section.
Back to FAQ
Why am I getting an “invalid username or password” error when trying to re-open a previously saved Tableau Desktop workbook?
Please see this FAQ item.
Back to FAQ
What is the max number of tables that the Smartsheet Live Data Connector will support in a join?
The Smartsheet Live Data Connector will support up to 64 tables (sheets) in a single join.
Back to FAQ
What SQL dialect does the Smartsheet Live Data Connector support?
The Smartsheet Live Data Connector uses an implementation of SQLite to locally store and query Smartsheet sheets and reports. As such, the Live Data Connector can interpret native SQL as understood by SQLite. The SQL syntax used by SQLite is well documented on the SQLite website: https://www.sqlite.org/lang.html. In addition to the SQLite core functions https://www.sqlite.org/lang_corefunc.html, the Live Data Connector also supports a variety of functions which expose sheet, column and cell metadata through native SQL. Those functions are described above.
Back to FAQ
Why doesn’t Tableau’s Fiscal Calendar Start functionality work correctly?
Any time I select a different fiscal year start month, it loses the year and quarter data for any month after the start month, through the end of the calendar year. For example, if I choose August as the start of the fiscal year, the months of August through December show Null values for year and quarter. January through July, however, show up correctly.
When communicating with the Smartsheet Live Data Connector, Tableau expects (as an example) that if you ask for the 15th month of 2014 that the Connector will return a date of the 3rd month of 2015. The Connector’s date function, however, sees the 15th month as a bad value and returns NULL instead. To work around this limitation, create a calculated field in Tableau:
- Right click anywhere in the Data pane and select Create Calculated Field
- Let’s call it FY Order Date
- In the Functions column select
RAWSQL_DATE - Then in the Formula window type
RAWSQL_DATE('date(%1, '+6 month'), [Order Date]), whereOrder Dateshould be replaced with the name of the date column you will use from your table.
In this example the Fiscal Year starts on July 1st - hence ’+6 month’. If your Fiscal Year starts on a different month, you would need to adjust accordingly. For instance, if it starts in March, your calculation should say ‘+2 month’.
The result should look like this:
Back to FAQ
I tried publishing a Smartsheet-based Workbook and got a message that the functions TODAY() and NOW() will work differently because the server and Smartsheet are in the different timezones.
Tableau appears to be returning incorrect information when executing the NOW() function, and there appear to be a number of online forum posts confirming this.
Luckily, as with DATEADD and DATEDIFF we can modify these calculations to return UTC or local time. The NOW() UTC function is RAWSQL_DATETIME('datetime('now')'). And the NOW() local time function is RAWSQL_DATETIME('datetime('now', 'localtime')').
The way most other Tableau users address this is by picking a common time zone and modifying the calculations to always use that time zone. Our recommendation is to replace NOW() with your own function that returns UTC if you need to be consistent across time zones (all other Smartsheet dates will also get returned in UTC).
Back to FAQ
My Smartsheet data does not appear to refresh on Tableau Server.
There is a known issue with Tableau Server prior to version 8.3.3 where the manual refresh (using F5) does not refresh any data from Smartsheet. The issue has been addressed in Tableau Server 8.3.3.
Back to FAQ
I am seeing “Tableau identified limitations…” warning message.
The Smartsheet Live Data Connector comes with a Tableau configuration file designed to optimize the Tableau integration experience. The file gets installed into C:My DocumentsMy Tableau RepositoryDatasources directory and is called “smartsheet.tdc”.
If for some reason Tableau is not able to find this file, you will see the following warning:
This may happen for one of two reasons:
The Smartsheet Live Data Connector was installed before Tableau was installed, so the .tdc file was never copied to the Tableau directory because it didn’t exist at the time.
Both 32-bit and 64-bit Connectors were installed and then one of them was uninstalled, removing the .tdc file.
In both instances, the solution is to uninstall your Smartsheet Live Data Connector(s) and do a clean install of the Connector, insuring that the .tdc file is now installed into the Tableau directory.
Back to FAQ
How do I get Excel to Auto-Refresh Smartsheet Data?
You can schedule a data refresh on specific intervals in Excel.To start, navigate to Data > Refresh All > Connection Properties…
After that, a dialog will open with several settings for refreshing. After you’ve finished setting the options to your liking, click OK:
Back to FAQ
I am getting an error message trying to configure or delete a DSN - “The setup routines for the … could not be found. Please reinstall the driver.”
If you removed the Live Data Connector from your computer, or have upgraded it from version 1.0.20.3 (or earlier when it used to be called Smartsheet ODBC Driver), but still have Data Source Names (DSNs) set up with the old driver, you will not be able to configure or delete them.
When you attempt to configure or delete a DSN set up for an ODBC driver that has been removed from your computer, you will receive the following error message:
Please follow the instructions described in this online post to remove any references to the old driver.
First, click Start, type regedit and launch the Windows registry editor:
If your DSN is a user DSN (most likely) configured for a 64-bit driver, navigate to HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INI and delete the data source key under ODBC.INI and the entry under ODBC.INIODBC Data Sources:
Back to FAQ
I am getting an error message when attempting to open a saved workbook - “Invalid username or password for data source”
This error is likely caused by character limitations in Tableau. Because of the way data is passed in Tableau when a workbook is re-opened, the following characters cannot be used in your password:
{} ; =
If you have any of these characters in your password, you will need to change it and remove them.
Back to FAQ
The odbc package provides a DBI-compliant interface to Open Database Connectivity (ODBC) drivers. It allows for an efficient, easy way to setup connection to any database using an ODBC driver, including SQL Server, Oracle, MySQL, PostgreSQL, SQLite and others. The implementation builds on the nanodbc C++ library.
ODBC drivers can typically be downloaded from your database vendor, or they can be downloaded from RStudio when used with RStudio professional products. The odbc package works with the DBI
Using
All of the following examples assume you have already created a connection called con. To find out how to connect to your specific database type, please visit the Databases page.
Database information
The odbc package gives you tools to explore objects and columns in the database.
You can also see other data sources and drivers on the system.
Reading and writing tables
The DBI package has functions for reading and writing tables. dbWriteTable() will write an R data frame to a SQL table. dbReadTable() will read a SQL table into an R data frame.
You can specify tables outside the database with the Id() command.
Queries and statements
For interactive queries, use dbGetQuery() to submit a query and fetch the results. To fetch the results separately, use dbSendQuery() and dbFetch(). The n= argument in dbFetch() can be used to fetch partial results.
You can execute arbitrary SQL statements with dbExecute(). Note: many database API’s distinguish between direct and prepared statements. If you want to force a direct statement (for example, if you want to create a local temp table in Microsoft SQL Server), then pass immdediate=TRUE.
Odbc Driver Numbers Lookup
odbc Performance Benchmarks
The odbc package is often much faster than the existing RODBC and DBI compatible RODBCDBI packages. The tests below were carried out on PostgreSQL and Microsoft SQL Server using the nycflights13::flights dataset (336,776 rows, 19 columns).
PostgreSQL Results
| Package | Function | User | System | Elapsed |
|---|---|---|---|---|
| odbc | Reading | 5.119 | 0.290 | 6.771 |
| RODBCDBI | Reading | 19.203 | 1.356 | 21.724 |
| odbc | Writing | 7.802 | 3.703 | 26.016 |
| RODBCDBI | Writing | 6.693 | 3.786 | 48.423 |
Microsoft SQL Server Results
| Package | Function | User | System | Elapsed |
|---|---|---|---|---|
| odbc | Reading | 2.187 | 0.108 | 2.298 |
| RSQLServer | Reading | 5.101 | 1.289 | 3.584 |
| odbc | Writing | 12.336 | 0.412 | 21.802 |
| RSQLServer | Writing | 645.219 | 12.287 | 820.806 |