Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 35 ยท Connecting to Data

Connecting Tableau to Data: Files, Servers, Live vs Extract

Learn how to connect Tableau to Excel, text files and databases, install drivers, choose live or extract, use the Data Source page, and replace a data source safely.

Every workbook starts with a connection. Getting this step right (the right source, the right grain, live or extract) makes the rest of your work simpler. This lesson covers the Connect pane, the Data Source page, the choice between live and extract, and how to replace a data source without losing your charts.

The Connect pane

On the start page or at the top left of the Data Source page, Connect lists two groups.

  • To a File: Microsoft Excel, Text file (CSV, TSV, TXT), JSON, PDF, spatial files (shapefile, GeoJSON, KML), statistical files (SAS, SPSS, R) and saved Tableau files (.hyper, .tds, .twbx).
  • To a Server: relational databases (MySQL, PostgreSQL, SQL Server, Oracle, Amazon Redshift, Snowflake, Google BigQuery), big data platforms, cloud apps (Google Sheets, Salesforce) and generic Other Databases (ODBC / JDBC).

Click More... next to either list for the full catalog. A Tableau Exchange website offers extra connectors built by partners.

Drivers

For server connections Tableau uses a driver. When a driver is missing, a dialog tells you so and links to the Driver Download page: choose the database, your operating system, install, and restart Tableau. If the driver is present but the connection fails, check these in order:

  1. The host name and port (MySQL 3306, PostgreSQL 5432, SQL Server 1433).
  2. The database user and password, and whether that user may read the schema.
  3. Network access: VPN, firewall and allow-lists.
  4. Whether the database needs SSL, tick Require SSL in the connection dialog.

Never share a workbook that has an embedded database password with people who should not have it. Prefer Prompt user for credentials, or publish the data source with managed credentials when you reach the sharing chapter.

Connecting to Excel and text files

  1. Choose Microsoft Excel and pick the workbook.
  2. The Data Source page opens. The left pane lists the sheets. Drag one to the canvas.
  3. Look at the grid at the bottom: it shows the first rows and the data type icon above each column.

Excel files often need cleaning. Tableau's Data Interpreter (a checkbox in the left pane) detects titles, notes and blank rows above the header and removes them. For a sheet with merged cells or footers, the interpreter helps, but tidy source files are always better.

A few rules for good source files:

  • One header row, one value per cell, no merged cells.
  • No totals mixed in with data rows. Tableau computes totals itself.
  • Use one row per fact (an order line, a visit, a day), not one column per month.
  • If your data has monthly columns, pivot them (Lesson Data blending and cleaning).

For a text file, Tableau guesses the delimiter. Use Text File Properties (the drop-down arrow) to set the field separator, text qualifier and character set. Wrong delimiters give you one wide column.

The Data Source page

This page has four areas:

  • Left pane: the connection, then the tables or sheets available.
  • Canvas (top right): where you drop tables to join or relate them. Lesson Joins, relationships and unions covers this.
  • Grid (bottom right): a preview of the data, with a column header menu to rename, hide, change type, split or create calculations.
  • Connection toggle (top right): Live or Extract, and a Filters box for data source filters.

Click Sheet 1 at the bottom to start working, or check the data first by switching Data > View Data from the Data pane.

Live or extract?

Live Extract
Data freshness Reads the source on every query A snapshot from the last refresh
Speed Depends on the database Usually fast (columnar .hyper file)
Load on the source Every interaction sends queries Only during refresh
Offline use No Yes
Size None Stored with the workbook or on the server

Choose Live when the database is fast and you need real-time numbers. Choose Extract when the source is slow, when you want offline work, or when the data changes only once a day. You can schedule refreshes after publishing, and extracts can be incremental (append only new rows) when a key column such as Order Date is available.

When you create an extract, you can add filters to limit rows, aggregate to visible dimensions to shrink it, and roll up dates to a coarser level. All of these make the extract smaller but remove detail that you can no longer query. Think about what questions will be asked before you remove columns.

Try it: live versus extract (stand-in)

This Python stand-in imitates the difference. The "live" reader looks at the table whenever you ask. The "extract" is a copy taken at a point in time, so it does not see later changes until it is refreshed.

Python Stand-in: a snapshot is not a mirror

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Replacing a data source

Suppose your sales file moves from Excel to a data warehouse table, or you want to swap a test database for production. Tableau lets you point existing worksheets at a new source:

  1. Connect to the new data source (it appears next to the old one in the Data pane header).
  2. Select the old data source in the Data pane.
  3. Choose Data > Replace Data Source..., pick the new source and click OK.

Tableau matches fields by name. A field that exists in the old source but has a different name in the new one turns red with an exclamation mark, and the sheets using it break. Fix it by right-clicking the red field and choosing Replace References... (pick the field in the new source), or rename the column in the new source to match.

Python Stand-in: find the fields that will break

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Compare field lists before you replace. If the new source uses a different grain (for example, one row per customer instead of one per order), the numbers will change even when every field matches.

Data source filters and extra settings

Click Add in the Filters area (top right of the Data Source page) to add a data source filter. It applies to every worksheet that uses the source and is evaluated very early in the filter order (see Filters and the order of operations). Use it to remove test records or restrict a user to their country.

You can also rename the data source (click its name), hide unused fields and set default properties (number format, aggregation, color) so every sheet starts consistent.

Common mistakes

  • Choosing live for a slow database and blaming Tableau for slow dashboards. Try an extract first.
  • Packaging a workbook with a local Excel path and forgetting .twbx.
  • Replacing a data source with different field names and not checking for red pills.
  • Embedding passwords in a shared workbook.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a Tableau extract (.hyper) store?

    An extract is a stored copy of the data, so it is fast and works offline, but it only changes when refreshed.
  2. 2. When you use Replace Data Source, how does Tableau match fields?

    Fields are matched by name, and missing names turn red so that you can replace the references.
  3. 3. A server connection fails with a missing driver message. What is the first action?

    Tableau needs the database vendor's driver for many connectors; install it and restart.
  4. 4. Which source file layout works best for Tableau?

    Tidy, one-row-per-fact data aggregates cleanly. Wide layouts need pivoting and totals rows cause double counting.

Summary

  • Connect to a file or a server; install the vendor driver when Tableau asks for it.
  • The Data Source page has the left pane, canvas, grid and the Live/Extract toggle.
  • Choose live for fast databases and real-time needs, extract for speed, offline use and low load.
  • Replace Data Source matches fields by name; fix red fields with Replace References.
  • Tidy source data (one header, one row per fact) saves you hours.

Next: Joins, relationships and unions