To follow along with the recipes in this chapter, you will require Tableau Prep Builder version 2020.2.3 or later. If you'd like to follow along and connect or write to a database, you must have the appropriate database set up and have an account with the appropriate access privileges.

The recipes in this chapter use sample data files, which you can download from the book's GitHub repository: https://github.com/PacktPublishing/Tableau-Prep-Cookbook.

In this recipe, we'll connect to a Comma-Separated Values (CSV) file containing sales transactions and create a second connection to multiple Excel files. These connection types are very similar and so we'll cover them in one recipe. However, there are key features to both, which we'll highlight.

Getting ready

To follow along with the recipe, download the Sample Files 2.1 folder from this book's GitHub repository.

How to do it…

To get started, ensure you have the sample CSV and/or Excel file(s) ready on your computer and open up Tableau Prep Builder:

1. From the Tableau Prep Builder home screen, click the Connect to Data button and subsequently select Text file from the Connect pane:
   

   Figure 2.1 – Connect to a text file

2. Tableau Prep will bring up the file selection window next. From here, navigate to our sample file, December 2016 Sales.csv, and open it:
   

   Figure 2.2 – Select December 2016 Sales.csv

   Once selected, Tableau Prep will automatically create a new flow with the data connection in it:

   

   Figure 2.3 – A new flow is created when selecting any text file

   Since the data connection is automatically selected, all options onscreen now relate to that particular connection. On the left-hand side, you can see the Tables pane. Note that there is only one table, equal to the filename. Since text files do not contain tables, this is by design. Tableau Prep Builder will always provide a generic user interface for data connections whenever possible. Once you're comfortable with one connection type, others should be easier to master:

   

   Figure 2.4 – Text files such as CSV always have a single table

   In the bottom pane, you can find a summary of all data fields identified in your text file, along with the automatically determined data type. In Chapter 3, Cleaning Transformations, we'll dive into the cleaning options you can perform here:

   

   Figure 2.5 – Field summary

3. In the same bottom pane, you can configure the data connection settings. For text files, you'll always want to verify the Text Options section. Tableau Prep will automatically set these values as best as possible, but I recommend you verify them before you continue. The word header refers to the first row in your dataset. If you do not have headers in your dataset, you can select Generate field names automatically, which will create headers named F1, F2, F3, and so on. You can rename those fields later on. Field Separator tells Tableau how columns are defined in a CSV file, which is usually a comma or pipe symbol. Text Qualifier tells Tableau Prep which characters indicate the start and end of a value or string.

   Finally, Character Set and Locale are typically identified appropriately but you can alter them here as needed:

   

   Figure 2.6 – Connection settings

4. Now that we have connected a CSV file, let's create a second connection to an Excel file, in the same flow. To start, click the + icon in the Connections pane and select Microsoft Excel:
   

   Figure 2.7 – Adding a second data connection

5. Identical to the selection of a text file, browse to and select our sample file named December 2016 Sales.xlsx.

   Once we've selected the file, Tableau Prep Builder does not automatically show another data connection in the flow, as it did for our CSV file. This is the default behavior for any data connection that has multiple tables. In the case of Microsoft Excel, each Excel sheet is considered a table:

   

   Figure 2.8 – Connections with tables require table selection before you can continue

6. In order to continue, we must drag a table, or sheet, onto the flow canvas to finalize the data connection. Proceed by dragging in the Sales_Data table. Once added, you'll notice the color of this connection is different from the text file connection we made earlier. Tableau Prep Builder randomly assigns a color to the various data flows for easy recognition.

   The colors do not denote any kind of functionality:

   

   Figure 2.9 – A flow with multiple data connections

7. Once connected, you'll notice the options specific to text file connections no longer appear. However, the layout remains the same. A function common to both text and Excel files is the ability to ingest multiple files simultaneously. Select the Multiple Files tab for this function:
   

   Figure 2.10 – Multiple Files tab

8. From here, select Wildcard union to reveal the options:
   

   Figure 2.11 – Multiple Files options

9. Here, we can opt to include files in subfolders from the selected folder, which defaults to the folder where our Excel file is located. Select the Include subfolders option to enable this. Let's assume we want to include all sheets named Sales_Data, in all files ending in 2016 Sales.xlsx. To do so, we can use the asterisk symbol as a wildcard and set the file Matching Pattern property to *2016 Sales.xlx and the sheet Matching Pattern property to Sales_Data:

Figure 2.12 – Using wildcards to ingest multiple files at the same time

As a result, this step will now ingest all files in our subfolder named Archive and combine the data:

Figure 2.13 – All sample files here will be ingested at the same time using a single connection

