5 Replies Latest reply on Oct 18, 2016 11:23 PM by mortenbodaugaard.jrgensen

    Need help moving from Excel to solution that can handle much more data

    Bryan Mills

      Everything we do is via Excel and it works fine. We administer surveys and so each time we get surveys back we load them into our database and the database keeps growing larger and I can easily slice out the data I need as an extract and attach workbooks to it with ease. But we're now getting to the point where we have scenarios where we would want/need to create datasets that far exceed the limitations of Excel. I currently use the data reshaper in Excel but with the data I'm looking at, that will generate well over 6 million rows and Excel isn't having it and the CSV file it spit out instead won't load correctly, AND we will continually be adding data to these databases so I need something where I can keep using the data reshaper in XL and add a few thousand rows at a time to the database on a somewhat regular basis. Basically, I just need to find a way to move everything from Excel to a solution that can handle tons more data while remaining relatively easy to administer and maintain, including the ability to reshape data with Excel and then copy that data into the new solution.


      My questions is, how would you do this? What would you use? We're a small organization, currently on Tableau Desktop and at present we don't have a need for a live connection to the database though down the line that would likely be something we'd like to do; extracts work great for us. I'm the only one who knows about how to use Tableau and set up any sort of datasource though my knowledge is limited, but I suspect I can figure out most solutions with a little help. If upgrading our Tableau license is necessary that's fine, I'd just like to know the best course for my usage scenario and hopefully keep it all sitting here on my machine.


      Any help, suggestions, resources and such you can point me to would be greatly appreciated. Thanks!!

        • 1. Re: Need help moving from Excel to solution that can handle much more data
          Wes Reneau

          Hi Bryan,


          An important factor that I am keeping in mind for this reply, is 'right size'. Since your organization is small and you have had success with using Excel, I'd suggest looking into Access. Yes, it's another Office software and yes many people don't like it, but it may fit your needs and be relatively inexpensive. There's plenty of forums and sites offering solutions for design help, conversion solutions and even potential pitfalls. However, if your organization is able to spend some money on conversion and hiring a database developer to help lay the foundation, it may be worth the additional cost now, versus converting again in five years. If that's the case, something more powerful like SQL server may be worth researching or even a cloud based solution. If you have any databases in mind it might be helpful to list them for any future replies.


          Just my two cents, best of luck and hopefully an experience database developer chimes in.



          1 of 1 people found this helpful
          • 2. Re: Need help moving from Excel to solution that can handle much more data
            Bryan Mills

            Thanks for the reply, Wes.


            I'm pushing for the SQL Server route as I'd rather do this once rather than do it all over again in a year when we realize we should have gone with SQL Server from the start I looked and MSFT Azure can host what we'd need for $15/mo which is awesome.


            We administer our surveys through Survey Monkey and I found a service called Zapier that will insert rows into a database as surveys are completed, which is killer. But I know how data comes out of Survey Monkey and it requires a lot of manipulating in order to work with Tableau (like taking 100 questions and turning them into a question_ID to be looked up in a separate questions table I setup and converting string values like "often true" to a 4 and so forth), so now it seems that the majority of my questions are related to "how do I take survey monkey data and manipulate it via SQL so I can use it in Tableau?" Access seems like a sort of stop gap measure (as you rightly seem to suggest) and while it may be more difficult to setup the SQL Server route in the beginning, ultimately it does way more for us and gives us some great options, namely around that being able to have our surveys automatically inserted into a table which we can then connect clients to via a live connection with Tableau Server or Online.


            Thanks for the help!

            • 3. Re: Need help moving from Excel to solution that can handle much more data
              Wes Reneau

              No problem and I hope you can convince leadership to consider a longer term solution.


              I'll have to look into Zapier. One of our marketing teams uses Survey Monkey quite often and it might help them stage the data for one of our analyst.


              Best of luck!

              • 4. Re: Need help moving from Excel to solution that can handle much more data
                Bryan Mills

                Take a look at this, Wes.


                Looks like a really slick solution to insert rows into a SQL Server every 5-15 minutes (depending on subscription level). Could be worth a look!

                • 5. Re: Need help moving from Excel to solution that can handle much more data

                  If that doesn't work, simply use SSIS (which comes freely with almost all SQL-Server installations)