1 Reply Latest reply on Nov 27, 2017 3:53 AM by lenaic.ridinger

    Import text file - string column with numeric characters coerced to Numeric - change data types

    Igor Zhilin

      Dear Tableau Enthusiasts:

       

      My first day trying out Tableau 10.4 after years with Qlik and Power BI. Climbing the learning curve.

       

      I do the following:

      1. Load a table from SQL server. It has column "Sold-to party", which is a customer number. This is a zero-padded string value.

       

      2. Now I need to join another table coming from a text file. I have a tab-delimited text file without string qualifiers (string values are not quoted):

      So I connect to this file data source, try to join, and here's the error:

      So far so good. Data types are mismatching because the column "Customer Nr" from text file is coerced to Number (whole).

      Problem is I did not ask the text connector to coerce the "Customer Nr" column value into Number. It is a left zero-padded string, which is a very common case with SAP data. But the connector did not ask me to verify data types. So now I am stuck with the text file highlighted orange, and I cannot change the imported field data type.

       

      I noted if I import the text file first (so that I do not have pre-imported tables and am not forced to join the txt immediately), it will be blue, and then I can click on it and specify column data type. It works marvellous.

      But this is kind of far from intuitive, because there are 2 different behaviors of the same function (text file import) depending on the sequence of steps (first import SQL then Txt > coerced to join, cannot change datatypes; first import Txt > can change data types).

       

      Can anyone please confirm if there is a way to change the data types of the imported text file when it is imported AFTER other tables?

       

      Many thanks for your help!

        • 1. Re: Import text file - string column with numeric characters coerced to Numeric - change data types

          Hello Igor Zhilin,

           

          Welcome to Tableau !

          Let me help you with this

           

          You can change the data type for a field either on the Data Source page or in the Data pane.

          Sometimes, it might happen, Tableau incorrectly interprets the data type of a field. For example, Tableau might interpret a field that contains dates as an integer data type, rather than a date data type.

          You can change the data type for a field that was part of the original data source (as opposed to a calculated field created in Tableau) on the Data Source page.

          1. Click the data type icon for the field (as shown in the table above).
          2. Choose a new data type from the drop-down list:

          Tip: Be sure to change data types before you create an extract. Otherwise, your data may not be accurate. For example, if a floating-point field in the original data source is interpreted as an integer by Tableau, and you create your extract before you change the field's data type, the resulting floating-point field in Tableau will have some of its precision truncated.

          For information on changing data types on the Data Source page, see Data Source Page.

          This article from our knowledge base might help you as well: Data Types

           

          Let me know if you need further assistance!

           

          ----------

          Lénaïc RIÉDINGER, Global Community Engineer Tableau

          Tableau Community Forums | Knowledge Base

          If you see a Helpful or Correct response, please mark it thanks to the buttons below the targeted post!