Hey Naquiyah Cash,

While there is no quick way to simply remove a certain number of rows from an excel file being imported into Tableau Desktop, a data source filter when importing the data may work best. Using a sample data set that included a "row id" I removed the first few entries. Perhaps there is a field you can use or a field you can add to the data source that would make the import simpler. I attached a screenshot for further clarity.

Another way would be to use a data source filter that excludes the "Company", "Date Range", etc. values for the Report Name dimension.

Sorry to rip into this again, but I have the same issue. I want to import a Excel file that starts like this;

I want Tableau to remove/ignore the first row, and use two 2 as title. Now removing row 1 can be done by filters, but I cant get Tableau to use row 2 as title. Because even though I remove row 1,that is still row 1, it is just hidden. So if I first remove/exclude row 1, then choose to use first row as title, it will just use the hidden/removed row.

As thread starter, I really want this automated as much as possible, and this could've been an acceptable option, however the problem is that that the filter and column title is not remembered for the next import. So I have to do everything all over again for every import... Unfortunately this won't be an acceptable option for us as we will need to do this multiple times, every day. A bit surprised really that this hasn't been catered for as there are a lot of Excel exports that usually add report data/info in the first rows, resulting in title row and data rows being pushed down.

Any other tips out there to automate this as much as possible?

Tableau 9.0 (currently in beta) can handle this kind of situation, until it's released you'll have to do something else. One possible workaround for the interim is to create a separate workbook that uses a formula to refer to the cells in the  "true" workbook, then connect Tableau to that separate workbook. One note of caution, you'll have to test in your environment to make sure updates to the true workbook are reflected in Tableau, you might need to set up an automated macro to refresh the data.

You wrote, "A bit surprised really that this hasn't been catered for as there are a lot of Excel reports that usually add report data/info in the first rows, resulting in title row and data rows being pushed down." I've been thinking about this subject lately and I believe the issue is more fundamental than that and has to do with "Excel report" . Someone(s) designed those Excel reports with specific functions in mind, the two most common ones would be to be consumed by a human or potentially by Excel--in other words, not for Tableau or some other application. Humans can do many things that computers can't, and Excel (and other spreadsheet applications) have a cell-based model of data which is different from the database model of data--as in rows are records, columns are attributes. Tableau was originally built for the database model of data, with version 8.2 there were some small improvements for handling Excel files, and 9.0 has several more.

Jonathan

Hi Jonathan --

Now that Tableau 9.0 is available -- I'm still having a hard time finding how this solves Ricky's (and now my) problem.  Thoughts?

Thanks so much,

Justin

Tableau v9 introduces a new "data interpreter" that automatically detects the data part of a worksheet based on a  number of factors.

Here's a sample data set (based on Ricky's screenshot):

When I connect to this in v9, there's now a "Data doesn't look right? Tableau Data Interpreter might be able to help." line and "Turn on" button:

When I turn it on, that extraneous Table1 goes away:

Here are some more links on the new Data Interpreter:

https://boraberan.wordpress.com/2015/01/24/whats-new-in-tableau-9-0-part-1-data-preparation/

http://vizpainter.com/data-prep-and-the-new-tableau-9-data-interpreter/

http://interworks.co.uk/blog/data-reshaping-tableau-9-data-interpreter-data-pivot/

Jonathan

Just requested as a feature for tableau prep, feel free to vote

https://community.tableau.com/ideas/8676

Use powerquery aka get and transform in excel or power bi .

removing those extra rows and promote headers is the most basic functionality. such a shame tableau is not competing well in the data clean up department.

I just upgraded to 2019.1 and the Data Interpreter worked flawlessly. This is a great feature.

FYI, link to the Data Interpreter feature, introduced back in Tableau version 9.0

9.0 Features | Tableau Software

In instances where the Data Interpreter doesn't work, I've been able to use Tableau Prep (2018.3.1) to address this type of issue.

Using Jonathan's above example data, if you were to connect to this file (e.g. as a .CSV) in Tableau Prep, you would end up with field names F1, F2, F3, F4 (instead of Client, Media, Media Placement and Campaign) with the latter being pulled into the columns as data values. In my version of Prep, "Table1" also gets pulled in as a value within the first column (F1) with corresponding Null values in the remaining columns.

To resolve this, do the following:

• Add a Clean step to your flow in Prep.
• Within the Clean step, rename each column to the appropriate name (e.g. rename F1 to "Client", F2 to "Media", etc.)
• Within the newly renamed Client column (previously named F1):
  • Right-click the value "Table1" and choose Exclude.
    • Result: The value "Table1" is removed along with all corresponding Null values in the remaining columns.
  • Right-click the value "Client" (the value NOT the column name) and choose Exclude.
    • Result: The value "Client" is removed along with all corresponding header-values listed on row-2 of the data source.

At this point the column names and data should be laid out in the desired format. You might still need to adjust data types of particular columns (e.g. from String to Number). I admit that this can be cumbersome if you have a large amount of columns, but you should only have to do it once and then the process will be saved to your flow.

Thanks for sharing this, Jim. No doubt it will help fellow Community members

Thanks, Ciara

[Program Manager - Tableau Community Forums]