By completing these steps, you have learned how to connect Tableau Prep to text and Excel files.

How it works…

Tableau Prep text files and Microsoft Excel connections automatically detect most settings very well, so, in most cases, a couple of clicks will get you up and running. The most powerful feature is undoubtedly the ability to ingest multiple files at the same time. You can ingest hundreds of files at the same time using this method, using a single data connection.

In this recipe, we'll connect to a PDF file containing text and a table with data. Tableau Prep has an exciting feature that can automatically detect the presence of tables in PDF files and extract the data for you.

Getting ready

To follow along with the recipe, download the Sample Files 2.2 folder from the book's GitHub repository.

How to do it…

To get started, ensure you have the sample PDF file ready on your computer, and open Tableau Prep Builder:

1. Tableau Prep Builder will not show us the entire PDF document, so it's best to open it in a PDF viewer and review what data we want to extract from our PDF. In our example document here, we have a single table and so we expect a table in Tableau Prep with the headers Department and Amount:
   

   Figure 2.14 – Sample PDF file with a table embedded in it

2. In Tableau Prep Builder, select the Connect to Data button, followed by PDF file to open the file browse dialog and select our sample PDF file, Sales Summary.pdf:
   

   Figure 2.15 – Select PDF file from the Connect pane

3. Once connected, Tableau Prep Builder will automatically detect the tables within the PDF file. In our sample, we can see the Department and Amount fields coming through as expected:
   

   Figure 2.16 – PDF tables are automatically extracted

4. Each table is listed separately in the Tables part of the Connections pane to the left. This allows you to digest PDF files with multiple tables within them just as easily. The name of the table is automatically generated and refers to the page number in the PDF file and its position on the page:

Figure 2.17 – Tableau Prep can detect multiple tables in a single PDF file

In this recipe, you have learned how to connect to PDF files and extract data for processing in Tableau Prep.

How it works…

Tableau Prep converts each table in a PDF document into a data table when ingesting the file into a new flow. As such, Tableau Prep removes the complexity of parsing PDF documents and allows you to treat this like any other data connection.

In this recipe, we'll connect to a statistical file. Tableau Prep offers fantastic integration with popular statistical files from SAS (.sas7bdat), SPSS (.sav), and R (.rdata, .rda).

I advocate the use of open file formats such as CSV or commonly used standards such as Excel. However, if you are unable to obtain your data in such a format from your data science partner, this connector may offer a solution.

Getting ready

In this recipe, we'll connect to an R file using the statistical file connector. In order to follow along, download the Sample Files 2.3 folder from the book's GitHub repository.

How to do it…

To get started, ensure you have the sample RData file available on your computer. From the Tableau Prep home screen follow these steps:

1. Click the Connect to Data button and select Statistical file.
2. From the browse file window, locate and open our statistical file named December 2016 Sales.Rdata.

   And with just these few steps, Tableau Prep Builder has added the statistical file source to a new flow:

Figure 2.18 – Flow with a statistical file connection

Most options in the bottom pane are identical to those when processing Excel files. However, there is a small but important feature absent. You cannot alter the data type of the fields in the statistical file connection step. In order to do this, you have to use a cleaning step, which we'll discuss in Chapter 3, Cleaning Transformations:

Figure 2.19 – Data types cannot be altered directly in the statistical file connection step

In this recipe, you have learned how to add Tableau Prep to a data science workflow by connecting to data produced by popular statistics applications.

How it works…

Tableau Prep unpacks statistical files when you connect to them and, from that moment on, allows you to leverage them like any other connection.

There's more…

There are some limitations when it comes to connecting to statistical files. If you run into any connection issues, I recommend you refer to the following section of the Tableau documentation online: https://help.tableau.com/current/pro/desktop/en-us/examples_statfile.htm.

In this recipe, we'll connect to a Microsoft SQL Server database. The many connectors provided out of the box by Tableau Prep allow you to connect to databases almost as easily as to file connections, allowing you to quickly start an advanced flow.

Getting ready

In order to follow along with this recipe, download the Sample Files 2.4 folder from the book's GitHub repository. This folder contains the Wide World Importers sample database backup (.BAK) file, which you can restore to your SQL Server instance.

Note that the provided BAK file is suitable for SQL Server 2016 SP1 or later. If you're running an earlier version or need instructions on installation, please consult Microsoft's support page at https://docs.microsoft.com/en-us/sql/samples/wide-world-importers-oltp-install-configure.

How to do it…

To get started, ensure you have Tableau Prep Builder open, then follow these steps:

