2 Replies Latest reply on Nov 3, 2016 2:36 PM by Bruce Segal

    Fix for Issue Using Union w/ .txt & .csv files

    Bruce Segal

      Hey Folks:

      I've been pulling my hair out on this one for a while - misaligned columns when I add a file to a union of many files, which had worked just fine. Tableau doesn't process a .txt or .csv file the same way as prior files in a union. I suspect the issue is related to .txt files created by .xls in the save as process. Might be the jet dbase engine in .xls.

       

      I couldn't find a solution to this problem using a google search or in the forums here. So, I want to share the solution.

       

      Let me know if this solution works for you. Or post other solutions.

       

      The problem:

      1. I start w/ .xls files that I open and save as either a .txt or.csv file. I do this b/c I get a new file each week that I want to union with all the files from all the prior weeks. (Union's in .xls files only work on multiple tabs in one file, not across files.)

      2. I make sure to save as the same type of .txt file (dos, or unicode, etc. each time.) Note I've been told .txt files have fewer issues than .csv files.

       

       

      The Solution Summary:

      1. Go to Edit Data Source

      2. Right click on the little arrow for the data source

      3. Click on "Text File Properties"

      4. Change the "Text Qualifier" from whatever Tableau picked.

       

      4.1 Let tableau process the data.

      4.2 Check the data either in the data connection window, or in a work sheet

      5 return the to Edit Data Source screen.

      5.1 repeat the process and change the text qualifier back to the original setting.

      6. Let Tableau reprocess the data.

      7. check to make sure it worked.

       

      The problem and solution illustrations

       

      Sample of multiple files in a union that work right

       

      Example of what it looks like when things go wrong

       

       

      A way to solve the problem - turn the text qualifier to "None." (In the screen shot below the text qualifier started out as a " and I changed it to 'None") Let Tableau process the data.Go to a work sheet if you want. Then, return to the edit connection screen and change the text qualifier back to "