1 2 Previous Next 18 Replies Latest reply on Jul 26, 2017 7:56 PM by Muralidhar Areti

    I need to JOIN a csv and a txt (Tableau 9.3)

    Dave Dixon

      Hi - I asked about data blending last week but got no response so I'll be more specific in hopes of getting help, like, today if possible...

       

      Client needs a dashboard created with one CSV file and one TXT file as the datasources.   I can't get any actual data connections for this one, sadly, and I don't even have the option to put the content into a db.

       

      Problem is, trying the bizarre **** known as Data Blending yields nothing at all.

       

      I just need to join them, like a pair of tables, on 4 fields or so.  Full outer join with all fields from both tables showing in the resultant view, even if they have to be Null because of a lack of matching in the opposite file.

       

      e.g. File 1 has columns A B C D E, file 2 has b c d F G H I.

       

      After joining, I would have A [Bb] [Cc] [Dd] E F G H I.

       

      That's all I want to do, but I can't even find the option to add a second datasource with a join, the way I would in a database connection.

       

      Thanks for any help you can provide.

        • 1. Re: I need to JOIN a csv and a txt
          Abhinav Garg

          Hi Dave,

           

          Well, Here are the simple steps I followed to connect two data files, one CSV to another text file. It worked beautifully.

           

           

          Step 1: connect using Text file:

          Step 2: To add the CSV, you have two options:

          Option 1: In case the CSV is at the same location as the Text file, Simply drag the CSV file and join.

           

          Option 2: In case the CSV is at other file location, Click on add button and add another data source through a Text file. Locate the CSV and drag it to the joining area and Click on full outer join or whatever kind of join you want.

          .

           

          Let me know in case you need some more help.

           

          Abhi.

          • 2. Re: I need to JOIN a csv and a txt
            Dave Dixon

            Hi Abhi - I don't have those options; I'm on Tableau 9.3.

             

            I have both files in the workbook as datasources, but when one source is selected, the other file is not visible in the list even though they are stored in the same folder.

             

            The only way to see the second source is to change the dropdown at the upper left; the two do not appear on screen at the same time. (EDIT: dragging a file is not possible from the file picker popup, or from one datasource to another - at least I can't find a way to do so)

             

            This is making me insane.

            • 3. Re: I need to JOIN a csv and a txt
              Abhinav Garg

              Well, in that case, I am not sure what's going on there.

               

               

              I even tried connecting it to some other files (SAS data file, tab delimited file). It works fine for me. I am using Tableau 10.1.4 btw.

               

              Abhi.

              • 4. Re: I need to JOIN a csv and a txt
                Dave Dixon

                Well, sadly, I don't have the option to do anything in tableau 10.   If only I were in charge of such things.

                 

                I hope somebody else has the answer - I will edit the title of the thread to reflect the version.

                • 5. Re: I need to JOIN a csv and a txt
                  Ritesh Bisht

                  When don't you try Blending  Blend Your Data  ?

                   

                  Thanks,

                  Ritesh

                  • 6. Re: I need to JOIN a csv and a txt (Tableau 9.3)
                    Dave Dixon

                    I mentioned this in the very first post:

                     

                    "Problem is, trying the bizarre **** known as Data Blending yields nothing at all."

                     

                    NOTHING.  I get NOTHING out of blending.  Linking the fields and then trying to drag all of them into one view does not work at all.   Linking the fields and then just trying to bring ONE of the linked fields into the view will still only give me the values from one file, not both.    It's useless junk.

                    • 7. Re: I need to JOIN a csv and a txt (Tableau 9.3)
                      Ritesh Bisht

                      Sorry I did not notice but Data blending would have helped you for Inner and Left/Right joins for sure not for Full Outer Join.

                       

                      Not sure how to do it without version 10

                       

                      Thanks,

                      Ritesh

                      • 8. Re: I need to JOIN a csv and a txt (Tableau 9.3)
                        Muralidhar Areti

                        You really should upgrade to Tableau 10...

                         

                        Are both CSV and TXT files in the same folder? If not, try putting them in the same folder and see if you can join them (I don't think this will work)

                         

                        Do you have the option to convert the files into a single format (either both files are CSV or both files TXT)? Both files must be in the same folder. This will [should] work.

                        • 9. Re: I need to JOIN a csv and a txt (Tableau 9.3)
                          Dave Dixon

                          For Tableau 10 - I don't have the option.  It's the federal govt and they decide what software we get and don't get.

                           

                          Anyways, yes, the files are in the same folder.

                           

                          The other problem is that one of the files is a .TXT file that is a whopping 2.6 GB - NOTHING else will even *open* that file besides Tableau.

                          • 10. Re: I need to JOIN a csv and a txt (Tableau 9.3)
                            Prayson Wilfred Daniel

                            Can I suggest Pandas?! Yes it is Python, and yes it is free. If you have Mac, you already have Python 2.7 pre-installed.

                             

                             

                            #if pandas is not installed  you can install with pip install pandas
                            #Import Pandas
                            import pandas as pd
                            #Read your CSV to pd DataFrame
                            
                            df = pd.read_csv('Your CSV or TXT', sep =',' )
                            #sep can be Tab '\t' type help(pd.read_csv) to see more parameter
                            
                            #Do same with TXT file
                            df2 = ....
                            
                            #Merge the two
                            ndf = pd.merge(df, df2,  how='left', left_on=['B','b'], right_on = ['C','c'])
                            
                            #Save the merged file
                            ndf.to_csv('mymergeddata.csv', sep =';', index = False)
                            

                            in Tableau open your merged Dataset

                            2 of 2 people found this helpful
                            • 11. Re: I need to JOIN a csv and a txt (Tableau 9.3)
                              mahesh p

                              first download and install tableau 10.3.1

                               

                              and there you can connect the both the data sources and there give the join as full outer

                               

                              after that join that both the tables and save the report and now downgrade

                              this workbook to 9.3 then it will work fine

                              1 of 1 people found this helpful
                              • 12. Re: I need to JOIN a csv and a txt (Tableau 9.3)
                                Prayson Wilfred Daniel

                                This is a good idea. A downgrade would most likely not work! You can export the new join data to CSV, though, and open it in Tableau 9.3

                                1. Download 10.3

                                2. Install 10.3

                                3. Connect to your data-sources the way you want

                                4. Export the join data-source to CSV

                                5. Open Tableau 9.3 and do your magic

                                6. Remove 10.3 (If you do not want two Tableau versions on your machine)

                                • 13. Re: I need to JOIN a csv and a txt (Tableau 9.3)
                                  Dave Dixon

                                  So, what I am hearing here is quite simply "Tableau 9.3 cannot do this."  Is that correct?

                                   

                                  I've been using tableau a few years now but this problem is a new one on me - in this specific work environment I don't always get to connect to DBs, and once again, I do not get to install software myself.

                                  • 14. Re: I need to JOIN a csv and a txt (Tableau 9.3)
                                    Dave Dixon

                                    OK.  What I have done just to try it, is I got a trial of 10.3 on my own personal computer.

                                     

                                    Guess what?  Still not really working.  I can bring in both files and join or even union them, but the result is still wrong.

                                     

                                    What I end up with is still BOTH of the joined columns in each pairing, and they're both still only showing the data from their respective file!   I am joining them to get one column!!!

                                     

                                    For example, I've joined Organization from one file to Service in the other file, as they are the same.   Here's a mockup sample.

                                     

                                     

                                    What I have:

                                    File 1: Service

                                    DHS

                                    DLA
                                    Army
                                    File 2: Organization
                                    Army
                                    Navy
                                    Air Force

                                    USCG

                                     

                                    What I NEED:

                                    Service/Org

                                    Army

                                    Navy
                                    Air Force
                                    USCG
                                    DHS
                                    DLA

                                     

                                    What I actually GET instead - I still have to bring both columns into the worksheet!:

                                    OrganizationService
                                    ArmyArmy

                                    Navy

                                    Air Force
                                    USCG
                                    DHS
                                    DLA

                                     

                                    What???   I don't even know where to begin - that is NOT HOW JOINS WORK, TABLEAU.

                                    1 2 Previous Next