Importing Data into Tableau

"Tableau can help anyone see and understand their data. Connect to almost any database, drag and drop to create visualizations, and share with a click." - from www.tableau.com. So, to see and understand data in Tableau (to be more specific to exploit the full power of Tableau), first, some data should be present in Tableau. This guide will clearly define the ways in which a user can import data from various sources into Tableau.

The following is an image of how Tableau looks when you open it.

To import data from an excel file, first, click on "Microsoft Excel" under the connect tab. It will open a dialog box where you can navigate to the Excel file in your machine from which you want to import the data. Then, click on the file and click on ‘Open’. If there are multiple sheets in your Excel workbook, all of them will be imported automatically and they will be listed as sheets on the left-hand side panel of Tableau.

Remember that any formulas you have written in the Excel won't be imported into Tableau. Also, Tableau queries the file for data and imports the data only. So, if any data is updated in the source file, the connection needs to be refreshed each time the data is modified to keep the data up-to-date in Tableau. Also, remember that pivot tables in excel are not supported by Tableau. File extensions, in this case, would be .xls and .xlsx.

To add more data (which can be from any source) into the existing workbook, just click on "add" which is present beside connections on the left pane. Sometimes Tableau recommends using the inbuilt data interpreter to use while importing the data. There is no need to worry about it; it justs formats the data. Adding more data and using the data interpreter are the two most common steps that you will use while importing data from any sources or file formats.

The first few steps are similar to that of importing data from Microsoft Excel. After clicking on "Text File", navigate to the file that you’re importing, click on "Open", and the data in the text file will be imported. The most frequently used file formats are .txt, .csv, .tab, .tsv.

While importing from a text file, the most important property is the delimiter. To change the properties, first, click on the “options” in the text file which will give a drop down and then select the "Text File Properties", as shown below.

From the menu, you can edit the Field Separator, Text Qualifier, Character Set, and Locale. Field Separator is our delimiter. Character Set is the encoding used to encode the text file. In most of the cases, Tableau auto-detects these values. Only in some cases, will you want to edit these.

If you are not already familiar with JSON, please refer to the JSON Wikipedia article. Importing data from JSON file repeats the first two steps of other formats. First, click on "JSON File" under connect, navigate to the file's location, then select the file, and click “open”. Tableau will prompt you to select the schema levels you want to analyze, so select the levels required and click "ok".

Importing data from a JSON file is somewhat different from that of other formats, especially when you are importing data from a large JSON file. So, what happens when you import a JSON file is that Tableau scans only the first 10,000 rows of the JSON file and detects the schema from these lines. So, if you notice any fields missing from the detected schema, you can choose to scan the entire file which can take quite a bit of time, depending on the file size. Also, keep in mind that the more levels in the schema of the JSON file, the more time it will take for Tableau to load the data.

A single JSON object cannot exceed 128 MB. Also the pivot option is not supported for JSON files.

Conventionally data is not stored in PDFs but the connector for PDF files exists for a reason. Data, as it is, might not be stored in PDFs but data along with other things might be packaged and the whole is stored in PDFs. The best example would be research papers. Most research papers will include data in some or another to solidify the theory that is being presented.

The first two steps repeat themselves again, click on “PDF File” under connect, navigate to the file’s location, then select the file, and click ‘Open’. Now a dialog box appears prompting you for the pages to be scanned in the selected PDF file.

You can choose to scan the entire document, a single page, or give a range of pages. Keep in mind that scanning all the pages in large documents can be time-consuming. Once the pages are scanned, Tableau auto-names the table with unique names and lists them on the left navigation bar. If Tableau prompts the use of a data interpreter it is best to use it to clean your data easily. Make sure to validate the data in the tables that Tableau identifies in your .pdf file because sometimes, due to different formatting, Tableau might not be able to detect your data correctly. When a table is present across multiple pages in a PDF then Tableau creates a new table for data in each page, so to work on the whole all those tables must be unioned.

Tableau cannot connect with PDF files whose file names contain Unicode characters. Tableau cannot scan password protected PDF files.

Used efficiently, Tableau is a very powerful tool. The above-explained connectors are some of the most used connectors for Tableau. Other than those, Tableau can also be connected to Statistical Files, Spatial Files, Rdata Files, etc. In terms of Databases and Cloud storage systems, connectors exist for most of the popular ones like Amazon Redshift, Cloudera Hadoop, Dropbox, Google Drive, Microsoft SQL Server, MongoDB BI Connector, MySQL, OneDrive, Oracle, PostgreSQL, Salesforce, Teradata, and much more. The point here being there are almost no limitations to how you import any kind of data into Tableau. Good luck importing your data.