1 of 1 people found this helpful
So it sounds like your csv/text file doesn't contain any TEXT/COLUMN Delimiter (which is different from a COLUMN SEPARATOR). This means that if you have a comma (say that's the column separator) in the text of another field, then for that row Tableau (well any text file parser) will create 2 columns for that text and so the other columns get bumped along one.
Say you have this
Simon Runc 100
Joe, Blogs 200
with column seperator comma, it looks like this
Simon Runc, 100
Joe, Bloggs, 200
In he 2nd row you'll get 3 columns (with Bloggs in the Value column)
Are you able to re-run out the text file? where there is (hopefully) a setting to use TEXT/COLUMN delimiter.
So the exported file was checked comma delimited. So does it mean the file needs to be cleaned up prior to uploading into Tableau and there's no way Tableau can recognize the different data types: dates, number, string.
Here's a simplified example
Name Date Price Quantity
Vendor A 11/10/2016 $3.17 12
11/11/2016 Vendor B 5 $7.17
So I guess what I'm asking, is there not a way to recognize that Columns 1 and 2 have both a string value and date, or Columns 3 and 4 have both a Number (decimal) and Number (whole), and somehow use that information to separate the information?
In a word no! so, yes you will need to clean up the file, so that each row is in the same (column) order.
Text files contain no concept of data-types, Tableau interprets the columns to dates, strings, integers...etc. by profiling a few rows and making an "educated" guess (eg. if it see's that the rows it profiles for a column is in the format dd/mm/yyyy then it guess's it's a date). In your example it will probably make everything a string as it can't find a single type of data in each column.
It's a good thing it does this as 99.999% of the time you expect a data system to interpret data in a single column to be in the same column (Excel wouldn't be able to handle this, without building a VBA script), and not for it to move columns about based on data-type (what if there were 2 date fields?). I've never come across a system (spreadsheet, ETL tool, database, BI software) that can even output a text file with mixed-up columns!!
I'd go back to the source and get a re-run of the text file, or if this is the only way it comes out VBA (in Excel) or Python is probably the easiest way to re-structure this text file so the columns are in the right order.