3 Replies Latest reply on Apr 1, 2013 7:18 AM by Jonathan Drummey

    Importing Excel file with more than 255 columns

    Tony Gruebner

      Hi,

       

      I know this question has been asked before on here as I have read around but I can't seem to find an answer to my specific situation.

       

      I have inherited an excel file with over 2000 columns. I'm aware this is bad data structure but it is from survey data where every question that is asked there was a matrix of answers the respondent chooses therefore some questions have 8x8 possible answers therefore require 64 columns to house the answers (the way that this survey tool extracts the data).

       

      I want to put this into Tableau but it is only exporting the first 255 columns, which I know is an issue on the Excel side and not Tableau. I've tried doing it as CSV or txt files but the data is so wide that one line seems to drop into multiple rows which makes it unreadable for Tableau. Same is true for exporting to Access.

       

      I know that the reshaping tool has been mentioned to convert the data to a better format but as every question in the survey has a different structure I think I would need to reshape each question individually of which there is about 70 and would need to combine them by ID inside Tableau which would take forever. Likewise would be true of splitting the dataset into about 9-10 different datasets (each with less than 255 columns) and then combining them in Tableau based on Excel but this sounds complicated, time consuming and messy.

       

      Does anyone have any ideas around this problem?

        • 1. Re: Importing Excel file with more than 255 columns
          Joshua Milligan

          Tony,

           

          You've mentioned quite a few of the options.  It does sound like a rather difficult issue.  I have a couple of thoughts:

          1. Maybe posting the data structure would help.  There are some very creative data people on the forums who might have good ideas about the structure and how it could be (easily?) modified.

          2. Moving the data from Excel/txt/csv/Access (all of which use the Microsoft JET driver and thus have the 255 column limit) to a data source that does not have the same issue (SQL Server, MySQL, etc...) would get you past the limit.  However, I wonder how easy analysis will be when you have 2000 dimensions and measures.

           

          Regards,

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Importing Excel file with more than 255 columns
            Tony Gruebner

            Thanks Joshua,

             

            I'm going to try getting the original extract in spss and seeing if that works as I can't see any way of getting it out of excel to anywhere in full (unless my understanding of what I have read is wrong). I'm not too sure how tableau works with spss files though.

             

            As to your last point I do agree with you here

            • 3. Re: Importing Excel file with more than 255 columns
              Jonathan Drummey

              Steve Wexler wrote a wonderful set of posts on using Tableau for survey data at http://www.datarevelations.com/category/visualizing-survey-data-and-likert-scales, and he includes instructions on using the Tableau data reshaper.

               

              Based on your description, where I think I'd start is:

               

              1) Get a sense of the layout of that data - what makes a question, what makes an answer, what are your question types

              2) Use the data to create some dimension tables of question types, questions & answers (& anything else needed

              3) Use the Tableau data reshaper as Steve describes to create two fact tables, one for respondents and one for responses. This will take those 64 columns down to 1.

              4) Write some custom SQL or a query in your tool to do a 2nd level of reshaping to appropriately assign dimensions. For example, you might take the 1 answer column and break it up into 8 columns with 8 possible values.

              5) use that as your data source

               

              I've used this process succesfully with survey data that had a combination of yes/no, multiple answer, and Likert scales. It took some work to get started, however at this point my ETL process is really straightforward and when the survey changes all I have to do is add some rows to the dimension tables.

               

              Jonathan