10 Replies Latest reply on Jun 22, 2016 7:16 AM by Karen Massey

    numbers as text get formatted incorrectly

    Karen Massey

      I have a Project Number field that can contain A/N values like X1234 as well as values like 1234.123

      When I bring the data into Tableau (from Excel), it changes the non-alphanumeric values to only one decimal place, e.g., 1234.1 from 1234.123

      I have changed the data type of the column from General to Text in Excel before bringing it into Tableau, and still see the truncation.

       

      How can I retain all values of the field after the decimal?

       

      Thank you!

       

      In excel:

      Sample number.PNG

       

      In Tableau:

      Sample number tab.PNG

        • 1. Re: numbers as text get formatted incorrectly
          Ramin Melikov

          Can you post a workbook saved on 9.0?

          • 2. Re: numbers as text get formatted incorrectly
            Joe Oppelt

            I'm looking at this in the 9.2 workbook that was uploaded.


            No need to downgrade to 9.0...

            • 3. Re: numbers as text get formatted incorrectly
              Joe Oppelt

              Well this Is really weird.

               

              I did "View Data" and made an excel sheet with your data.  Re-expanded out the numbers to the three decimal places you showed in your original post.

               

              When I do "New Data Source" and read in this excel file (which I have now attached), it says "Errors occurred while reloading the data source".  (Even though, in my mind, nothing is actually getting reloaded!  It's a new data source!)  For the record, the same error occurs with a brand new workbook, so it's not like there is anything corrupt in the workbook itself.

               

              This occurs on both 9.2 and 9.3.

               

              I played with some basic stuff in Excel.  I though maybe it was because the column was formatted as "General" instead of "Text".  Didn't change it.  I made a new column in excel but that didn't matter.

               

              Something that DID work was to add a trailing space after all the non-alpha values.  That DID address what you are seeing, but wow, that's quite a hack to do if you have a million rows.

               

              And after tableau reads this in (with or without the trailing spaces) it knows that this is a text column.  Yet it's rounding off the 3-digit decimals.

               

              You have a nice little packaged example to give to Support if you decide to pursue this with them.

               

              If nobody else has anything better to add, that's what I would do at this point!

              • 4. Re: numbers as text get formatted incorrectly
                Joe Oppelt

                And just for the record, the same behavior occurs in 8.2, so it's not like something has been recently introduced here...

                • 5. Re: numbers as text get formatted incorrectly
                  Shanaka Gunaratna

                  This is wired. As a workaround you can save your excel as ".csv" and use it on tableau. It will be fine

                  1 of 1 people found this helpful
                  • 6. Re: numbers as text get formatted incorrectly
                    Ramin Melikov

                    Joe,

                     

                    I think I figured it out.

                     

                    Select the "bad" column, copy it, and then paste it into Notepad. Once in the notepad, select it all and copy again.

                     

                    Go back to Excel. Click on the cell of the header of the field. Then click on down pointer on the Paste button on the ribbon. Select "Use text import wizard..." Click next (accepting defaults on the first window). Click next again. Select "Text" instead of "General" and click on "Advanced" button

                     

                     

                    Where it says, "Decimal separator:" put comma instead of a period. Click OK and then Finish and Save.

                     

                    Note to OP: In the future, make sure your project names aren't made up of numbers with decimals.

                    1 of 1 people found this helpful
                    • 7. Re: numbers as text get formatted incorrectly
                      Joe Oppelt

                      Ramin -- Nice work!

                       

                      Karen -- See if this gets you on your way in your actual data source.  I'll say this:  Once you get past this, and get your project out of the way, I would file a ticket with Support.  You already have a nice example packaged up, and you have a workaround.

                       

                      In my opinion, a text column should be a text column, no matter what characters are stored in a field.  Maybe the issue is something on the excel side of the interchange, in which case Tableau can't do anything about it, but it's worth reporting, even if only to have some searchable record of this so that someone else might come across it if they encounter it.

                      • 8. Re: numbers as text get formatted incorrectly
                        Karen Massey

                        Thanks, this did work for me, although when I pasted it added a column that I had to then remove.  With my non-sample file, it added/overwrote 6 columns...

                        Another workaround that worked was to load the data with the excel file as General or Text, which loads it as Text in Tableau, but with the truncation issue.  If I then change the excel column to Number, then refresh in Tableau, it brings in all decimal places but remains a Text field in Tableau.  I can then change the column in Excel back to Text as it needs to be for other processing I do there.  Very convoluted to make sure Tableau treats text as text.

                        • 9. Re: numbers as text get formatted incorrectly
                          Karen Massey

                          I agree, text should be text.  I'll submit the Support case and hope that a fix will make it work in Tableau so I don't have to take so many steps, and can avoid introducing errors in the process.

                          • 10. Re: numbers as text get formatted incorrectly
                            Karen Massey

                            One final workaround, which works faster and easier than the others:

                             

                            Create a new column in excel and put in the formula

                            =(reference to cell with the number to be treated as text)&""

                             

                            Copy down to all rows that have numeric values

                            Copy the resulting values in the new converter column

                            Paste Values over the numeric values

                            Remove the converter column

                             

                            This seems to put the invisible ' before the number, which is the signal to treat the value as text.  Still many steps, but seems an easier process for me, and nice to know an excel formula that does this.