1. From the home screen, click the Connect to Data button to bring up the Connect pane. From here, select the search field and type in SQL to instantly filter the available connections.
2. From the filtered selection of connections, select Microsoft SQL Server. This will bring up the Connection dialog.
3. In this dialog, enter your connection details. Depending on your server, these details will vary. If you're not sure about these details, please contact your database administrator. Click Sign In to continue.

   Important note

   If you are using a macOS computer, the Sign In button may remain disabled even though you have populated the appropriate connection details. This could be the result of a driver missing on your device. To find the drivers needed, go to the Tableau Driver Download web page at https://www.tableau.com/en-us/support/drivers?edition=pro&lang=en-us&platform=mac.

4. Once the connection has been established, Tableau Prep will show a dropdown in the Connections pane of all databases on the server you've selected. Select your database to reveal the available tables:
   

   Figure 2.20 – Database selection

5. From the list of available tables, we need to select the table we'd like to ingest into our flow and drag it onto the canvas. Let's drag in the table named Order:

Figure 2.21 – Drag a table onto the canvas

With the table on the canvas, we can now continue building out this flow as with any other data connection type.

How it works…

Tableau Prep has a number of built-in database connections that remove the complexity of connecting by configuring connections such as ODBC manually. The number of supported data connection types is continually expanding, too. If you do not see your database listed, you can always opt to use an ODBC connection instead. See the recipe titled Connecting to JDBC or ODBC data sources in this chapter for more information.

There's more…

Database tables can relate to each other and be joined to each other to create insightful datasets. We'll cover joins in detail in Chapter 5, Combining Data. For now, I want to highlight a great feature in Tableau Prep that indicates the table primary key and foreign keys in the bottom pane. You can find this information in the Linked Keys section as shown in the following screenshot:

Figure 2.22 – Linked Keys for database connections

There are three types of keys: primary, foreign, and keys representing both primary and foreign. Tableau Prep shows an icon indicating the key type accordingly:

Figure 2.23 – Database key types

By completing the steps in this recipe, you have connected Tableau Prep to an on-premises database.

In this recipe, we'll connect to a local Amazon AWS Athena database. Just like on-premises data connections, Tableau has made it as easy as possible to connect securely to cloud data sources. You'll find many connections for popular cloud providers including Microsoft, Google, and Amazon. Each data connection dialog has been customized to the technology you're attempting to connect to. This means you won't see irrelevant fields for the selected connection type, reducing the complexity of cloud connections.

Getting ready

In order to follow along with this recipe, you must have data stored and have access to that data in Amazon AWS Athena.

Tip

Getting set up on AWS Athena is beyond the scope of this book. However, if you wish to explore this option, the simplest way to get started is to create an account at https://aws.amazon.com/, then upload data to S3, and make it available to Athena by using AWS Glue. To use the same sample data as this recipe, download the Sample files 2.5 folder from the book's GitHub repository.

How to do it…

To get started, ensure you have Tableau Prep Builder open, then follow these steps:

1. From the home screen, click the Connect to Data button, then search for Athena in the Connect pane. Select Amazon Athena to continue.
2. In the Connection dialog, enter the details for your AWS Athena instance and click Sign In to continue:

   a) The Server field for Athena needs to be populated with the region information. The format for this is athena.[region].amazonaws.com. For example, athena.us-east-1.amazonaws.com or athena.eu-west-1.amazonaws.com.

   b) The staging directory is where your Athena results are stored in AWS S3 and follows the format s3://[s3 bucket]/[s3 folder]. For example, s3://company/orders.

   c) Finally, you'll need your AWS access key information. For information on how to obtain this, see the AWS documentation at https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#access-keys-and-secret-access-keys.

   d) You'll also need to install the Amazon Athena JDBC driver, which Tableau provides on its download page at https://www.tableau.com/support/drivers.

3. Next, select the appropriate Catalogue from the dropdown. In Athena terminology, this is the data source:
   

   Figure 2.24 – Select the Athena data source from the Catalogue dropdown

4. In the last step, select the database of your choice and drag the table you need onto the flow canvas. In our example, I've selected a database named opssalesdb and dragged a table named results onto the flow canvas:

Figure 2.25 – Selecting an Athena table

By following the steps in this recipe, you are now able to connect Tableau Prep to cloud databases.

How it works…

Similar to on-premises data connections, Tableau Prep provides a simplified user interface on top of the database driver, so you can easily configure the connection. In this recipe, we've used the Athena JDBC driver in the background and configuring it is as easy as any other connections.

There's more…

The following screenshot shows the clear mapping between the Athena web interface and the Tableau Prep UI:

Figure 2.26 – Mapping Athena terminology to Tableau Prep

Let's move on to the next recipe!