Excel and CSV connections in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to analyze monthly sales data from two sources: an Excel file with product details and a CSV file with sales transactions. They want a combined report showing total sales per product category.

📊 Data: You have two files: 1) Excel file 'Products.xlsx' with columns ProductID, ProductName, Category. 2) CSV file 'Sales.csv' with columns SaleID, ProductID, SaleDate, Quantity, Price.

🎯 Deliverable: Create a Tableau dashboard that connects to both files, combines the data, and shows total sales amount by product category.

Sample Data

ProductID	ProductName	Category
101	Red T-Shirt	Clothing
102	Blue Jeans	Clothing
103	Running Shoes	Footwear
104	Baseball Cap	Accessories

SaleID	ProductID	SaleDate	Quantity	Price
1	101	2024-01-05	2	15
2	102	2024-01-07	1	40
3	103	2024-01-10	1	60
4	101	2024-01-15	3	15
5	104	2024-01-20	2	10
6	103	2024-01-22	1	60

1

Step 1: Open Tableau and connect to the Excel file 'Products.xlsx'.

In Tableau, click 'Connect', select 'Microsoft Excel', then open 'Products.xlsx'.

Expected Result

Tableau shows the 'Products' table with ProductID, ProductName, and Category columns.

2

Step 2: Add a connection to the CSV file 'Sales.csv' in the same Tableau workbook.

In Tableau, click 'Add' under Connections, select 'Text file', then open 'Sales.csv'.

Expected Result

Tableau shows the 'Sales' table with SaleID, ProductID, SaleDate, Quantity, and Price columns.

3

Step 3: Create a relationship between the 'Products' and 'Sales' tables using the ProductID field.

In Tableau's Data Source tab, drag 'Sales' table next to 'Products' table and create a relationship on 'ProductID'.

Expected Result

Tables are linked by ProductID, enabling combined analysis.

4

Step 4: Create a calculated field 'Sales Amount' in Tableau to calculate total sales per row.

Create calculated field: [Quantity] * [Price]

Expected Result

New field 'Sales Amount' shows total sales value for each sale.

5

Step 5: Build a worksheet showing total sales amount by product category.

Rows: Category; Columns: SUM([Sales Amount])

Expected Result

Bar chart or table showing total sales per category: Clothing, Footwear, Accessories.

6

Step 6: Create a dashboard to display the sales by category visualization with a clear title.

Add the worksheet to a new dashboard; add title 'Total Sales by Product Category'.

Expected Result

Dashboard shows total sales by category clearly for manager review.

Final Result

Dashboard: Total Sales by Product Category

Category     | Total Sales
----------------------------
Clothing     | $115
Footwear     | $120
Accessories  | $20

Bar chart with three bars representing these totals.

✓Clothing category has total sales of $115.

✓Footwear category leads with $120 in sales.

✓Accessories category has the lowest sales at $20.

Bonus Challenge

Add a filter to the dashboard to allow the manager to select sales by month.

Show Hint

Use the SaleDate field to create a date filter and add it to the dashboard for interactive filtering.

Practice

1. What is the first step to connect an Excel file in Tableau?

easy

A. Click on 'Connect to Data' and select 'Microsoft Excel'

B. Open Tableau and start a new worksheet directly

C. Drag and drop the Excel file into the Tableau workspace

D. Write a SQL query to import Excel data

Solution

1. Step 1: Open Tableau and locate data connection options

   Tableau starts with a welcome screen where you can choose data sources.

2. Step 2: Select the correct file type for Excel

   Choosing 'Microsoft Excel' allows Tableau to open and read Excel files.

3. Final Answer:

   Click on 'Connect to Data' and select 'Microsoft Excel' -> Option A

4. Quick Check:

   First step is selecting Excel connection [OK]

Hint: Always start by choosing the correct file type in Tableau [OK]

Common Mistakes:

• Trying to open Excel data without selecting the file type
• Assuming drag and drop works for Excel files
• Starting with a worksheet instead of connecting data

2. Which of the following is the correct way to connect a CSV file in Tableau?

easy

A. Select 'Text File' under 'Connect' and choose your CSV file

B. Select 'Microsoft Excel' and open the CSV file

C. Use the 'Web Data Connector' to import CSV files

D. Drag the CSV file into the 'Data Source' tab without selecting file type

Solution

1. Step 1: Identify the correct connection type for CSV files

   CSV files are considered text files, so 'Text File' is the right choice.

2. Step 2: Select the CSV file after choosing 'Text File'

   After selecting 'Text File', browse and pick your CSV file to load data.

3. Final Answer:

   Select 'Text File' under 'Connect' and choose your CSV file -> Option A

4. Quick Check:

   CSV files connect via 'Text File' option [OK]

Hint: CSV files connect through 'Text File' option in Tableau [OK]

Common Mistakes:

• Choosing 'Microsoft Excel' for CSV files
• Using 'Web Data Connector' unnecessarily
• Dragging file without selecting file type

3. After connecting an Excel file with multiple sheets, what does Tableau show by default?

medium

A. A blank worksheet with no data

B. Only the first sheet's data loaded automatically

C. A list of all sheets to choose from

D. A combined view of all sheets merged

Solution

1. Step 1: Understand Tableau's behavior on Excel connection

   Tableau reads the Excel file and lists all sheets available for selection.

2. Step 2: Recognize that Tableau does not auto-load any sheet

   It waits for the user to pick which sheet(s) to use for analysis.

3. Final Answer:

   A list of all sheets to choose from -> Option C

4. Quick Check:

   Tableau lists sheets after Excel connection [OK]

Hint: Tableau shows all sheets to pick after Excel connection [OK]

Common Mistakes:

• Assuming Tableau loads only the first sheet automatically
• Thinking Tableau merges sheets by default
• Expecting a blank worksheet without data options

4. You connected a CSV file but Tableau shows an error saying 'File not found'. What is the most likely cause?

medium

A. The CSV file is open in another program

B. The CSV file was moved or deleted after connection

C. Tableau does not support CSV files

D. The CSV file has too many rows

Solution

1. Step 1: Analyze the error message 'File not found'

   This usually means Tableau cannot locate the file at the saved path.

2. Step 2: Check if the file was moved or deleted

   If the CSV file was moved or deleted after connecting, Tableau loses the link.

3. Final Answer:

   The CSV file was moved or deleted after connection -> Option B

4. Quick Check:

   'File not found' means missing file [OK]

Hint: Ensure CSV file stays in original location after connecting [OK]

Common Mistakes:

• Assuming Tableau does not support CSV files
• Thinking file open in another program causes 'File not found'
• Blaming file size for connection errors

5. You want to combine data from multiple CSV files with the same structure in Tableau. What is the best approach?

hard

A. Use 'Join' on a common column between CSV files

B. Connect each CSV separately and create separate worksheets

C. Manually copy and paste data into one CSV before connecting

D. Use the 'Union' feature after connecting the first CSV file

Solution

1. Step 1: Understand the goal to combine multiple CSV files with same columns

   Union stacks data vertically, perfect for files with same structure.

2. Step 2: Use Tableau's 'Union' feature to combine files

   After connecting one CSV, you can add others via Union to merge rows.

3. Step 3: Recognize why other options are less efficient

   Joining merges columns, not rows; copying data manually is error-prone; separate worksheets don't combine data.

4. Final Answer:

   Use the 'Union' feature after connecting the first CSV file -> Option D

5. Quick Check:

   Union stacks same-structure files vertically [OK]

Hint: Use Union to stack multiple CSV files with same columns [OK]

Common Mistakes:

• Using Join instead of Union for same-structure files
• Connecting files separately without combining
• Manually merging files outside Tableau