Macro to join multiple files in directory

[bhauteshved]

I would like to create a macro to join multiple text files. Each file will have common column headers separated by tabs. The number of files can vary. Any help? 

[Thableaus]

Hi @bhauteshved 

Is it a union or a join? Because it looks like you want to union this data (put rows below rows).

Cheers,

[estherb47 · Accepted solution]

Hi @bhauteshved 

If you want to union them together (that is, combine all files into one longer file), you can do this without a macro, by using a wildcard input. Configure your input tool to look like this: 

This will string file 2 input after file 1, etc., until all of the files are combined.

If you want to read in all of the files and have each new text file drive into a new column, joining the fields by position, be sure to choose "File Name Only" on option 4 of the input tool, Output File Name as Field.


Then add a crosstab tool, with a grouping level of "Position", Column Header of "File Name" and Values of "Number". I used "Concatenate" because the numbers are stored as text. If you convert them to a number, you can use Sum


Finally, here is the result:

I suppose you could turn this into a macro if you wanted to. With 2 tools, I tend to just use them instead of a macro :)

Cheers!

Esther