Merge and compare files with different format

Question

Hi all! I'm new to Alteryx and I ask for your support as I am stuck with the flow that I am working on.

I have 1700 txt files that are daily records of our cash registers with different delimiters (, " and so on). The date is only at the top of each file.

I am trying to merge all of them in one file and compare it to a report based on the order number.

The output I need is:

1. unique values from the merged txt files (if possible with the date)

2. matches between the files

3. unique values from the report

I used the join tool but I might have set something wrong because there are very few matches and too many orders without a match.

Could you please help me out?

Here's a screenshot of my flow.

Last questions: could you suggest a way to pack my output as I might have mil of lines and excel won't be able to read them.

Thank you very much!

image.png

OTrieger · Answer

Now in regards to the output. You can split the output into several excel files

Write it as CSV

OTrieger · Answer

@BiancaCNO 
There could be several reasons why you are getting only a few matching items.

You can check the SME and verify if it is logic that there are so few matching items, so your workflow is correct.

The items are not 100% the same. It could be trailing spaces

I'm not sure how many criteria is set for the matching

It can be due to rounding of numbers.

There can be many reasons, so it is very hard to tell without seeing it.