8 Replies Latest reply on Mar 5, 2013 2:17 AM by Irma Doze

    What to do if you have over 255 columns

    Irma Doze

      It seems like Tableau can only handle 255 columns. With research data I easily cross that line. How to handle best?

       

      Kind regards,

      Irma

        • 1. Re: What to do if you have over 255 columns
          Joshua Milligan

          Irma,

           

          The 255 column limit is imposed the by Microsoft Jet Engine driver which applies to Excel, text files, and Access.

          Are the columns all truly separate dimensions and measures?  Sometimes data looks like this:

           

          CountryGold Medal CountSilver Medal CountBronze Medal Count
          USA5075100
          Germany606090
          Mexico407080

           

          While good for presentation, this is not a good shape for the raw data.  Why?  Because there is really only one measure (Medal Count) and not three (Gold/Silver/Bronze -- those are just a single dimension).

           

          So, what if the data was reshaped to this:

           

          CountryMedal TypeMedal Count
          USAGold50
          USASilver76
          USABronze100
          GermanyGold60
          GermanySilver60
          GermanyBronze90
          MexicoGold40
          MexicoSilver70
          MexicoBronze80

           

          Now the data is in a good form to be consumed by Tableau and we've eliminated a column.

           

          It may be possible that you could eliminate a lot of columns by reshaping your data (Tableau has a data reshaping tool here: http://kb.tableausoftware.com/articles/knowledgebase/addin-reshaping-data-excel).  Especially in research data, I've seen cases where each column was a date and each cell under them as really the same measure at each of those dates.  Reshaping it can reduce a lot of columns.

           

          If that doesn't work or isn't an option, then you may need to look into importing your data into a database where the driver supports over 255 columns.

           

          Regards,

          Joshua

          1 of 1 people found this helpful
          • 2. Re: What to do if you have over 255 columns
            Alex Kerin

            Great answer Joshua. I would also question whether you need all the columns. You could use custom SQL to bring only the ones you need in.

            1 of 1 people found this helpful
            • 3. Re: What to do if you have over 255 columns
              Jonathan Drummey

              Excellent answer, Joshua, especially your first & last paragraphs. The 255 column limit is due to the particular data source, not Tableau.

               

              Alex - for a wide data set with more than a few 10s of thousands of records I'd recommend against using Custom SQL (particularly for text, Excel, and Access files) because Tableau will perform the entire Custom SQL query every time Tableau hits the data source and not do the query pruning that it can do when the Single or Multiple table options are used. Of course, this is not an issue if a Tableau data extract is in place.

               

              Another point to keep in mind is that performance-wise, there's a big hit in terms of data extract creation for data sets that are tall (10s of millions of records and up) and wide (dozens and dozens of fields and up) as Tableau has to get all that data out of the data source and then index it within the extract.

               

              Jonathan

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: What to do if you have over 255 columns
                Irma Doze

                Thank you all, very helpful. I do recognize the option (first training). But some issues...

                 

                1. I don't get the add-on in Excel (could it be because I already have powerpivot on)?

                2. I have 13 'multiple' answer questions like this, how do I get all of them converted at the same time, so the dimensions/measures are correct?

                3. I currently have a SPSS file... to start with (it doesn't fit excel, indeed)....

                 

                Thanks again already for your feedback.

                • 5. Re: What to do if you have over 255 columns
                  Alex Kerin

                  Ah yes, I remember just reading about that - Is there a better option for not reading some columns? I guess a schema.ini possibly for a text file...

                  1 of 1 people found this helpful
                  • 6. Re: What to do if you have over 255 columns
                    Jonathan Drummey

                    @Irma - I haven't used PowerPivot so I don't know if the plug-in would work or note. As for working with survey data in Tableau, Steve Wexler did a great series at http://www.datarevelations.com/category/visualizing-survey-data-and-likert-scales.

                     

                    @Alex - I don't know, maybe someone like Robert Morton would?

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 7. Re: What to do if you have over 255 columns
                      Joshua Milligan

                      Irma,

                       

                      Hmmm... Do you mean that you were unable to install the reshaping tool, it's not showing up, or that it isn't working?

                       

                      I'm not sure what versions of Excel are supported (it is actually an unsupported utility provided by Tableau, but I've never had any issues with it.)  In my version, it created a new menu item called "Tableau" (see image below)

                       

                      If you are getting an error, please let us know what it is and probably someone in the community will have an answer!

                       

                      Regards,

                      Joshua

                       

                      excel.PNG

                      • 8. Re: What to do if you have over 255 columns
                        Irma Doze

                        Hi,

                         

                        I've solved the initial issue by splitting the file into different files and link them via ID.

                         

                        Regarding the reshaping tool, I don't get a message when installing but it doesn't show up. I have a 64 computer, Excel is 32 version. Could that be an issue?

                         

                        Kind regards,

                         

                        Irma