Odbc Driver Numbers

ODBC Driver now includes the version number in the driver name when OCIATTRDRIVERNAME is set, such as ODBCCLNT: 12.2.0.1.0. (Bug 21795969) (Bug 21795969) Oracle ODBC driver truncated an output parameter of SP when run from 12.1.0.2. Getting latest version of SQL Server ODBC Drivers. 32-bit applications use 32-bit ODBC drivers and 64-bit applications use 64-bit ODBC drivers. Our software uses the highest driver installed, For example, If both SQL server 2005 and 2008 are installed it will use SQL Server 2008 Driver. To Check Version of SQL Server ODBC Driver do the following.

Before creating python connection to SQL database from windows computer we need to ensure we have correct driver installed. The quickest ways to do this is by opening ODBC Data Source Administrator and selecting Drivers tab as per screenshot below.

In my case I will be connecting to SQL server 2017 therefore I will be using ODBC Driver 17 for SQL Server.

If you are connecting to other databases such as Oracle, MySQL, etc you need to make sure you have correct driver and version (32bit or 64bit) installed otherwise it will not work.

I have seen many connection issues is due to incorrect driver version. For example if you have python 32-bit version installed, you need to make sure you have 32-bit ODBC driver installed otherwise your connection will not work.

If you’re not sure if your python executing 32-bit or 64-bit then you can run the code below and the output will how if it is 32bit or 64 bit version.

Now you know which version of ODBC driver (32-bit or 64-bit) you need to install it, the next step is to install pyodbc package.

pyodbc package will be communicating directly with the ODBC driver.

pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0specification but is packed with even more Pythonic convenience.

The easiest way to install is to use pip:

If you’re using IDE PyCharm you can install it from:

File –> Setting –> Project: Name of Your Project –> Project Interpreter

Click on icon + and install the package, if everything is ok you will receive confirmation Package ‘pyodbc’ installed successfully.

I’m will break down connection string in more meaningful way.

Trusted_Connection=no; – Specify if you want connection string to use your windows credentials, if my case no therefore SQL credentials will be used.

DRIVER={ODBC Driver 17 for SQL Server}; – Specify name of the driver from ODBC Data Source Administrator

SERVER=.SQLEXPRESS01; – Dot means my local computer and SQLEXPREESS01 is a instants of your SQL server.

PORT=1433; – port number connect to SQL server

DATABASE=test; – name of the database

UID=USER1; – username of SQL server

PWD=Password; – password of the SQL server user

Last updated: 02/05/2019

The 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

After you’ve completed setup and configured the Live Data Connector with the software of your choice, we recommend reading through the Configuration section.

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 later

Once the destination is selected, click OK to finish:

If you have already set up a Smartsheet ODBC Data Source Name you may skip to the next section.Some applications, like Tableau, allow using the Connector directly. Others require to first set up a Data Source in Windows.

If you need to set up a Data Source:

Navigate to Start > Programs > Smartsheet Live Data Connector
Run ODBC Data Source Admin

Note: For Windows 10 users, please see the next section for instructions on locating the ODBC Data Source Admin menu.

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

The Manually Override Proxy Settings option can be used in the event that system or user proxy settings can’t be determined.

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])

Smartsheet allows for any data to be stored in a cell despite the column type.

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:

Make sure you copy the token before you close the window because you won’t be able to view the token again (for security reasons).

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-sensitive

Then 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:

Note: If you have any questions relating to using Tableau, please contact Tableau directly.

If you need to publish your work to Tableau Server, you can proceed to this section.

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.

This section covers publishing workbooks to a server using Tableau. If you do not need to publish workbooks or do not have a Tableau server, you may skip this section.

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.

If you have the wrong version of the driver installed, you will need to:

Uninstall the current Live Data Connector driver.
Reinstall the correct version of the driver.

Configure a Data Source.

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.

If you have the wrong version of the driver installed, you will need to:

Uninstall the current Live Data Connector driver.
Reinstall the correct version of the driver.

Configure a Data Source.

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.

Note: In order to have server visualizations load seamlessly, it is recommended that you check the option to “Allow Saving Credentials” when setting up the data source for the first time.

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…:

In order to access content on Spotfire Web Player you’ll need your web player URL. Please reach out to your Spotfire Server administrator if you need help locating the URL.

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:

Qlik Sense installation instructions can be found here.To use Qlik Sense with the Live Data Connector, you must configure a Data Source first.

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:

To use QlikView with the Live Data Connector, you must configure a Data Source first.

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):

Odbc Driver Numbers List

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:

Note: If you add new data to your script after the initial setup, you’ll need to be sure you reload the script.

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:

Once connected, select your desired sheets or reports:

The next two steps (not pictured here) let you filter and sort your data. It is recommended that you do not use filtering here but instead use Microsoft Excel filtering. Microsoft Query filtering frequently generates invalid SQL code.

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:

Note: In order to save your credentials, you’ll need to generate an API access token. Using your Smartsheet password is not recommended as it may be visible to others.

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:

When a workbook is shared with a user for the first time, it may ask them to enter their Smartsheet username & password. You can cancel the login process when the dialog appears, then refresh the connection manually.

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”?

Odbc Driver Numbers Search

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]), where Order Date should 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.

Odbc Driver Numbers Lookup

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:

Download powtoon full version with crack.

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.

Odbc Drivers

Odbc Driver List

Back to FAQ