5 Replies Latest reply on Sep 12, 2012 6:26 PM by Joe Mako

    How can I combine  large .tsv files into one data source for Tableau? (made problematic as Tableau doesn't recognize .tsv?)

    Karen Burns

      I have a number of big TSV files with historic sales data by product and I will be receiving an additional file each month with additional sales data. TSV is the only format that the data provider will provide. I have a separate file that will be a cross reference for product attributes and features for analysis.

       

      Tableau doesn’t directly recognize TSV files (???) so I had been pulling the data together in Access to make one data source and that worked ok until I maxed out at the Access file limit of 2 gig.

      So I thought I’d try the method outlined in the forum archive – using SQL script to append the files.

      http://community.tableau.com/thread/104396

      Unfortunately Tableau doesn’t see a TSV file as a text file.

      I tried cheating and changed TSV to TXT but then it isn’t reading the tab delimiters correctly even though I specify tab as the delimiter.

       

      Can anyone recommend an approach to getting multiple large TSV data most efficiently together as a single data source for use in Tabeau on an ongoing basis?

      I would like to avoid spending a lot of money out of pocket on additional (database) software!

       

      Full disclosure: I’m not a programmer or a SQL person!!

      I’m a self employed consultant and recently started working with Tableau – so I am a novice with Tableau too! however I did do some programming early in my career and I pretty well versed with PCs in general.

      Thanks for any ideas!

      Karen

        • 1. Re: How can I combine  large .tsv files into one data source for Tableau? (made problematic as Tableau doesn't recognize .tsv?)
          Joe Mako

          You can use MySQL on your local computer to setup a database, and import/load your text files into a single table, and then either connect Tableau directly to that database table, or export it to a flat file, fully appended.

           

          Tools like Navicat Essentials are cheep and make this process easy enough so you don't need to learn much SQL.

           

          There are many other routes as well, including ETL applications, or scripting languages like Python or Pearl.

          • 2. Re: How can I combine  large .tsv files into one data source for Tableau? (made problematic as Tableau doesn't recognize .tsv?)
            Karen Burns

            Hey Joe:

            You are my hero! Your advice helped me get done my immediate work. I ended up getting Navicat as you suggested and MySql. I also got a file splitter - and split the biggest of the tsv files into pieces that excel could handle. I cleaned them up (the split happened mid-record....) and then saved as text  and imported the separated pieces into separate tables in Navicat and then used the export wizard to reassemble (append) the tables into one txt file. I'm keeping the pieces in Navicat to that i can add a table with each months data and then do a clean re-assemble each month. I don't have the $1000 to spend on the Pro version right now but I'm gonna ave to start saving up I guess!

             

            I still have some other big issues (Tableau Desktop only supporting 255 columns - yet I have a product specs flat file  also in tsv :-( . I converted it to txt stretching the limits of Excel! but when I connected to it with Tableau - tableau lopped off everything past the 255th column. I don't know if the "Pro" version can handle more columns?

             

            So I'm back in research mode.

            I am a bit aggravated that Tableau doesn't mention the file SIZE limitations on the web site. No mention of Microsoft Jet or limiting the number of columns. I haven't hit the # of records or file size limit yet but I will any minute. But I have no way of telling waht file sizes the "Pro" version of Tableau supports?

            For Personal they say they support text files but really it's txt files as I found out with tsv not being supported. I think they need more specificity especially for the Desktop products where they are going to have novices like me trying to step up to the next level of analytics tools! I'm an excel wiz and I used to do some light database programming a decade ago so with the right warnings or specs from Tableau ahead of time I could have had a less "challenging time"!

            Thank goodness you stepped up to rescue me!

            I TRULY appreciate your help!

            • 3. Re: How can I combine  large .tsv files into one data source for Tableau? (made problematic as Tableau doesn't recognize .tsv?)
              Joe Mako

              I do not believe Tableau is limited to 255 columns, but what Tableau uses to connect to text/Excel/Access (MS Jet) is limited to 255 columns.

               

              Additionally, 255 fields may not be an ideal data structure for Tableau style analysis, you may want to reshape your data prior to Tableau.

               

              As for why does marketing not highlight the limitations of Tableau? I do not know.

              • 4. Re: How can I combine  large .tsv files into one data source for Tableau? (made problematic as Tableau doesn't recognize .tsv?)
                Karen Burns

                Hey Joe:

                Yes you are wise -  and Yes - I'm looking at how I can reformat - it's just big and hard to work with!

                 

                And on the other point

                IF Tableau Desktop Personal uses MS Jet AND Jet is limited THEN by definition Tableau Desktop Personal is limited.

                My point is transparency!

                They should have that information visible to users who are choosing Tableau and paying $1000 out their own pockets to buy the Desktop Personal software,  so that they don't just stumble upon the limitation by accident!!

                 

                Cheers!

                Karen

                • 5. Re: How can I combine  large .tsv files into one data source for Tableau? (made problematic as Tableau doesn't recognize .tsv?)
                  Joe Mako

                  I agree, more transparency would be beneficial to users, but the current content may be more a function using the website for marketing, and I can see how discussing those limits does not help sell.

                   

                  There are lots of ETL tools, useful for making restructuring data easier. With more specifics, assistance could be provided.