Mutiple excel file read and create single output file with multiple columns

Question

HI ALl,

We want to read n number of excel files from the provided folder.. All files have few columns same and once column content is different in each file.

Now we have to read all these files and create a single file with multiple columns.

e.g

File_1    File_2    File_n    Column1Column2column3  Column1Column2column3  Column1Column2column3 1a111  1a222  1a456 2b112  2b223  2b457 3c113  3c224  3c458 4d114  4d225  4d459                            Output              Column1Column2column_3_1column_3_2column_3_3         1a111222456         2b112223457         3c113224458         4d114225459

bhaushedbale · Answer

Looks good but not solving my problem

bhaushedbale · Answer

Hi Rishi,

This will work if you know the number of files input.. we need solutions more n files (maybe in 100 or 1000 file volume).

For a small file set, this is perfect.

Regards,

Bhausaheb

T_Willins · Answer

Hi @bhaushedbale,

Attached is a dynamic solution that should work for n number of files.  It uses the filenames in a MultiRow Formula tool to determine each set of data then a Formula tool to create the Column names.  Finally, a Cross Tab tool to put the fields in the right format.  The Excel files used for the inputs are just the sample files you provided.  A single Input Data tool with a wildcard allows all files to be brought in at once.  Let me know if you have questions.

Multiple Files Single Output.yxmd