3 Replies Latest reply on Nov 10, 2018 3:28 AM by Simon Runc

    Columns from a text file v10.5

    Jason Guffey

      I've uploaded a text file and some of the information in certain columns does not align.  For example, some dates are in the price column, and some prices are in the quantity column and so forth.  Because it's a million lines worth of data, I can't manually fix it line by line.  Thanks for any help!

        • 1. Re: Columns from a text file v10.5
          Simon Runc

          hi Jason,


          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


          Name                    Value

          Simon Runc          100

          Joe, Blogs             200


          with column seperator comma, it looks like this


          Name, Value

          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.

          1 of 1 people found this helpful
          • 2. Re: Columns from a text file v10.5
            Jason Guffey

            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?

            • 3. Re: Columns from a text file v10.5
              Simon Runc

              hi Jason,


              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.