Combining data from multiple files into a another file tab

[allisonwu]

Hi, Pretty new to alteryx but would like some help. 

1. I would need to combine multiple files (20250801/20250802/20250803) into another file specific tab (Consol file - Consol tab).

2. After which, i would need to refresh the pivot table in Consol file (USD SGD /JPY C1/JPY F tab)

3. Next, i need to copy the data from (USD SGD /JPY C1/JPY F tab) to the SGD USD Sorting/JPY/JPY Sort tab in values.

4. Once this step completed, i need to use the Main file and vlookup to SGD USD Sorting/JPY/JPY Sort tab and paste the values into the Aug tab accordingly to the account. 

Is there a way alteryx could do it? Not too sure how to start. 

[OTrieger]

@allisonwu 

There are different things that you are trying to achieve. Let's start with point 1, have you managed to create a FW that merging all the different files? This is a basic knowledge, to know how to merge different input files.

Have you managed to do just that?

[caltang]

I would ask for you to review your process entirely. Rather than rely on Excel Pivots, use Alteryx to pivot the data then write the data to the Main File directly.

Skip the Consol file entirely. 

To give you a good start, I did part 1 for you with a Batch macro to call in your csv files and consolidate them.

[apathetichell]

Hey @allisonwu --- I see one of your files is an .xls file --- not an .xlsx file... Is using a xls a hard requirement for your prorcess? this is a legacy deprecated excel format which requires special drivers and is best avoided. If you have an ingestion system running on .xls ---> I'd recommend swapping to something/anything which still has support. Your presentation layer should not be in .xls.

[Gaurav_Dhama_]

As @caltang mentioned, all of this should be done in Alteryx, as that is the efficient method and it is easier to do it in Alteryx too.

As @apathetichell mentioned, it's recommended that you use .xlsx, preferably convert .xls to .xlsx. There should not be any challenge in doing so.

[KGT]

As mentioned above, this should all be one process... trying to replicate an excel process (especially one involving Copy/Paste) will wind up convoluted as many steps in a process in Excel, are there purely because Excel is not a data process tool and so don't need to be there. However if that's the only steps you have, easy to use that as base, but try not to go back and forth.

I had a quick play with it, and the attached should show you some methods to get it all in one. You may want to explain the headers differently, I just renamed them programmatically, but with the anonymised data, that then creates 40 accounts.

Combine this with the Batch Macro from @caltang and you are pretty close to having the data right. As for formatting a report on output, or creating an excel output, there's heaps of examples on the community to get you started and then see where to go from there. But get the data right first before worrying about presentation layer.