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.
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.