2 Replies Latest reply on Jul 22, 2018 3:02 PM by Anthony Yeung

    Tableau Prep Vertical Bar Deliminator

    Anthony Yeung

      I'm using Prep to clean up data that's in a text file. The data uses the Vertical Bar as a Field Separator.


      Prep appears to be changing the field separator from a Vertical Bar (|) to a comma (,) and then back again, which I believe is throwing off my joins. This is my flow;



      The original source file, PropertyData_C, uses the Vertical Bar Field Separator for all rows-

      Row 2:     C|2018|00000051|AAAA|000|14437----2----1|FORT WORTH, CITY OF|...


      Row 116: C|2018|00005495|AAAA|000|14437--108----1---20|WTW PROPERTIES INC|...

      Row 117: C|2018|00005517|AAAA|000|14437--108----9---20|WTW PROPERTIES INC|...

      Row 118: C|2018|00005649|AAAA|000|14437--111----2---20|HOTEL TEXAS ANNEX LLC|...

      Row 119: C|2018|00005657|AAAA|000|14437--111----1---20|210 E 7TH STREET LLC|...


      At Output 2, Prep changes Row 2 to 117 to use the comma Field Separator and then changes back to using the Vertical Bar at Row 118 onwards.

      Row 2:     C,2018,00000051,AAAA,0,14437----2----1,"FORT WORTH, CITY OF"


      Row 116: C,2018,00005495,AAAA,0,14437--108----1---20,WTW PROPERTIES INC,

      Row 117: C,2018,00005517,AAAA,0,14437--108----9---20,WTW PROPERTIES INC,

      Row 118: C|2018|00005649|AAAA|000|14437--111----2---20|HOTEL TEXAS ANNEX LLC|...

      Row 119: C|2018|00005657|AAAA|000|14437--111----1---20|210 E 7TH STREET LLC|...


      This keeps flipping back and forth throughout the file. I believe this is causing "Join 1" to not match up.


      As a workaround, I imported the source data with the Vertical Bars, PropertyData_C, into excel as a text file. I specify the Vertical as the deliminator. I then save it as csv, which inherently uses the comma as a separator. Then I load this file into Prep. This works.


      However, it would be great if Prep worked with the Vertical Bar.

        • 1. Re: Tableau Prep Vertical Bar Deliminator



          Prep does work with vertical bars. Import your original file. In the lower left side you will see a selector for "Field Separator". Select vertical bar.


          As far as the output file format, I am assuming you are looking at a preview of the data and not the actual output file? Prep does use a sampling method when examining the data, you might be seeing the the rows that Prep samples to understand data structure. The actual csv output file should be correct.


          It might be just the test data you had in the file you provided but your Master data file only contains 15 unique account numbers, where as your Property_Data_C file contains almost19k. Your inner join step is only going to return the account numbers that are the same in each data set.  That is 6 accounts.




          • 2. Re: Tableau Prep Vertical Bar Deliminator
            Anthony Yeung

            Thanks for taking the time to look at this.


            The Master file is comma separated. The PropertyData_C is the only file that uses the Vertical Bar as a field separator.


            The output file format that I took snippets from is the actual output from "Output 2" in flow. All the entries in Master are in PropertyData_C and I could not figure out why Prep could not make the joins. I created "Output 2" as a way to verify what gets inputted into the join to see if in fact the entries in the Master file were missing from "Output 2". The "Output 2" file format is csv and I was able to find all entries that are in Master in PropertyData_C by using a basic text search using notepad++. That's when I noticed Prep changed the field separators for some of the rows, which is where I took the snippets from.


            You should be able to create the "Output 2" file with the packaged flow workbook if you change the directory to something relevant to your computer.


            I'm speculating that Prep is not able to complete the joins for all the entries in Master because Prep is changing the field separator for some of the rows. I know for certain if I take PropertyData_C, which has the Vertical Bar separator, and import it into excel and convert the deliminator to commas, and then take that file and import it into Prep all the inner joins come through.


            I'm open to trying any other suggestions you may have.